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."
Microsoft just announced plans for their fix (Score:5, Funny)
Re:Microsoft just announced plans for their fix (Score:5, Funny)
Oh wait, it isn't and the bug is still at large. Sorry, jumped the gun there due to the speed at which the Open Source community usually fixes issues like this. Maybe ISO should take note.
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.
Re:Microsoft just announced plans for their fix (Score:5, Funny)
"It looks like you're trying to multiply two numbers. I can help show you how to use the Method of Finite Differences to find a good approximation of your answer using only addition and subtraction. Would you like me to bring up a wizard so that we can get started on finding an appropriate power series?"
Re:Microsoft just announced plans for their fix (Score:4, Funny)
Re:Microsoft just announced plans for their fix (Score:4, Informative)
Re:Pido libro de reclamaciones por daños. (Score:5, Funny)
Re:Microsoft just announced plans for their fix (Score:4, Funny)
Re:Microsoft just announced plans for their fix (Score:5, Funny)
No, no, no... remember this is effectively Office Vista.. so the fix will be, like:
You are trying to multiply 2 numbers and the answer is 65535. This is a very dangerous multiplication frequently used in viruses... Cancel / Allow?
Re:Microsoft just announced plans for their fix (Score:4, Insightful)
Re:Microsoft just announced plans for their fix (Score:5, Funny)
Oh, is that how it works? I thought it wouldn't show you answers above 255, unless you have a HDCP-compliant monitor.
Re:Microsoft just announced plans for their fix (Score:5, Funny)
Re:Microsoft just announced plans for their fix (Score:5, Funny)
Windows 5: "What the hell have you done? Delete it! Delete it!"
Windows 95: "Fuck it, we have to release this steaming pile of crap now.
Just a theory...
The reason it was named "Windows 95" (Score:4, Informative)
In OOXML? (Score:5, Funny)
Looks like a typo (Score:5, Interesting)
Someone was slacking in the testing department.
Re:Looks like a typo (Score:5, Informative)
I have another theory entirely. (Score:5, Informative)
...except for the minor detail that 65535 is 0xFFFF... :-)
That said, my 32-bit print routine for a 16-bit CPU actually works by printing two 16 bit numbers, with a slight hack to the 16-bit routine to allow it to print numbers in the range 65536 - 99999 for the lower 5 digits. It does this by dividing the 32-bit number by, you guessed it, 100000. It then prints the quotient and the remainder. It has to do some extra legwork, though, to get the leading zeros right across the two words, and I think it's there that the code went south if they're using a technique similar to mine.
I'm guessing what happened here is that there's an off-by-1 error in a comparison somewhere (i.e. ">= 65535" instead of "> 65535"), and the 32-bit quotient/remainder print routine kicks in. Since the number is already smaller than 100000, it probably hits a fall-thru case where the quotient is assumed to be 1, and there's no remainder, hence it printing 100000.
For reference, here's that assembly code I mentioned: prnum32.asm [spatula-city.org] and prnum16.asm [spatula-city.org]
--JoeI've got a theory... (Score:4, Funny)
Re:I have another theory entirely. (Score:5, Funny)
Re:I have another theory entirely. (Score:5, Funny)
Re:I have another theory entirely. (Score:4, Interesting)
As Harlan Grove pointed out in the thread, [google.com] some functions may be taking the value from the .Text property rather than the .Value property. Most things see the correct value despite it being misdisplayed.
Why would they do something goofy like that? I have no idea, and I don't have an easy way to check off-hand, at least not in Excel 2007.
And yet it works FINE when you GRAPH it... (Score:5, Informative)
I just fired up Excel and created a simple graph:
One column of numbers was a series from 845-855. The next column was the first column * 77.1. As expected, the series jumped from:
65149.5, 65226.6, 65303.7, 65457.9, 100000, 65612.1, 65689.2, 65766.3...
But then when I created a graph to display this, I had a simple straight line -- trying to plot the single data point represented by "100000" also displayed the accurate number. Any other calculations done with this number yielded the right result, too. Taking the value of the cell that displays100000 and multiplying it by 2 results in 131070.
So all things considered, this really amounts to an Easter Egg. Most spreadsheets will calculate, graph, and function exactly as they should even using the results from a cell that displays inaccurately in that one case...
Re:And yet it works FINE when you GRAPH it... (Score:5, Informative)
Re:In OOXML? (Score:5, Funny)
to retain backward compatibility with MSDOS 16bit mode, the operands of any multiplication that may exceeds a 16 bit boundary must be converted to farsi and multiplied using an abacus emulator, as per sec (II)par alpha comma 2; the result may or may not appear in Windows Genuine Octal Format (a.k.a. fake octal - that is octal without the leading zero and minus 1) for added convenience of EndUser(tm).
Re:In OOXML? (Score:5, Funny)
Re:In OOXML? (Score:5, Funny)
Re:In OOXML? (Score:5, Insightful)
Re: (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 compatibl
Re:Wow... (Score:5, Insightful)
Re:Wow... (Score:5, Funny)
Re: Your sig (Score:5, Funny)
No wonder you can't meet any women!
Re:In OOXML? (Score:4, Interesting)
I was surprised to find that the error actually had came up much earlier, but when the difference between cells is less than a certain amount, excel's equality resolves as true. So removing the last few entries, the book balanced again, even though the difference was now -2.18E-19. Clearly they are aware of this problem and are trying to hide it. If it was not actively fudging numbers there would probably be a lot more people having numerical problems. (numbers cited here are illustrations, I do not recall the exact values or precision)
Re:In OOXML? (Score:5, Informative)
Look at these articles.
http://support.microsoft.com/kb/q78113/ [microsoft.com]
http://c-faq.com/fp/printfprec.html [c-faq.com]
http://c-faq.com/fp/fpequal.html [c-faq.com]
Microsoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floating-point numbers.
Re:In OOXML? (Score:5, Insightful)
Pentiums (Score:5, Funny)
Re:Pentiums (Score:5, Funny)
Re:Pentiums (Score:5, Funny)
Is anyone using Excel 2007? (Score:5, Insightful)
Yes. (Score:4, Informative)
As an aside, when I went to pick up a lease renewal form for my apartment complex, I noticed that the lady at the front counter was also running Office 2007, so I'd say it's out there - just not exceptionally widespread at the moment, compared to other versions of Office.
The renewal form, hmmm? (Score:5, Funny)
Re:Is anyone using Excel 2007? (Score:4, Interesting)
I started with Excel 2.0*, in those days every release seemed to add significant new features (and floppies) and there was always a rush to install the new version and some excitement as you discovered the new features. By Excel '97 the program was so feature rich that most users never used more than a fraction of its functionality, same goes for Word '97. Where do you go from there?
(OOXML etc, I know).
*I think it was 2.0, it was 20 years ago.
Re:Is anyone using Excel 2007? (Score:5, Funny)
perhaps making a statement like this about a spreadsheet is not really a shining endorsement. let me know when you've tested the rest of them.
Re:Is anyone using Excel 2007? (Score:5, Funny)
They DO pay you to find bugs .. (Score:5, Interesting)
Bug finding is a valuable contribution to the value of any product. My current testing policy is to find the biggest pain in the ass in my user community - whoever has the largest bug count from the previous release. And give them the first beta. Every bug squashed improves the product for everyone. The cumulative value can't be ignored.
If you find bugs in any software, it's in your interest to report them, because you obviously want them fixed. If it's a commercial product, you may even be able to get more instant satisfaction. OTOH, for open-source products, I've had an instance where I was able to saunter into an IRC channel, mention a particular bug, and have the lead developer upload a new version to my server within 10 minutes, because he recognised the value of having a technically able user put his product through heavy stress.
Don't just swear and cuss about bugs in OpenOffice. Report them, send them copies of the files that break it. You might get your bug fixed. For free, in the next version. When did you last get that sort of deal from Microsoft?
Who are you going to call? (Score:5, Funny)
Give em a break, even the Count from Sesame Street cant count that high.
Re:Who are you going to call? (Score:5, Funny)
Ghostbusters?
Re:Who are you going to call? (Score:5, Funny)
But I think the Count can manage to count to six without stuttering.
Re:Who are you going to call? (Score:5, Funny)
Re:Who are you going to call? (Score:5, Funny)
1... 2... 3... 4... 5... 5 Mod points for Halcyon! {thunderbolt, lightning, kid-friendly-maniacal laughter}
Re:Who are you going to call? (Score:4, Funny)
But I think the Count can manage to count to six without stuttering.
Geez, way to go. Give 'em a chance to fix their current problems before you start finding new ones.
Google Spreadsheet bug (Score:4, Interesting)
It only gets worse. (Score:5, Funny)
Re:It only gets worse. (Score:5, Funny)
What's your point?
Re:It only gets worse. (Score:5, Funny)
Re: (Score:3, Funny)
Vista Sales (Score:4, Funny)
Microsoft Excel is the Worst Part of Office 2007 (Score:3, Interesting)
I use the Office 2007 versions of Word, Excel, and OneNote on a regular basis for my Windows business work; the *only* one I've had *any* trouble with is Excel. I can blue screen my machines with Excel on a regular basis; unfortunately, one of my paying customers requires Excel 2007 because they invested heavily in it (long before I was contracted), so I haven't got much choice, since .xlsx documents won't open in any FOSS spreadsheet apps.
For my personal work, I use Gnumeric on Linux.
Re: (Score:3, Insightful)
There, fixed that for you.
Special powers (Score:4, Insightful)
MSOffice doesn't run on the operating system, it runs the operating system.
Re:Special powers (Score:4, Interesting)
Try an install of the Visual Studio
Check a C multiply on that machine... (Score:3, Insightful)
Re:Check a C multiply on that machine... (Score:5, Insightful)
I can see the OOXML tag now... (Score:5, Funny)
=850*77.1
</MultiplyLikeExcel2007>
Re:I can see the OOXML tag now... (Score:5, Insightful)
Lets wait and see
Re:I can see the OOXML tag now... (Score:5, Funny)
Lets wait and see
Re:I can see the OOXML tag now... (Score:5, Funny)
Re:I can see the OOXML tag now... (Score:4, Funny)
Unnecessary abstraction (Score:5, Funny)
Re:Unnecessary abstraction (Score:5, Funny)
some limited testing (Score:5, Interesting)
425 154.2 100000
212.5 308.4 100000
8500 7.71 100000
but this evaluates correctly..
25 2621.4 65535
so it's not every multiplication that evaluates to 65535
I'm using Excel 2007 12.0.6024.5000
Re:some limited testing (Score:4, Informative)
that evaluates to 65535
but :
=850*(771/10) evaluates to 100000
Retro (Score:4, Funny)
Kinda Ironic (Score:3, Insightful)
Imperiled by binary decimals? (Score:5, Interesting)
Clearly the error is weirdly subtle, if 5.1*12850 gives the bugged behavior, but 8.5*7710 works just fine. In fact, I verified that all permutations of a bugged combination =A*B of the form =A/2*B*2 are bugged. Further...all of the buggy decimal values have no perfect floating point binary representation. 77.1 has an infinite binary expansion using IEE 754, while 8.5 has an exact representation. It seems likely that they are only using their BCD format (or whatever) when binary floating (or fixed) point just won't cut it, but then their internal->decimal conversion code chokes on 2^16 for some reason, while the binary (whether it is floating or fixed point) conversion works just fine (possibly because it doesn't have a boundary at 2^16--maybe it has its own threshold bugs
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.
This is why OOo Calc can never replace Excel (Score:5, Funny)
This is yet another example of where Calc fails utterly to be compatible with Excel. How can I use Calc if I can't be sure that it will produce the same answers that my boss gets with Excel?
All those open source developers just don't get it. Geeks that they are, they prize accuracy over consistency and uniformity. The clueless dweebs need to get out of their parents' basements and get a clue about how the REAL WORLD works. Nobody gets promoted for contradicting their boss, duh.
Nope, until Calc can faithfully reproduce every Excel calculation, it simply won't be ready for use in the real world.
Pay Day! (Score:4, Funny)
Not the only devastating bug (Score:4, Interesting)
If you type in 3.5 you will get "3. Mai". Now if you try to correct yourself by entering 3,5, the correct way to enter decimals in german, you will get "3. Januar". There just is no way for the unexperienced user to get back to normal.
The bug essentially is that the system allows dates without a year.
Step 2 Found! (Score:5, Funny)
MS Multiplan on Commodore 64 (Score:4, Interesting)
I have always been under the impression that Excel was originally based on MS Multiplan (isn't it?) so the code was correct at that time and has become broken at some subsequent point.
basic math errors in MS libs? Nothing new.. (Score:5, Interesting)
Back in the 386 days, the tan() function returned the wrong sgn if no coprocessor was present. Contacted and confirmed the error they simply ignored such a basic issue, and replied with 'use sin() and cos() functions instead'. Great.
It seems old habits never die!
Re:Oh no! (Score:5, Insightful)
Re:Oh no! (Score:5, Funny)
News at 11
Re:Oh no! (Score:5, Informative)
It could be for Excel users.
65535 is common in computing because it's the highest number which can be represented by an unsigned 16 bit binary. If Excel is mishandling it somewhere in the background, chances are that failure will show up at multiple points.
If I had an important Excel 2007 spreadsheet, I'd be loading it up in OOo Calc or an older version of Excel now.
Re:Oh no! (Score:4, Funny)
I checked [wikipedia.org], I'm not.
Re: (Score:3, Insightful)
None of the math or physics folks I work with would think about using Excel for their data.
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: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: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: (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:Oblig. (Score:5, Funny)
Re:obviously malicious (Score:5, Insightful)
Re:obviously malicious (Score:5, Insightful)
Re: (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
Re: (Score:3, Informative)
Re:Dunno... (Score:5, Funny)
What, lxvDXXXV?
(And yes, what have the Romans ever done for us, apart from apparently producing correctly functioning spreadsheet software?)
Re:But the important question is.. (Score:5, Funny)
As long as you stay below 65535 feet.
Re:So? (Score:5, Funny)
Just remember Micro$oft knows best... move along, nothing to see here.