• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+
The Office Tech Pro

Calculation anomalies in Excel® 2007: That darned penny!

Get PDF file

by on
in The Office Tech Pro

If you work with currency data and percentages, you have likely had an argument with that darned penny! You have flawlessly crafted your worksheet, expertly constructed formulas and can’t reconcile the penny that results when your calculations round up.

There are a couple of approaches. The first is to turn on the Excel option, “Set precision as displayed.” This forces cell values to actually be what is displayed, rather than just hide the fact that there are more decimal places and rounding up. You will see a warning, “Data will permanently lose accuracy.” Technically, it does. Even though your calculations now work, those decimal values beyond what you displayed have been altered to “make” the calculation work. A good way to use this is to set it to get the desired output, and then set it back. Otherwise, you will gradually begin to lose accuracy in other calculations.

To set it, click on the Office Button, then Excel Options. In the Advanced category, scroll down to the options under “When calculating this workbook:”. There you will find a check box for “Set precision as displayed.”
Although the next solution isn’t always 100% effective, it may solve a good number of display (as opposed mathematic) inconsistencies. The ROUND function requires the number or cell reference and the number of decimal places to round to. In our example this solves the issue.
The most important factor in statistical and financial reporting for decision support is accuracy. Double check, verify and validate your calculations to make sure that the effect of correcting the penny problem doesn’t simply give you the wrong answer!

Leave a Comment