Forum Discussion
Excel - How do I compare calculated values in cells with the IF function?
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
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