Defensive Computing

If there is anything a computer should be able to do, it's compute. Apparently however, Excel 2002 and Excel 2007 have trouble with this, most basic, task.

Office-watch.com details a bug involving the addition of numbers with two decimal places. Simply put, Excel generates the wrong total. It comes close to the right answer, but at this point, it really should get the exact, completely correct answer - and it doesn't. See Excel SUM anomaly from April 29, 2008.

You may also want to verify that your copy of Excel has the necessary fix for another calculation bug described at office-watch.com in Excel's problem with 65,535 & 65,536 from September 2007.

Update: May 3, 2008. For those of you who don't read the comments Woody Leonhard left the following comment to this story. Basically what he wrote is a copy of a posting he did on his askwoody.com site called The Excel bug that isn't. Quoting Woody:

"...the problem has nothing to do with Excel. It's a congenital problem in the way computers represent decimal numbers with bits and bytes. Here's how I explained it last October, in Windows Secrets Newsletter: Excel works internally with binary numbers. It's therefore subject to all the myriad problems programmers encounter when they translate base-10 numbers into base-2 and back ... The number 0.1 can't be represented precisely in binary ... If you need precise decimal accuracy, you have to use a program that performs calculations in decimal. It's much slower than calculating in binary, but in some cases it's worth the effort. If you need to perform decimal arithmetic, take a look at an Excel add-in called RCOM, from Thomas Baier and Erich Neuwirth at the University of Vienna."

I respect Woody, having read his newsletter, and other writings of his, for a long time. But, on this point, I disagree with him. For one thing, I added the same numbers using the Calculator in Windows XP and got the totally correct answer, zero, not a number very close to zero. The Calc spreadsheet in Open Office also yielded exactly zero. For another, he is basically saying that computers can't do arithmetic. That adding 1 plus 1 may yield a number very close to 2, but not exactly 2 and that's the nature of the beast, so we should get over it.

Regardless of whether the computer internally uses base 2 numbers, or the Klingon numbering system, there is no excuse for a computer not to compute correctly. Perfectly correctly. Totally, 100% correctly. If you paid for Excel, you deserve perfect addition.

The article Woody links to at Wikipedia is about Floating point numbers. If Excel is, in fact, using floating point numbers internally, this is a mistake by Microsoft. A big one, if you ask me. Floating point numbers are not very accurate. All programmers know this, it has been true from the get-go. They are appropriate only for representing very large numbers. Using floating point numbers for decimal arithmetic on small numbers is a design mistake. One that the Calc spreadsheet and the Windows XP calculator don't make. I programmed applications in a mainframe environment for a decade and never once chose to use floating point numbers to represent anything. They were the wrong tool for the job.

Besides being mis-guided, Woody's comments scare me. They represent a mindset that I see all too often. Last year I wrote about Why Java can't do addition correctly which is another case of the same mindset. The designers of the Java language are very happy to let it add two numbers, get the wrong answer and continue running without raising an error condition. This is unacceptable, at least to me, and it should be unacceptable to you too.

And, just as an aside, IBM mainframes can calculate in decimal, natively.

See a summary of all my Defensive Computing postings.