I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

I have a worksheet error testing formula that subtracts one number from another in a cell thats formated to show red when the answer is less than zero. If I have not made an entry error the two numbers are the same so the answer is zero and the zero showes up in red. If I reverse the formula the answer is still zero but shows up in black. My first thought was that sometimes in Excel during computations a number might be added to one of the numbers well to the right of the decimal. I checked for this, to 15 digits past the decimal and it's still all zeros. In another simular sprea...

Hi Again everyone I am trying to get a formula in (possible countif) which doesn't seem to be working In column D I have the following dates D42 30/03/2009 D43 01/06/2005 D44 06/02/2009 D45 30/03/2009 D46 19/10/2009 D47 08/03/2010 D48 15/07/2010 D49 15/07/2010 D50 15/09/2010 D51 15/09/2010 In I3 I have 31/01/10, J3 28/02/10 etc etc running along to AR3 which has 31/12/12 I want to write a formula that simply says to check the dates in column D42 - D51 against the date in I3 and if the date in ...

Can I record it as a macro or anything like that? I have to run it o five sheets. I've tried to conditional formatting, and it works only o one cell, unless I'm doing it wrong -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26434 not sure what you are trying to do but conditional formating can be coped and pasted. you have also conditional format an entire column/row by selected the en...

1.I have simple dates in one column (say column A) . 2.In the next column(Column B) I would like the date five months after Column A to be displayed.Eg if Column A has an entry of 9th June 2007,Column B should display 8th November,2007. 3.A simple formula does not do the job as this does not take into account the different number of days in different months! regards S.Sanatani Your post is a bit ambiguous since you don't really say how the different number of days in months should be handled. One way: A1: <date> B1: =DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)-1) In articl...

I need to get a specific total from the following criteria. I have data in 2 columns and need to get a total based upon specific entries in the cells in thoses columns. For example: Column A has 4 choices from a picklist. (N,P,C,R) Column B has a different set of choices. (ENG,OP,T&E,RS) What I need to do is be able to get an answer for how many N's are also ENG's Thanks -- doc ------------------------------------------------------------------------ doc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1434 View this thread: http://www.excelforu...

Hello, I am trying to create a formula that will insert the date and time into a new cell only when another cell is "0". In other words, if the specific cell hits 0, then the current date and time when the cell hits 0 is recorded in the other cell. If the cell is anything except 0, then the other cell is blank or says "not finished". I tried the if and now formulas to no avail and my macro is returning a VALUE error, so I'm completely lost. Any help would be much appreciated. Thanks, Katie Katie, It is a lot simpler with VBA This tests A1 and puts the date and t...

Does anyone understand this array function? {=TABLE(,H2)} Here's what I do know: 1) It doesn't seem to refer to any named ranges. 2) It doesn't seem to be a user-defined function. HELP! Thanks, Jim Look up "one variable data table" in help You can also look up "two variable data table", but yours is one. Regards Ken................. "Jim" <clovisjim@gmail.com> wrote in message news:d62b8b12-89a9-4cb9-acbc-b7f1e54244ed@15g2000yqi.googlegroups.com... > Does anyone understand this array f...

