Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
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 '13 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 '13 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 '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
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.