Multiplication Bug in Microsoft Excel 2007

I didn’t believe this when I saw it at Slashdot (original Usenet post here), so I tried it myself. Open a copy of Excel 2007, and in any cell enter the formula =850*77.1. The answer should be 65535 (check it in you calculator), but it actually comes out in the Excel with the value 100000. A number of other calculations yield the same problem - try it out for yourself.

Pretty amazing, isn’t it? Multiplying two numbers together in Excel does not work for particular choices of numbers. (By the way, Microsoft Excel 2003 does not show this behaviour.)

This is obviously a big problem to anyone who uses Microsoft Excel for any kind of calculation work. Of course, you might think that relatively few calculations will throw up this error, i.e. just those calculations with floating-point numbers that yield 65535 as the answer, but that’s not the point. It’s just shoddily-written software, and therefore should not be used for serious analysis, especially for scientific or engineering work (imagine designing an aeroplane wing or bridge strut with this kind of mistake potentially creeping in). Actually, this bug is a problem for the very thing Excel was written to do, which is to allow you to multiply columns of numbers together! Hopefully my bank will use it to work out my balance, and I’ll come out 35 grand up - all I need is to get exactly 65535 pounds in credit….

With simple bugs like this in the program, you should start to wonder what other problems there might be in the way Excel does things, and well you should. This issue reminds me of a recommendation I read some time ago that Microsoft Excel (any version) should not be used for statistical analysis, since many of its functions are deficient; the difficulty here is that some of those deficiencies are subtle and therefore likely to not be noticed by the average user.

This is worse than not having the algorithms available at all, as it encourages people to believe that the algorithm that is provided works properly. I’ve had it said to me like this: ‘well, the authors of Excel wouldn’t have put the function into the application if it didn’t work, would they?’ Well, they did, and the algorithms are poor. Not convinced by the multiplication bug above? Then take a look at these articles:

I hope that convinces you of the basic piece of advice I repeat here: don’t use MS Excel for anything serious, if you use it at all.

Postscript: Wolfram have an article on the underlying issues in computer arithmetic. Worth reading.

September 25, 2007 | Filed Under Computing, Posts 

Comments

Leave a Reply