Slashdot Mirror
Excel 2007 Multiplication Bug
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."
They will be disabling multiplication in all future versions of Excel.
Perhaps this is how multiplication is done in OOXML. They do leap years in dates wrong, too.
What happens if you use this on an older Intel chip? Do the issues cancel out?
See my journal for slashdot ID's by year. Mine created in 2005. http://slashdot.org/journal/289875/slashdot-ids-by-year
I bought a Dell this year, it came with Office 2003.
How we know is more important than what we know.
1 2 3 4 5 5 6 7 8 9 10 11 12... ...65,533 65,534 100,000
Give em a break, even the Count from Sesame Street cant count that high.
Make SELinux enforcing again!
Open a Google Docs spreadsheet. Type =COMBIN(55,27)
-- Subvert the dominant paradigm. Repeat as desired. http://ownlifeful.com/
well, it could very well lead to massive fuckups if anyone happens to make a decision based on the flawed result.
If you mod me down, I will become more powerful than you can imagine....
Try =6*9.
It's used in the algorithm that MS uses to report Vista sales.
Never attribute to malice what can be adequately explained by incompetence.
Control is an illusion, order our comforting lie. From chaos, through chaos, into chaos we fly
This just in: Florida plans to do use Microsoft Excel to calculate the 08 election results.
News at 11
Make SELinux enforcing again!
=850*77.1
</MultiplyLikeExcel2007>
Multiplication is an unnecessary abstraction anyway. This should really be represented by summing the value of 77.1 entered independently into 850 cells: =sum(a1:a850).
Yeah...cause the chances of having a number evaluate to 65,535 via multiplication is extremely common.
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?
-- Alastair
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
"cause the chances of having a number evaluate to 65,535 via multiplication is extremely common."
"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
Sufficiently advanced incompetence is indistinguishable from malice.
What, lxvDXXXV?
(And yes, what have the Romans ever done for us, apart from apparently producing correctly functioning spreadsheet software?)
Tedious Bloggy Stuff - hooray?
It sounds like they are doing small-number math in one representation (perhaps they use short fixed-width decimal representations) and then switching to another method (arbitrary length decimal numbers?) at the binary-inspired boundary 2^16...but somehow they got it mixed up with a different decimal boundary in the edge case.
;p).
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
Crazy stuff. I could have sworn that MS had some resources allocated to doing huge beta tests.
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 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.
Note to ACs: I usually delete AC replies without reading them. If you want to talk to me, log in.
Does the built-in flight simulator still work?
As long as you stay below 65535 feet.
Table-ized A.I.
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.
"I've got more toys than Teruhisa Kitahara."
Was it not Bill himself who said you would never need more than 64K of memory? Well it's official, you don't need any number greater than 64K either!
Just remember Micro$oft knows best... move along, nothing to see here.
A number of people have verified the bug, and it's quite improbable they ALL have the same model of CPU.
I'd say the chances of it only effecting this number are quite high.
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.
In the german version there is a bug for almoust a decade now.
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.
And you didn't balk at the 34% increase in rent?
This issue is a bit more complicated than you think.
Well, I'm off to deposit $655.35 less my current balance into my bank account.
Surprised noone has pointed this out yet, but this is likely a hardcoded constant being used when excel converts from a 16 bit number to a 32 bit. It should have been "0x10000" but instead was 100000. Speculation sure, but it looks pretty likely, I mean how else would 100000 randomly appear when you did that computation?
Someone was slacking in the testing department.
CAD derivatives are quicker, less precise, like a blaster vs a light saber, only few use light sabers, many use blasters.
Under the influence of Post-Cyberpunk Gonzo Journalism
a spot-on analogy involving jedis? you sir win two internets.
... still waiting for this free-as-in-beer free beer I keep hearing about.
Just for fun I cranked up Microsoft Multiplan 1.06 from 1983 for the Commmodore 64 (using the Vice emulator, and the magic calculation (850*77.1) gives the correct answer of 65535.
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.
If you're a zombie and you know it, bite your friend!
...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 and prnum16.asm
--JoeProgram Intellivision!
Rubbish.
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.
If my call is important, why am I talking to a recording?
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!
What's in a sig?
... your payment is a free office suite.
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?
No wonder you can't meet any women!
....interesting twist...
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...
I would have to say that explosives are the most abused technology in all of history.
Wow, that was a lot of work to demonstrate that a number ending in a 5 isn't prime.
I read the internet for the articles.