# MS Excel 2007 If Then Formula Troubles

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.

• 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 '13 at 16:47
• 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 '13 at 16:49
• 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 '13 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. – JabberwockyDecompiler Oct 10 '13 at 17:28

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")`.
So the best advice is to always use `ROUND()` in you formulas where it comes to expected precision during evaluations.