Excel 2007 Multiplication Bug 806
tibbar66 writes with news of a serious multiplication bug in Excel 2007, which has been reported to the company. The example that first came to light is =850*77.1 — which gives a result of 100,000 instead of the correct 65,535. It seems that any formula that should evaluate to 65,535 will act strangely. One poster in the forum noted these behaviors: "Suppose the formula is in A1. =A1+1 returns 100,001, which appears to show the formula is in fact 100,000... =A1*2 returns 131,070, as if A1 had 65,535 (which it should have been). =A1*1 keeps it at 100,000. =A1-1 returns 65,534. =A1/1 is still 100,000. =A1/2 returns 32767.5."
Is anyone using Excel 2007? (Score:5, Insightful)
Re:Oh no! (Score:5, Insightful)
Re:Good Luck! (Score:3, Insightful)
None of the math or physics folks I work with would think about using Excel for their data.
Re:obviously malicious (Score:5, Insightful)
Re:In OOXML? (Score:5, Insightful)
Re:Microsoft Excel is the Worst Part of Office 200 (Score:2, Insightful)
Check a C multiply on that machine... (Score:3, Insightful)
Re:Good Luck! (Score:5, Insightful)
What are the odds of that bug only affecting that number? It's a symptom that the underlying routine is totally fscked, in ways that could cause nasty surprises.
If it gave the wrong answer all the time you'd know it was crap and would just ignore the whole thing. It's when it only gives wrong answers some of the time that can lead you into a false sense of confidence.
You've also got to wonder, if it worked fine in previous versions of Excel, what the frack they were messing with to hose it up. It's not like somebody changed the rules of arithmetic recently, did they?
Re:Good Luck! (Score:3, Insightful)
Re:Good Luck! (Score:5, Insightful)
"No it doesn't really affect anything. "
Oh, it's only the largest unsigned 16 bit number and comes up in a crapload of places. Enough times that you should have _memorized_ it by now.
Jeez. Please turn in your geek card NOW.
--
BMO
Re:Microsoft Excel is the Worst Part of Office 200 (Score:3, Insightful)
There, fixed that for you.
Re:obviously malicious (Score:5, Insightful)
Kinda Ironic (Score:3, Insightful)
Re:I can see the OOXML tag now... (Score:5, Insightful)
Lets wait and see
Re:In OOXML? (Score:3, Insightful)
What? Hey, stop laughing. I'm serious! You should see these things.. Massive diagrams all built using the draw toolbar in Excel...
Personally, I've found Visio to work out nicely for network drawings, but apparently the other engineers like Excel...
Speaking of which.. Anyone know of a decent OSS replacement for Visio? And no, dia doesn't count. It's nowhere near what Visio does. Nor does it save a Visio compatible file.
Re:obviously malicious (Score:3, Insightful)
With Microsoft, we will be waiting for days before they even notice it...
As such, their product stands unusable, possibly for weeks. And there is nothing we can do about it, not even sue for lost profits or damages.
I am pretty sure those that bought Office at an academic/company discount would also not be able to return it.
What happened to the beta tests?! (Score:5, Insightful)
For that matter, they probably assign people to create scripts to randomly create calculations and test the results. However, after reading a bit of the Usenet thread, automated scripts might not have caught the problem, it seems that it is at the rendering layer - using VB to get the cell value apparently gets the correct value.
Weird. And highly embarrassing.
I can't wait for the advertisements from OpenOffice (and it's new allies in IBM and Google) to play this up! Apple will have a field day too -- "Hi, I'm a Mac. Sure I'm good at video and music and all that fun stuff, but I can also do math. I know that 65,535 doesn't equal 100,000." -- OK, maybe that wouldn't be TV worthy, but I'll make a good web ad for Slashdot et. al.
Special powers (Score:4, Insightful)
MSOffice doesn't run on the operating system, it runs the operating system.
Re:Is anyone using Excel 2007? (Score:2, Insightful)
Re:Check a C multiply on that machine... (Score:5, Insightful)
Re:Good Luck! (Score:5, Insightful)
I'm not the only one that recognised this as the 16bit number (see some of the other posts in this thread).
I'd wager a fair amount of money to say that numbers under 65535 are represented as 16 bit ints but anything over that number is changed on the backend to possibly a 32bit number. This allows faster calculations of smaller numbers (which happen quite a lot) while still allowing higher numbers to be used. The developers created this system, but forgot to test the edge cases (though it can be argued that they don't test any cases before release). If it were to happen to any other number, it would either be at the 8 bit number (though I doubt it) or at the 32/64 bit number they use for the larger values.
Re:Microsoft Excel is the Worst Part of Office 200 (Score:3, Insightful)
Re:Microsoft just announced plans for their fix (Score:3, Insightful)
The Number That Must Not Be Named (Score:3, Insightful)
Re:It only gets worse. (Score:2, Insightful)
Re:Wow... (Score:5, Insightful)
Re:Google Spreadsheet bug (Score:2, Insightful)
Now, unless I'm mistaken, this reduces to:
4*5*7*11*17*29*31*37*41*43*47*53
which you'll notice is the multiplication of all prime numbers between 53 and 27 (inclusive). I can't spot any further properties, hence probably need somebody to check this.
Re:Dunno... (Score:2, Insightful)
You'd better watch out, if your knee keeps jerking that high you might catch yourself on the chin.
Re:I have another theory entirely. (Score:3, Insightful)
please dig this story and get it to the masses (Score:1, Insightful)
Re:Good Luck! (Score:5, Insightful)
spreadsheets have their place.
Just about every engineer I know (and yes, IAAE)uses Excel for calculations with no issues at all. Yes they use more specialised (and expensive) tools for things like Finite Element analysis (ie bridges, etc.) and so on, but a lot of everyday stuff gets done on spreadsheets.
Everything from hydraulic calculations to reinforcing quantities can be done in a spreadsheet.
However it would be a mistake to rely solely on results produced by any software without using some judgement on the results. Problems come when people get too trusting of software, whether its a spreadsheet or a $20,000 Analysis package.
Mind you, none of the engineers I know are using Excel 2007 yet, and aren't likely to while this sort of problem is out there.
Re:Microsoft just announced plans for their fix (Score:0, Insightful)
Re:Microsoft just announced plans for their fix (Score:4, Insightful)
SIMD instructions (Score:3, Insightful)
This depends on the type of data that is being worked on. A financial ledger may have numbers that are positive, negative or some scalar of i . Census or poll data, mileage charts, employee time-sheets or transactions/time (to name a few),however, will seldom have negative numbers.
A large number of x86/x64 systems now have support for SSE and MMX instructions which, if done right, could provide a significant speed increase when working with a large set of small numbers. However, many instructions in these extensions behave in an data destructive manner when an overflow condition is encountered. For example, when working with saturation arithmetic instructions you may compare the result with $FFFF and, on a match, branch to code that handles a saturation condition on the assumption that the real result is greater than $FFFF. Such an approach works fine until you encounter a situation where $FFFF is the correct result but the program flow jumps to the saturation handler anyway, possibly with garbage data stuffed in registers that would normally be used to pass values to the handler.
Of course it's just speculation that they attempted to optimize the offending function in assembly; and it's further speculation that they chose to optimize it with SIMD instructions. However the fact that this bug appears right at the edge of the unsigned 16-bit integer range really does suggest that somebody was trying to do something clever in assembly and failed to account for all possible conditions.
If anyone out there is bored and has read this far, try timing a few operations on a large set of small integers in Excel 2007 and then toggle off SSE/SSE2 in BIOS and time the same set again. I'm curious as to whether or not MS did do this sort of optimization but I'm not curious enough to borrow a copy of Excel from The Pirate Bay just for this test.
Re:Microsoft just announced plans for their fix (Score:1, Insightful)
Re:Microsoft just announced plans for their fix (Score:3, Insightful)
http://en.wikipedia.org/wiki/OS_X#Versions [wikipedia.org]
Re:In OOXML? (Score:5, Insightful)
Re:In OOXML? (Score:3, Insightful)
Re:And yet it works FINE when you GRAPH it... (Score:3, Insightful)
You know, some people actually read and use the displayed results from Excel for important purposes, rather than merely feeding them into further calculations, graphs, etc. Where the cell is an ultimate output, this isn't just a harmless display error that doesn't have any functional impact.
Re:In OOXML? (Score:3, Insightful)
They need to use "decimal".
The decimal type is floating point, just not binary. But yes, that's the type to use, because the problem isn't the floating point, but the base conversions.
Re:Microsoft just announced plans for their fix (Score:3, Insightful)
Re:Microsoft just announced plans for their fix (Score:5, Insightful)
Typical Slashdot. In the end the vast majority of people who use Firefox aren't even coders. You seriously want them to grab the fix from CVS and backport it to their own copy? My God man, these people don't even know what make does, and what are you suggesting?
There's nothing for open source apologists to gloat about when it comes to software fixes. As far as any normal user is concerned, bug fixes take just as long to come down the pipe as proprietary software. Nobody gives a hoot if it's fixed in CVS, if it's not fixed on their machine it's not fixed at all.