Hmmm…. Some people are claiming that Excel has difficulty with multiplication. (See image above.)

Over the weekend members of the microsoft.public.excel newsgroup reported a serious bug in calculations made by Excel 2007. Initially it just seemed that Excel Can’t Multiply. The bad news is, Excel can’t do other operations correctly either. The good news is, this is only true when the results are within a very specific range of numbers. And the better news is that the Excel team has mapped out the nature of the problem and is feverishly working on an update.

So what went wrong? It all boils down to the fact that you can’t represent an infinite group of non-integer numbers using a finite number of bits. In fact, Excel can store “only” about nine quintillion distinct values. The numbers going into your calculations may be infinitesimally different from the number displayed, and for two calculations that nominally have the same answer the result may be infinitesimally different. Excel generally manages just fine in dealing with these tiny differences, but in exactly 12 instances out of the nine quintillion possibilities it goes completely bonkers.

The Excel team discovered that it wasn’t just calculations involving 65,535 that were affected; those that should come out to be 65,536 were also sometimes wrong (returning 100,001 instead). And they discovered it wasn’t just multiplication. In truth, the operation didn’t matter, only the result. When they work up a fix it will still have to “make its way through our official build lab and onto a download site”, but they expect it to happen soon.

Useful fact: a quintillion is 1 followed by 18 zeroes.

The funny thing is that my copy of Excel does not make this mistake (see below).

But then, I’m running Excel 2004 for Mac.