RSSMicrosoft Acknowledges Floating Point Bug in Excel 2007
By Scott M. Fulton, III | Published September 26, 2007, 10:51 AM
It's the type of bug that, when discovered twenty years ago on the world's leading spreadsheets of the time, generated controversy and sent thousands back to their software stores for refunds: Excel 2007 has a bug with the display of floating-point values, a Microsoft developer confirmed on his team's blog yesterday. BetaNews confirmed the bug's existence, which leads to some values in floating-point multiplication to be "rounded" to incorrect values.
The problem appears to be with certain kinds of pairs of values whose product is either 216 (65,536) or 216 - 1 (65,535). When one or both factors has floating-point precision but is only slightly fractional - meaning, it has perhaps one or two decimal places but is otherwise not "irrational," to borrow its mathematical meaning - Excel renders the product as 100,000 instead.
In BetaNews' tests, integer factors of 65,535 did not yield erroneous results, and nor did factors with long fractional trails. For example, the formula =88.3*742.18573 correctly rounded up to 65,535, even though in fact the true value is lower by an infinitesimally smaller amount. Users expect Excel to be able to round trivially unrounded values to more manageable amounts.
But it's this rounding feature that appears to be the problem, said Microsoft's David Gainer, not the calculation engine.
"The key here is that the issue is actually not in the calculation itself (the result of the calculation stored in Excel's memory is correct), but only in the result that is shown in the sheet," Gainer wrote. "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 (i.e. if A1 contains '=850*77.1', and A2 contains '=A1*2', A2 will return the correct answer of 131,070)."
BetaNews verified Gainer's contention, though it resulted in a pair of cells that made it appear that 100,000 times 2 equaled 131,070.
Gainer then tried to dampen the issue by asking, what are the odds of anyone ever actually encountering this problem? "Of the 9.214*10^18 different floating point numbers that Excel 2007 can store," he added, "there are 6 floating point numbers (using binary representation) between 65534.99999999995 and 65535, and 6 between 65535.99999999995 and 65536 that cause this problem."
Of course, Gainer's inquiry had already been answered by the fact that someone did discover this problem, albeit not in the beta test phase. His team is busy working on a fix, he said.
Its not just an in memory issue, try
Value in A1 =77.1*850, it will show 100000
In A2 enter =A1*2, you get 131070, this sugests its in memory
In A3 enter =A1+.1 (note the .) you get 65535.1
In A4 enter =A1+1, you now get 100001, so its not just in memory
A1-1 gives you 65534.
Score: 0
|Confirm these results.
Excellent example, thanks spider31!
Score: 0
|M$ had better release a patch quickly! Arguing that a certain combination of numbers rarely occurs didn't do Intel any good. People run a lot more than financial spreadsheets on Excel. There is no excuse for this error.
Score: 0
|Once again, Scott Fulton writes the best report on this story (check Techmeme for others by comparison). It's amazing — or stunning — the contortions a Microsoft rep will go through to justify a mistake instead of honestly admitting it. If only the Office team were as good as the Xbox team!
And don't forget that Microsoft is still trying to make this sad, mistake-filled spec an ISO standard! Go read Bob Sutor now on how bad September has been for Microsoft while being wonderful for the rest of us (and some reasons why Microsoft's 'office' franchise is dying from a thousand cuts).
Score: 0
|lol Figures. glad my business never touched 2007 outside of beta, and never will... Who needs to go through all the Re-training of staff and crap when office XP or 2003 works fine and has a standard interface that needs no more training? 2007 has a unsupported option to go back to the original interface true. but most company IT departments just do not care about such things... If you can't figure it out they make more money. so its good for them... Thats why its there ultimately...
Score: 0
|The apparent chances of anyone finding such a bug might seem slim, but it happens.
It reminds me of a problem I once had with a major statistics package. After running my data through the mainframe, several times even, "something" still just didn't feel right about the outputs.
After a bit of poking around, I found the problem. Someone had coded the value 0.0003 instead of 0.003 in one of the routines. Damn near pulled out my hair before discovering the problem.
My point? The chances of average Joe Blow discovering the problem are slim to none, but that handful of users who push the product to its limit very likely will.
Score: 0
|My guess is that this was implemented simply to f#%k with the EU while they are calculating how much of MS' and Apple's profits to which they are self-righteously entitling themselves.
Score: 0
|Out! Out with you.
That was a pointless comment and one for which you deserve to sit in the corner with a dunce hat on, I'm afraid.
Score: 0
|Not sure what the author is trying to say, but imaginary numbers many times have little to do with fractions, I think he must be referring to another concept - but I cannot think of what he is referring to FROM THE DESCRIPTION - perhaps he means irrational numbers.
Score: 0
|You're quite right, guru_v, that's the term I meant to use. I've corrected it above. Thanks for finding it.
-SF3
Score: 0
|Wow did microsoft actually just try and justify the bug at the end there? Sneaky! You go Microsoft!
Score: 0
|Use OpenOffice.org or Apple Numbers or Google Documents ;-)
Score: 0
|That is a good solution in a corporate office.
Score: 0
|We recently had a word doc that was from S Korea that Word could not print (and would hang the shell when attempting to do so.)
Open office printed it perfectly first try.
We use OO regularly, in fact we find it a quite good drop in for Office 2002, the last real version of Office we actually enjoyed. (We also liked Outlook 2003, but not the generic Office 2003 suite.)
Ah well. Corps eventually will learn spending $500/seat every few years won't pay off.
Score: 0
|Actually I experience such situations almost every day. I work for an European institution but I receive a lot of documents from CJK countries (written in English or Japanese - I fluently speak Japanese). I think that double-byte Office suites in general are not fully compatible with their European cousins. If I add that often not only the language version is different but also the version of the suite (I mean XP, 2003, 2007) I have to write that the interoperability of Ms Office is an illusion. What is interesting OpenOffice works with such documents with flying colours (there are some minor problems that the converted document is not really WYSIWYG sometimes, but the content is OK). I think that I do not have to mention that OpenOffice to OpenOffice document exchange is always correct.
Score: 0
|