Updated 2--Excel 2007 Still Can't Addby
SECOND UPDATE 5/5 4 pm EDT Commenter Anthony Jones is absolutely correct—it’s a floating-point rounding error, described in Microsoft Knowledge Base article 214118. The article offers a couple of workarounds, neither very satisfactory.
The interesting question is how Google Spreadsheets and Zoho Sheets avoid the error. Perhaps by using internal fixed-point representations.
Incidentally, with default precision settings, Wolfram’s Mathematica generates the same not-quite-zero result as Excel.
UPDATED 5/5. 9:30 am EDT
It turns out Microsoft is not alone in its arithmetic difficulties. Here’s the rundown on various spreadsheets’ ability to sum this column of figures correctly:
Right OpenOffice 2.3.0 (Linux) Google Spreadsheets Zoho Sheets
Wrong Excel 2007, 2003, 2008 (Mac) Apple Numbers ‘08
I’d be interested in hearing if anyone knows about older versions of Numbers of ever AppleWorks. It would be interesting to trace the history of this bug; it might tell up who reversed-engineered whose code.
I’m still waiting for a response from Microsoft.
What is it with Microsoft and arithmetic? Excel 2007 has been plagued by mathematical anomalies and this lastest was just pointed out by Woody’s Office Watch, an on-line newsletter devoted to all things Office.
Enter this column of figures in Excel 2007 and sum the numbers:
Enter this column of figures in Excel 2007 and sum the numbers: -127551.73 103130.41 1807.75 7390.11 9028.59 2831.26 1568.90 1794.71 The result should be precisely zero, and thats what you'll see if you've fixed the number of places to the right of the decimal point to be displayed at 10 or less.
Unfortunately, the result that Excel actually has calculated is 8.6402E-12, or 0.000000000008640199666843. That may seem like too small a discrepancy to matter, but but there is a huge distinction between almost zero and identically zero. Say you have a formula doing a comparison, such as =if(a10=0,"True","False"). It will return False when the result should be True, and that sort of error can lead to big errors in models.
Excel is undoubtedly the most widely used mathematical software in the world, and a lot of people place a lot of trust in its accuracy. They shouldn't, but they probably have little choice. Microsoft should do everything possible--and quickly--to exterminate Excel's math bugs. UPDATE The same error occurs in Excel 2003 and Excel:mac 2008.