Forum Discussion
Excel - How do I compare calculated values in cells with the IF function?
Couldn't replicate your problem. Have look at the attached workbook. Works for me as it should. Does it work for you?
I found the problem!
It appears that occasionally subtractions fail and give some pretty strange incorrect values. I subtracted two dollar values (observed to 30 decimal places
$X.00 - $Y.00
And got a result
$Z.000000000007280000000000000000!
I found several other places where calculations (additions, subtractions, SUMs, etc.) gave results with fractions of a cent. None of these calculations involved division, roots, etc.
This is an example of floating point arithmetic error, and there is no way to correct the math.
The solution seems to be to enclose all calculations with the ROUND(X-Y,2) function to force all values to round to two decimal places (even cents).
I could probably set the "precision as displayed" option because there should never be any numbers with fractions of cents. But that is not reversible after it is implemented. I'll have to think about this for a while!
Phil
As you have determined, the problem is one of rounding error and not with the IF function. No one else was going to be able to reproduce the problem because an integer keyed in is held without rounding error. If you introduce cents then only $0.25, $0.50 and $0.75 will be represented without rounding error. Be very careful about using
= ROUND( X-Y, 2 )
Under addition, it should work, but if there is any multiplication/division then, instead of your calculation being accurate to billionths of a cent, your rounding could well create errors of up to ±1cent for each number. Add a million such rounded calculations together and all you can be sure of is that the rounding error that you have introduced with the formula will be less than $10,000. That is not an error I would care to live with. If you do not round, the maximum error would be ±$0.0005.
Phil, that is floating point error. CPU and OS version doesn't matter. The only matters if you are on Excel 95 or earlier; or on any later one.
More details is here Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers? or more modern version Floating-point arithmetic may give inaccurate results in Excel
