If Statement Not Working

[Guest]

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!

 

[Guest]

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

 

[Guest]

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

 

[papou]

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

 

[David Biddulph]

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?

 

[Guest]

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.

 

[Guest]

I tried that and still receive an error reading

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

 

[Guest]

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.

 

[Stan Brown]

Fri, 11 May 2007 14:00:08 +0200 from papou

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]

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.