What's 77.1 x 850? Don't ask Excel 2007
65,535 = the Number of the Beast
What you need to know about cloud backup
A Microsoft manager has confirmed the existence of a serious bug that could give programmers and number crunchers a failing grade when relying on the latest version of Excel to do basic arithmetic.
The flaw presents itself when multiplying two numbers whose product equals 65,535. Fire up your favorite calculator and multiply 850 by 77.1. Through the magic of zeros and ones, you'll quickly get an answer of 65,535. Those using the Excel 2007, however, will be told the total is 100,000. The program similarly fails when multiplying 11 other sets of numbers, including 5.1*12850, 10.2*6425 and 20.4*3212.5, according to this blog post from Microsoft manager David Gainer.
He stressed that the bug, which was introduced when Microsoft made changes to the Excel calculation logic, occurs only in the value Excel displays in a cell. The result stored in memory is correct. "Said another way, 850*77.1 will display an incorrect value, but if you then multiply the result by 2, you will get the correct answer," Gainer wrote.
Of the 9.214*10^18 different floating point numbers that Excel 2007 can store, six of them are susceptible to the flaw.
We're still at a loss as to why the latest and greatest version of Excel would get tripped up on equations totaling 65,535. The number, of course, is the highest that can be represented by an unsigned 16 bit binary number, so people who muck around with computers encounter it on a daily basis. For example, Internet protocol supports 65,535 TCP and UDP ports.
Microsoft engineers are in the final phase of testing a fix. It should be available for download soon, Gainer said. ®
COMMENTS
Re: Re: Software vs Hardware
"apart from the aggressive and slightly substandard intelligence / illiterate manner in which you raised it"
Oh well, I can't be bothered to put a disclaimer at the bottom of everything I write stating that I am not a native speaker of English, and have been speaking it for 5 years now. And no, I'm not 5 years old, before someone inevitably makes the suggestion. :-)
And it's not like your English is exactly stellar there either, I'm afraid. Maybe you have the same excuse as I do?
Now, why are you afraid of the aggressiveness? Chill, nobody is after you...
HAHAHA
My wife is a "number cruncher" and has been using excel 2007 since March, at the time I told her that her IT guy was nuts - she confirmed the undocumented feature. She is the only one in the office with that version, it had/will not be/en tested - she was new, got a new computer with "un-tested" new software ....
Lotus 123 'bug' and XLOPERs
But it is unlikely that the date calculation was a 'bug' in Lotus 123. It simplified the arithmetic and data storage requirements, so it was a reasonable trade off for the hardware at the time. It's not a reasonable trade off for the hardware available now.
But the (Lotus 123 compatible) spreadsheet is the reference calculation for most of business right now, and the painful compatibility mode in Open Office shows just how difficult it is to get around that problem.
If the Open Office example solution to the compatibility problem wasn't so fragile and error-prone, I'd have a lot less sympathy for the Microsoft position on standard spreadsheet formats.
By the way, values in MS spreadsheets are stored as XLOPERS, which became variants in VB. XLOPERS are structures which may have integer/float/string etc values. The calculation engine is a highly optimised hand-tuned piece of code which recalculates only affected cells, not every cell on the sheet, so it does a graph analysis as well.
Simple math libraries like the standard c library typically just use simple rounding to get the display format for floating point numbers. A slightly more complex algorithm gives you rounding to the shortest posible correct decimal. So cPython shows 65534.999999999993, and Excel is supposed to show 65535, but it's the underlying binary value that is used for calculations
Excel only rounds to the display format for display. What we have here is an error in the Mathematics used to calculate the display value. We have examples of three binary floating point numbers where the display value calculation is wrong, and examples of how to generate them. Has anyone published the 'other 3', or was the theory that there were 'just 6' a bit of wishful thinking?
IT infrastructure monitoring strategies
What you need to know about cloud backup
Enabling efficient data center monitoring
Agentless Backup is Not a Myth
Top 10 SIEM implementer’s checklist