This may seem academic, but I'm having difficulty finding the right formula to use for a task. In simple terms, I'm trying to cross-reference 2 columns (say A and B) to find instances where a value exists in column A but not B, and vice versa. For example, I have 2 columns of Vehicle Id numbers (VIN#), A and B, that should each have the same count. Assuming that these columns are duplicates, I'd like to find the exceptions where a VIN# exists in column A and not, and vice versa. Thanks for any help!!!! Hi in C1 enter =IF(A1<>B1,"no match","") and ...

I need help understanding which formula I should use. . . I have built a table similar to the one below on a sheet of my workbook. On a separate sheet, I need to be able to input ONLY two variables (state and fruit) and return information (the cost) defined in the table below. As an example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to change Texas to Florida and the formula should immediately update to the value to $6.00. Another example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to ...

I'm got a spreadsheet with many different sheets in it. The first sheet is the main page, and it holds a master table with data like customer name, salesman, registration number, date of registration etc. The other sheets are the individual salesman sheets, with just their relevant information which is copied from the main page. I'm experiementing with two ways to do this, VLOOKUP or just a formula like =sheet1!A12 etc. In the date column I want to put the date of registration, so I can copy that from the main page. However, sometimes a date is not always needed, so when I ...

I have a spreadsheet that I collate orders during the month from a customer, the spreadsheet contains the volume they order in one column and the volume actually delivered in another. I need to keep track during the month of how much they have ordered/received to ensure they remain within their product allocation for the month. eg: Site Ordered Delivered A 36,000 35,999 A 18,000 18,500 B 36,000 36,000 A 36,000 36,001 What I want to do is create a formula th...

hi, I don't know why my excel 2003 new worksheet cannot auto calulate formula (eg. summation), i need to press F9 and it will refresh and show the new figure. there is "calculate" word at the left hand bottom of the screen. what is the likely reason ? it was running fine 2 weeks ago. any advise is greatly appreciated. rgds. Tools>Options>Calculation tab, check Automatic -- Kind regards, Niek Otten Microsoft MVP - Excel <sg_s123@yahoo.com.sg> wrote in message news:d5393a73-eb7d-4e08-8fab-5f4ab895f77a@e23g2000prf.googlegroups.com... | hi, | | I don't know w...

Hi, I have a formula based user defined field containing the value: Str(Mid([Subject],2,6)) By default it's not possible to sort or group by formula UDF's. Sue Mosher wrote quite a while ago: "Quite right, and thanks for pointing that out. Turns out that I'd had formula and combination fields on the brain lately and hadn't been working with any other kind of UDF for a while. And, of course, there's a workaround. If you need to sort or group by a calculated field, put the formula in code behind the form rather than in the definition of the field." Does any...

I've done all the searches online and off for help. Now time's running out for my project. Here's the situation... My data is in two columns with thousands of rows, columns are Parcel Size in Acres (number) and Use Code (numeric text). There are probably a couple hundred different use codes. I would rather group the more detailed use codes into broader categories, then get those total acreages. For example, there are several use codes that specify exactly what type of single-family house but all I really need to know is that it is a single-family house. Ultimately, I...

have any body tried mod(12,2.4) it should return zero but instead it returns a number any clue here http://www.cpearson.com/excel/rounding.htm -- Jim <alaomair@gmail.com> wrote in message news:7ffaf97a-ad59-4e89-a174-6ffb1e1b1daa@l62g2000hse.googlegroups.com... > have any body tried mod(12,2.4) it should return zero but instead it > returns a number > any clue here Hi, Try not to do multiple posts. -- Thanks, Shane Devenshire "alaomair@gmail.com" wrote: > have any body tried mod(12,2.4) it should return zero but instead it > returns a number > an...

Hello all! I need help with an "if then" formula. What I have is a list of about 8000 people in column a, their hours worked in column b, and a number of 8,9,10,11,12,13,20,21,22,23 in column c. I need several things to happen. If someone's scheduled hours worked are greater than or equal to 32, and they are 8,9,10 I need a result to say 150. If someone's scheduled hours are less than 32 and they are 8,9,10, I need the result to say 75. AND If their hours are greater than or equal to 32, and they are 12 or 13, the result = 250, if hours less than 32 and 12 or 13...

Hi - is there a reference doc or an FAQ somewhere that details how MS Money calculates its various investment fomulas. I have investments that show different returns in Money vs what the various investment companies are reporting. I've looked around and haven't been able to find anything. Much obliged. Ian In microsoft.public.money, Ian wrote: >Hi - is there a reference doc or an FAQ somewhere that details how MS Money >calculates its various investment fomulas. I have investments that show >different returns in Money vs what the various investment companies are &g...

Good afternoon, Alright, here's a simple one. How I have forgotten it (when I use it almost every day) is beyond me. All I know is that my mind is mush right now. I am preparing a blank work sheet for others to use, so here it is: I have multiple columns that all sum different cells. I need the "Answer" cell to not show a 0 if there is nothing to "sum". ex. Cell A1 is the sum total of A2-A10. If A2-A10 are empty, I do NOT want A1 to show 0. I have no idea how I am forgetting this today. I need a drink. Thanks for your help. Really. -- Scuba Wino -------...

hi, I am trying to modify the formula below to use the LEFT function. any help Appreciated.. =SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!H6:H35"),"=W")) I tried this below, but I get a reference error: =SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!(LEFT(H6:H35,1"),"=W")) Thanks In Advance. -- Mh ----------------------------------------------------------------------- Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3598 View this thread: http://www...

I am trying to get a series of cells to fill with a color if the word "insert" is inserted into the first cell. I.E. cells A2-A5 and cell A2 has "insert" typed in so up to A5 will change color. How do I go about doing this? Try Format | Conditional Formatting - after selecting cells A2:a5 Then use "Formula is" and type in =$A2 = "insert" Then click on the Format button and select the color you want from the Patterns tab "Murph" wrote: > I am trying to get a series of cells to fill with a color if the word > "insert&qu...

I am looking to fill a series of increasing letters for example: aaa aab aac ... ... aaz aba abb Hi there, You can do this with some formulas. I'll assume you have this in A1 "aaa". Enter this formula in A2 and copy down as needed ... =IF(LEFT(A1,1)=122,CHAR(B1+1),CHAR(B1))&IF(MID(A1,2,1)=97,CHAR(C1+1),CHAR(C1))&IF(RIGHT(A1,1)=122,CHAR(97),CHAR(D1+1)) -- Regards, Zack Barresse, aka firefytr "tadpgk835" <tadpgk835@discussions.microsoft.com> wrote in message news:6EC42423-AA5B-40D0-ABB7-2A156AFEC187@microsoft.com... >I am looking to fill a ser...

If the value of a cell is a letter, can this be used in an IF formula? I am making a simple chore spreadsheet for my kids. In this sheet, each chore is represented by a letter. (i.e. B=clean bedroom) Also, each chore has a certain value amount with it (i.e. B=$1). I would like to be able to have the kids enter in the chore letter when it's completed and have the computer do all the calculations of amounts and totals. Can this be done? For example: =IF(c7=B,1,IF(c7=K,2)) Hi Stop re-posting, your messages 6 of them are posted plus you have an answer on your first post. Ignore...

I am creating a line chart where the source data is the result of an =IF formula. How do I get the chart to only plot the cells where there is a result? Currently the chart is plotting a zero value for all the cells where the formula does not yet show a result. If( something, formula, NA() ) The otherwise blank cells now have #N/A in then (you can hide these with Conditional formatting if they bother you) Charts ignore these values best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "JD" <JD@discussions.microsoft.com> wrote in message new...

Hi all, I'm looking for the correct formula for this expression. =COUNTIF((Your_Range,"hat and coat") or COUNTIF(Your_Range,"hat")) and (range,"New York") The first two criteria are possible selections and the third is a must in order to count the record as 1 My question is what would be the logical expression(formula) to show this. -- -- Cheers try =SUMPRODUCT((K7:K8={"hat","coat"})*1) -- Don Guillett SalesAid Software donaldb@281.com "zubee" <zubee@discussions.microsoft.com> wrote in message news:D711CA23-32A6-4C...