06 Jul2011
Written by Dustin Wheeler, CPA. Posted in Excel
When I was in first grade, I learned that 2 + 2 = 4. Excel, who I have come to know and trust, thinks the answer is five. How is this possible?
The truth is told after I increase the decimal places shown for each of these cells. The twos on the spreadsheet really aren’t twos after all. They’re 2.43 and 2.35, which both round down to two. The sum, 4.78, rounds up to five.
To fix this kind of rounding difference in Excel 2010, click on the File tab and go to Options. On the left side of the window that appears, click on Advanced. Scroll down and check the box next to “set precision as displayed.”
A window will appear, warning that “data will permanently lose accuracy.” For example, when I decrease the decimal so that all of the numbers are shown as whole numbers, they actually become whole numbers (increase the decimal again, and both twos will be shown as 2.00). Don’t select this option if you need complete data accuracy in your spreadsheet. I can’t be held responsible for a few lost pennies.
This option is especially helpful for an accountant whose report must always foot.
Thanks to Val Steed of K2 Enterprises for showing this in a session at the 2011 AICPA TECH+ Information Technology Conference.