Excel Tip: Dealing With Rounding Errors
Posted by accountingweb on
579
printer friendly
Have you ever experienced the frustration of displaying a worksheet without full decimal places and having Excel round the numbers for presentation purposes, but if you look closely at the calculations, the displayed numbers don't quite add up?
For example, consider this calculation:
23.49
23.49
46.98
If you display your Excel spreadsheet without decimal places, the calculation is rewritten like this:
23
23
47
The answer is technically correct, but who's going to believe it?
One way in which you can solve this problem is to request that Excel present the worksheet in "Precision as displayed" mode, which means that all numbers will be rounded to the actual number that is displayed. Apply this option by selecting the Tools menu, then choosing Options, Calculation, and checking the "Precision as displayed" box. Click OK to execute this change.
The result will be a calculation that appears thus:
23
23
46
Note that this change will be made with respect to this worksheet only.