BotT: Excel 2007 has algebra difficulties…

Do you use MS Office 2007?
Well, then you probably noticed that Excel multiplies “850 x 77.1” as “100,000” instead of “65,535”.

Uh, you didn’t notice? Well neither did I until I read it all over the internet.
See the post on SlashDot for scoop, and see its comments for some good laughs. :)

There are explanations all around about how this bug came to appear in Excel.
The best one is probably Joel’s one (you may remember Joel from this post).

Go and read it. Joel is very deep in there and explains why the binary calculations can be confusing, and how the binary notation standards may or not help you developing better software.

I say “go and read it” because I’ll not repeat his explanation here :)
I will, however, quote the best parts from the questions at the end — very insightfull!

First insight is a matter for pondering about bugs, small bugs and the perils of fixing such:

Q: Isn’t this really, really bad?
A: IMHO, no, the chance that you would see this in real life calculations is microscopic. Better worry about getting hit by a meterorite. Microsoft, of course, will be forced to tell everyone “accuracy is extremely important to us” and I’m sure they’ll have a fix in a matter of days, and they’ll be subjected to all kinds of well-deserved ridicule, but since I don’t work there I’m free to tell you that the chance of this bug actually mattering to you as an individual is breathtakingly small.
And let’s face it — do you really want the bright sparks who work there now, and manage to break lots of perfectly good working code — rewriting the core calculating engine in Excel? Better keep them busy adding and removing dancing paper clips all day long.

Second insight, on the perils of automated tests (an automated test sees only what you ask it to see, hence it has blind spots and will not report for side-effects!).
(Of course, as Joel says, it is doubtfull that manual tests would ever exercise rare/random calculations):

Q: Shouldn’t they be testing for these kinds of things?
A: I’ll bet that most of the numeric testing done on the Excel team is done automatically with VBA code. Cells containing this value display as 100,000, but from VBA, they’re going to look like 65,535 (since the number would be passed into the Basic runtime in binary, before the display formatting.) I’m sure there’s plenty of code to test display formatting, but with a bug like this that only happens on 12 out of 18446744073709551616 possible floating point binary numbers, it’s unlikely that any set of black-box tests would cover this case.

One thought on “BotT: Excel 2007 has algebra difficulties…”

Leave a Reply