Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement Not Working
Several people have worked on this and I am sure it is something obvious, but
the following If Statement will not work: =IF(SUM(I14:I19)=(G20-H20),(G20-H20),"error") It always returns 'error', even if the statement is true. Any suggestions? Thanks a lot! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement Not Working
How many decimal places do you expect? Let's say it's 2.
Try this =IF(round(SUM(I14:I19),2)=round((G20-H20),2),(G20-H20),"error") change BOTH occurrences of ",2)" to a different # depending on the desired/expected decimal places "Telegirl" wrote: Several people have worked on this and I am sure it is something obvious, but the following If Statement will not work: =IF(SUM(I14:I19)=(G20-H20),(G20-H20),"error") It always returns 'error', even if the statement is true. Any suggestions? Thanks a lot! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement Not Working
Hi
You formula does work. It assumes that the figure in G20 is higher than the number in H20. Is that correct? You can also remove some parenthesis e.g =IF(SUM(I14:I19)=G20-H20,G20-H20,"error") regards Peter "Telegirl" wrote: Several people have worked on this and I am sure it is something obvious, but the following If Statement will not work: =IF(SUM(I14:I19)=(G20-H20),(G20-H20),"error") It always returns 'error', even if the statement is true. Any suggestions? Thanks a lot! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement Not Working
Hello
Try =IF((SUM(I14:I19))=(G20-H20),(G20-H20),""error"") Because Excel calculates formulas with priority standards starting with the operation symbols (such as +, -) HTH Cordially Pascal "Telegirl" a écrit dans le message de news: ... Several people have worked on this and I am sure it is something obvious, but the following If Statement will not work: =IF(SUM(I14:I19)=(G20-H20),(G20-H20),"error") It always returns 'error', even if the statement is true. Any suggestions? Thanks a lot! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement Not Working
Do you have rounding errors in your calculations?
What do you get from the formula =SUM(I14:I19)=(G20-H20) ? What do you get from the formula =SUM(I14:I19)-(G20-H20) ? What are the contents of I14:I19, G20, and H20, respectively? -- David Biddulph "Telegirl" wrote in message ... Several people have worked on this and I am sure it is something obvious, but the following If Statement will not work: =IF(SUM(I14:I19)=(G20-H20),(G20-H20),"error") It always returns 'error', even if the statement is true. Any suggestions? Thanks a lot! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement Not Working
All the cells are formatted to currency, with two decimal places. And yes, it
does assume that G20 is larger than H20. It is formatted so that G20 is a Sum, while H20 is also an if statement: =IF(SUM(H14:H19)=SUM(K20:V20),SUM(H14:H19),"error" ) Even if I remove the If statement from H20 and put in a Sum statement, it doesn't work. I even took the sum I14:I19 and put it in one cell, the G20-H20 in another cell and used those seperate cells to do the If statement (e.g. =IF(I21=I22, I22, "error") and that didn't work. What will work in the If Statement is if I use a sign instead of an equal. Then it will come up with the right answer. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement Not Working
I tried that and still receive an error reading
"papou" wrote: Hello Try =IF((SUM(I14:I19))=(G20-H20),(G20-H20),""error"") Because Excel calculates formulas with priority standards starting with the operation symbols (such as +, -) HTH Cordially Pascal "Telegirl" a écrit dans le message de news: ... Several people have worked on this and I am sure it is something obvious, but the following If Statement will not work: =IF(SUM(I14:I19)=(G20-H20),(G20-H20),"error") It always returns 'error', even if the statement is true. Any suggestions? Thanks a lot! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement Not Working
Oh, crud. I found the error. When I checked for rounding errors, I didn't
go to the max of 30, I just went to 10 and found nothing. Now that I went back and went to 30, I found the discrepency in the 28th decimal place.....not sure why that is there. I'm feeling pretty sheepish right now...... THANK YOU THANK YOU..... I was going to have to go to a mental institution if I didn't figure this out. "David Biddulph" wrote: Do you have rounding errors in your calculations? What do you get from the formula =SUM(I14:I19)=(G20-H20) ? What do you get from the formula =SUM(I14:I19)-(G20-H20) ? What are the contents of I14:I19, G20, and H20, respectively? -- David Biddulph "Telegirl" wrote in message ... Several people have worked on this and I am sure it is something obvious, but the following If Statement will not work: =IF(SUM(I14:I19)=(G20-H20),(G20-H20),"error") It always returns 'error', even if the statement is true. Any suggestions? Thanks a lot! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement Not Working
Fri, 11 May 2007 14:00:08 +0200 from papou
: "Telegirl" a écrit dans le message de news: ... =IF(SUM(I14:I19)=(G20-H20),(G20-H20),"error") It always returns 'error', even if the statement is true. Any suggestions? Try =IF((SUM(I14:I19))=(G20-H20),(G20-H20),""error"") Because Excel calculates formulas with priority standards starting with the operation symbols (such as +, -) But "comma" has quite low priority, so it is never necessary to parenthesize function arguments. It is also never necessary to put parens directly around a function call. Telegirl had enough parentheses -- in fact, she even had two unnecessary sets. =IF(SUM(I14:I19)=G20-H20,G20-H20,"error") Telegirl, You say "even if the statement is true". why not create a couple of temporary "helper" cells, one containing sum(I14:I19) and the other containing G20-H20, and change your IF to reference those cells? That will help you see what is going on. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement Not Working
Fri, 11 May 2007 05:27:01 -0700 from Telegirl
: All the cells are formatted to currency, with two decimal places. And yes, it does assume that G20 is larger than H20. Formatting doesn't matter in calculations, unless you've checked "Precision as displayed" in Tools | Options | Calculation. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement for time data not working properly | Excel Worksheet Functions | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
IF statement not working | Excel Discussion (Misc queries) | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions | |||
Wildcard Not Working in IF statement | Excel Worksheet Functions |