Updated 2--Excel 2007 Still Can't Add

Posted by: Stephen Wildstrom on May 2, 2008

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.

Reader Comments

Surya

May 2, 2008 1:42 PM

Google spreadsheets calculates it to 0!

Susan Wildstrom

May 2, 2008 3:30 PM

Then Google spreadsheets is also having a computational problem since 0! (zero factorial) is actually equal to ONE (1) and not ZERO.

Ben

May 3, 2008 3:51 PM

Susan, I think Surya is using the exclamation point as a punctuation mark, not as a factorial sign :-P

Either way, I agree. This bug in Excel should be fixed. Nevertheless, it's pretty darn accurate.

Anyone know about OpenOffice?

Steve Wildstrom

May 3, 2008 8:19 PM

@Ben--It was an attempt at mathematical humor. And Excel does correctly compute =fact(0) as 1.

I don't know about OpenOffice, but I'll check Monday when I'm in the office--I don't have it running on a system at home. I expect it will be OK; I think this error is unique to Excel.

JP

May 5, 2008 1:36 AM

The same error happens in Mac Numbers spreadsheet. The value returned is the same as in excel (i.e. 8.6402E-12)

What is strange is that if you add the positive numbers together first and then add the negative number, you end up with 0. The problem only occurs when summing all the numbers at once.

pankaj

May 5, 2008 11:00 AM

You have to change the column type to Number (by right clicking). Once the columns are number type, the result is 0.

chandru

May 5, 2008 11:08 AM

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

Office 2007 excel ver. 2000 does calculate correctly if you can format the column to Number from General you would get precisely ZERO.

Anthony Jones

May 5, 2008 11:53 AM

A simpler example:
-100.07
100.01
0.06

SUM = 1.19349E-14

Anthony Jones

May 5, 2008 12:10 PM

The problem isn't actually something caused by Excel itself - well not directly. It comes more from how your computer stores floating point numbers - and is an issue that has been very well known for a while.

http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems

steve_wildstrom

May 5, 2008 12:30 PM

@Chandru--Spreadsheet users have to be very careful to distinguish between the displayed value of a cell and what is actually stored. The best way to find out for sure if a cell stores 0 is to test =if(=0,"true","false"). Only an output of "true" indicates equality. And this is not being persnickety--an error of this sort can cause catastrophic failures of a model.

Steve Wildstrom

May 5, 2008 1:09 PM

@Anthony Jones--The problems of rounding error and floating point representation have been known ab out since the dawn of computer. But it is incumbent on programmers, especially in an application like Excel. to sanity check results. That's what appears to have failed here.
(Back in mainframe days, IBM used a technique called binary-coded decimal to avoid floating point rounding errors on fixed-point values. Maybe we shoudl go back to it.)

DJP

June 5, 2009 1:16 PM

Another related glitch...say you are set up data points for calculation between -.095 and 0.95 in increments of 0.05. You enter -0.95 in A1 and then in A2 you do = A1+0.05 and then copy and paste this down the column....eventually you would get to zero and then increase to 0.95. BUT instead of zero you get a 1E-15 instead.

Post a comment

 

About

Bloomberg Businessweek writers Peter Burrows, Cliff Edwards, Olga Kharif, Aaron Ricadela, and Douglas MacMillan, dig behind the headlines to analyze what’s really happening throughout the world of technology. Tech Beat covers everything from tech bellwethers like Apple, Google, and Intel and emerging new leaders such as Facebook to new technologies, trends, and controversies.

Categories

 

BW Mall - Sponsored Links

Buy a link now!