QUESTION :
This is a simplified case of an issue that I am seeing using MS Excel 2007.
Format your sheet to be currency with 2 decimal places.
Cell A1 type 25
Cell A2 type 20.13
Cell A3 type 4.87
CELL B3 enter the formula =A1-A2
Cell A4 enter the formula =IF(A1-A2=A3,”TRUE”,”FALSE”)
Cell B4 enter the formula =IF(A3=B3,”TRUE”,”FALSE”)
ERROR is A4 displays FALSE and B4 displays FALSE
NOTE: I first noticed a precision error with the simplified version. So first I changed the precision to calculate by display using this guide. That fixed my simple version, but I still saw the issue on my page. I changed the display of decimals to be 12 decimal places, everything after the second decimal place is a zero on all values on the sheet.
Does anyone have any suggestions on what else I can try to fix my formula/worksheet.
ANSWER :
When using floating-point arithmetic and using the outcome to do evaluations or further calculations it is often best to make sure your outcome has the right/expected precision.
The best method of making sure you have the right precision is using ROUND() in your formula. In this case it would be =IF(ROUND(A1-A2,2)=ROUND(A3,2),"TRUE","FALSE").
The second method advised by microsoft here only ensures that the cells are displayed with the correct decimals. Internally they can still be slightly off. Using it to do further re-calculations will likely result in larger inaccuracy. For evaluations this setting has no effect.
Note Use of the Precision as Displayed option can have cumulative calculation effects that can make your data increasingly inaccurate over time. Use this option only if you are certain that the displayed percision will maintain the accuracy of your data.
So the best advice is to always use ROUND() in you formulas where it comes to expected precision during evaluations.