1

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.

4
  • 2
    Why didn't you use method 1 on that page (the ROUND( one)? The as displayed method only works in result-cells. You can't use these cells to do further calculations because the they are only displayed correctly (but internal they are still "off") Always use round( in these circumstances.
    – Rik
    Oct 10, 2013 at 16:47
  • 1
    Tried this on Excel 2010 and I'm having no problem, both A4 and B4 display TRUE. I would imagine using the ROUND() function to 2 decimal places should resolve your problem though?
    – Andi Mohr
    Oct 10, 2013 at 16:49
  • 2
    Yep. Always and in all versions. It even warns you on that page 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. Especially with these kinds of data always make sure and use round(.
    – Rik
    Oct 10, 2013 at 16:49
  • @Rik Ahh, that is helpful, that took care of it. I did not know about the ROUND formula. Please place that as an answer and I will mark it as the correct solution. Oct 10, 2013 at 17:28

1 Answer 1

1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .