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
!!! I opened a new sheet in the financia records file and entered the example you posted, and both
=A1=B1 and IF(A1=B1,"True","False")
work OK!!!
So it is something about the sheet I am doing all the calculations on that is the problem.
I may be able to work around the problem by placing the comparisons on another sheet and referring back to the results on the calculations sheet. But someday I want to squash this bug!
Phil
I tried it and when I copy the values from the Calculations sheet into a new blank sheet and then do the comparisons they fail!
AHA! I copied and pasted values into the cells on the new sheet. One value from the Calculations sheet is 3052.90000000002, and the other is 3052.9!
Since these are all supposed to be dollars and cents values I don't know where the extra $0.00000000002 came from, but now I know what to look for.
So it is just a strange problem in the data.
Nevermind! But thanks for your help!
I made a copy of the Excel 2010 financial workbook from the Win 7 machine and opened it with Excel 2013 on the Win 10 machine.
Same problem! So it isn't the version of Excel that is the problem, or the OS or machine architecture.
The plot thickens ....
Phil
Thanks! I tried your file in Excel 2010 V14.0.6023.1000 (32 bit) on my Win 7 Pro machine - my primary work machine) It does work OK.
I also tried it on my Win 10 laptop with Excel 2013, and it works OK there!
So somehow the much larger spreadsheet I am working on on the Excel 2010 is screwed up. I tried formatting the cells as "General" as in your file, but I still get the wrong results.
To my knowledge I have always used Excel with it's default settings. I wonder if there is a way to set preferences to use direct (cell contents - formulas) instead of indirect (calculated values) for comparisons?
I started a new blank workbook/sheet/file in Excel 2010 on the Win 7 machine and typed in what you have in your example. It all works correctly. So there is something about the larger file that is messed up.
The large file contains financial records for a 501(c)(3) nonprofit, and it has 10 sheets and a lot of calculation using data on multiple sheets. But it is only 207 Kbytes.
I'll keep trying to find out why it is doing what it is doing. If I figure it out I will post the answer here.
Phil
