• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Calculation anomalies in Excel® 2007: That darned penny!

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!

Like what you've read? ...Republish it and share great business tips!

Attention: Readers, Publishers, Editors, Bloggers, Media, Webmasters and more...

We believe great content should be read and passed around. After all, knowledge IS power. And good business can become great with the right information at their fingertips. If you'd like to share any of the insightful articles on BusinessManagementDaily.com, you may republish or syndicate it without charge.

The only thing we ask is that you keep the article exactly as it was written and formatted. You also need to include an attribution statement and link to the article.

" This information is proudly provided by Business Management Daily.com: http://www.businessmanagementdaily.com/13633/calculation-anomalies-in-excel-2007-that-darned-penny "

Leave a Comment