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!
Yep - my office switched to Vista and Office 2007. Then again, we're a networking firm, so it's in our best interests to use stuff while it's still "beta" so we know the bugs and quirks before our customers start playing with it.
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.
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.
If they are using Excel to analyze their data, I don't want to drive over a bridge they are designing...
None of the math or physics folks I work with would think about using Excel for their data.
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!
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.
All about me
Just for the heck of it. There's always an offbeat chance that the multiply bug is in the CPU, not Excel....
This is my sig.
=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).
RK numbers in Excel. They're not quite how you described them, though. It's a 32-bit value that can hold a 30-bit floating point number or a 30-bit integer (the last 2 bits are for the type), and if a number can't be represented either way, Excel will save it as a 64-bit IEEE float.
Visual IRC: Fast. Powerful. Free.
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
Let's see, on one hand, we have a 16-bit integer. On the other hand, we have computers that can do almost a billion multiplies per second. Nope, shouldn't be a problem... oh wait. The Pentium bug was much more difficult to hit, yet with millions of them out there, people managed to hit it while doing important computations (money, safety, etc). Another post mentions structural engineers using Excel, and I doubt they are doing just simple sums and averages; Would you really want go through an earthquake in a building with safety margins calculated in Excel?
"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
If an application can cause a kernel fault, it's not only the application that is broken.
There, fixed that for you.
-- Alastair
I always said that Microsoft would never successfully migrate from 16 to 32 bits...
Parity: What to do when the weekend comes.
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?
I find this bug kinda ironic since one of the big pros attributed to Excel over OO Calc is that it is so useful for data manipulation. Yet, the number which results in the bug isn't ever a particularly big number - well within a practical use case.
"Thanks for all the money you paid to us. We've used it to buy off ISO among other things" -Microsoft
The bug shows up for me. I'm running on Vista, and my About box gives the Excel version as 12.0.6024.5000 (MSO 12.0.6017.5000).
Not the point. The fix would probably be available within minutes if the source code were open. Plus, a more rigorous review would have been triggered to make sure there aren't more errors of the same type...
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.
Obama likes poor people so much, he wants to make more of them.
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
Corollary: Any incompetence distinguishable from malice is not sufficiently advanced.
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.
MSOffice doesn't run on the operating system, it runs the operating system.
Lacking <sarcasm> tags,
Now how can I work this into my salary.......
These are some of the things molecules do...... given 4 billion years -Carl Sagan
Office 2K7 on XP SP2 and I see the same results as the article.
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.
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.
Agreed. But just because a buggy kernel lets an app cause a kernel fault doesn't mean that the app itself is not buggy also. The kernel bug needs to be fixed first, but that's no guarantee that the app will then behave properly.
-- Alastair
And you didn't balk at the 34% increase in rent?
This issue is a bit more complicated than you think.
You wanted to see 65,535 and other popular numbers in spreadsheet results? You need Excel Penultimate Edition for that sir!
"There is more worth loving than we have strength to love." - Brian Jay Stanley
Maybe they convert to base 9.18956?
That's a good point. Has someone with Excel 2007 tested whether a similar bug comes up with (2^32)-1?
A few observations:
1) If you go into the Visual Basic editor and print the "Value" property of the cell containing the formula =850*771 you get 65535, as you should. The "Text" property however is "100000". So it seems the problem is not in the internal computation, but in the conversion to the displayed value.
2) That said, I am not sure why, if you then enter =A1+1 into another cell you get 100001 instead of 65536. It seems the formula is picking up the displayed value, not the internal value when you perform addition, but not when you do multiplication.
3) There is something special about the value 77.1. Other variations on the calculation produce the correct result.
85 * 771 = 65535
850 * 77.1 = 100000
8500 * 7.71 = 65535
I wrote a short C program so I could see what the actual floating point representations of these numbers were:
#include
#include
int main (int argc, const char *argv[])
{
if (argc != 3)
{
puts("You must enter two numbers");
}
else
{
double v1 = atof(argv[1]);
double v2 = atof(argv[2]);
double result = v1 * v2;
printf("%f * %f = %f\n", v1, v2, result);
printf("%#016I64x * %#016I64x = %#016I64x\n", *(long long *)&v1, *(long long *)&v2, *(long long *)&result);
}
return 0;
}
This produced:
850.000000 * 77.100000 = 65535.000000
0x408a900000000000 * 0x4053466666666666 = 0x40efffdfffffffff
Notice that the value 77.1 has a repeating fraction when represented in floating point. So does the result. In fact if you dig out your Intel manuals and decipher the hex representation of the result you find that it is the binary equivalent of 65534.99999999999999...
It seems that the problem occurs in the conversion of that value to displayable text.
Well, I'm off to deposit $655.35 less my current balance into my bank account.
literally don't know ANYONE who does any math, whatsoever, in Excel. It's all tables and primitive databases. The guy in the next cube does some pretty graphs. That's as close as it gets.
That's it then, definitive proof. Microsoft can breathe easy tonight, because you don't know anyone who would be affected by this bug. Ego problem, at all?
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.
I bet they put that feature in for selling Vista volume licenses. In bundles of 850 the price is $77.10 each which is $100,000.
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!
why, what, how?
I know 65535... it was called a WORD back in the old days (16-bit). (And a WORD is an unsigned INTEGER).
All of the sudden if anybody just 'knows' stuff it is because he read in on an overhyped wiki?
It must be bunnies!
Stop worrying about the risks of nuclear power and start worrying about the risks of not using nuclear power.
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?
Actually it's quoted from Snow Crash.
Real engineers use the back of an envelope, a pencil and some rules of thumb.
Pirate Party UK
No wonder you can't meet any women!
Besides, 65535 is at the top end of the unsigned 16-bit int range, which seems an odd type to be using for spreadsheet calculations (where negative values are as likely as positive).
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.
My God! It's full of eval()'s.
Every time I start to have faith in humanity, I ruin it by driving to work between 7 and 8 am.
....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.
Yes and no, the definition of a WORD has typically been hardware specific. For example, on a PDP, a word was 18 bits, IIRC. However, it has always meant a bit-field, which doesn't make any assumption about type.
We at slashdot are scientists, specialists and kernel hackers. Your FUD will be found out.
I checked, I'm not.
Public use of any portable music system is a virtually guaranteed indicator of sociopathic tendencies. -- Zoso
I found this interesting.
+44 diggs by trib4lmaniac 3 hours ago
Of interest is the fact that 65,535 is the largest number that can represented by an unsigned, 16 bit integer (i.e. 0xFFFF).
After a little experimentation in VBA, I have found that 100000 is only returned when accessing the cells "Text" property. When asking for the cells "Value" 65,535 is returned as expected.
I created a quick macro to test this after filling cell A1 with the formula "=850*77.1"
MsgBox (Range("A1").Text) => Alerts 100000
MsgBox (Range("A1").Value) => Alerts 65535
Armaments, 2-9-21 And Saint Attila raised the hand grenade up on high, saying, 'O Lord, bless this Thy hand grenade' N
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.
Not a conspiracy! Ha! Who do you think came up with IEEE 754?! Hint: It certainly wasn't Linus Torvalds or Steve Jobs!
For those who don't remember, the reason for the "95" name was likely because Windows "Chicago" kept getting delayed. Eventually Gates announced it was going to be released under the name "Windows 95". Speculation was that the name was chosen to pressure the development team to get it released in 1995 instead of letting it slip to 1996. They released it in late summer 1995, arguably too soon.
more about 65535