We all learned how to multiply with pencil and paper, even great big numbers and decimals. But when it comes to something important like a blueprint or a scientific formula we reach for a calculator – or a spreadsheet. That’s much more reliable, right? Well, not if the spreadsheet is Excel 2007. Over the weekend a member of the microsoft.public.excel newsgroup revealed that Excel 2007 thinks that 850*77.1 is 100,000. What’s the correct answer? Anybody? Anybody? Bueller? Anybody? Right, it should be 65,535. Other members verified that the error carries over into some (but not all) calculations based on the incorrect result. Microsoft has been informed of the bug, but hasn’t yet formulated a response.
UPDATE: Microsoft recognizes the problem and assures us that Excel Will Learn to Multiply.
GOOD NEWS: The Excel team has dissected the problem in detail and is working feverishly to swat this Excel bug.
If it were just 850*77.1 that gave a wrong answer, we could probably work around that. But there are tons of other problem numbers, as I discovered for myself. I set up a spreadsheet to divide 65,535 by every number from 1 to 65,535 itself, then multiply the number by that result. So, for example, it divided 65,535 by 26 to get 2,520.577. Then it multiplied 26 by 2,520.577 to get… 100,000?! Over ten thousand of these simple calculations gave the wrong answer.
We won’t know just why the problem comes up until Microsoft speaks out, but there is one thing about 65535 – it’s the very largest 16-bit number. In binary it’s a string of 16 ones. In hexadecimal (the programmer’s friend) it’s FFFF. But converting the “problem” results to hexadecimal in Excel yields FFFE. That’s surely a clue. Meanwhile, if you have any spreadsheets where some results hit the range around 65535, it might be a good idea to double-check with your trusty calculator… or a pencil.