Unexpected result in IF

[baulko]

Have a very simple worksheet as follows

B C D
5 1073.41 1073.41 OK
6 =1700-626.59 1073.41 ERROR

Formula in D5

=IF((B5-C5)=0,"OK","ERROR") and similarly for row 6 using B6,C6.

Why is D6 "ERROR" ?

Using Excel 2003 SP2.

 

[RSXchin]

unless b6 says "=1700-626.59" then it is actually considered text.

 

[baulko]

It does day "=(1700-626.59)" minus the quotes of course.
If B6 is treated as text we see excel #VALUE! errors as a result of trying to compare to value in C6 as requested by formula in D6.

 

[RSXchin]

i'm pretty sure it's because it isn't being computed.

 

[baulko]

Sorry,
Original post not clear.
B6 shows 1073.41 and is the result of formula =(1700-626.59)

 

[RSXchin]

Try this one then. works for me

=IF(B6=C6,"OK","ERROR")

 

[baulko]

Thanks, I can get it to work.
The issue appeared in a home accounting sheet.
Other values appearing in the sheet seemed to be fine. This one in particular always showed ERROR.
I don't understand why but would like to be enlightened if possible.

 

[BarCodeWiz]

You're doing math on decimal values and then comparing the results. You should never do that. That's why they are not equal. Internally, the computer sees 1073.409999999 or something like that. To see if two decimal numbers are equal, you should round them first or see if the difference between them is less than, say, 0.0001, like this:

=IF((B5-C5)<0.0001,"OK", "ERROR")