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."

Microsoft just announced plans for their fix

They will be disabling multiplication in all future versions of Excel.

Re:Microsoft just announced plans for their fix

It is nice to see that since this is a free and open standard that the bug has been identified quickly and fixed.

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

Microsoft already has a patch in the works to help users overcome this issue. Whenever the user types a '*' in a formula, an animated sprite of Charles Babbage's head will pop up. It will show this bubble caption:

"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

They will be disabling multiplication in all future versions of Excel.

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

Windows 4: "Oh, man. The previous one was garbage, but this one is making me physically ill."
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...

In OOXML?

Perhaps this is how multiplication is done in OOXML. They do leap years in dates wrong, too.

Looks like a typo

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.

Re:Looks like a typo

Wouldn't that apply to 65,536, rather than 65,535? 65,535 is 0xFFFF, not 0x10000.

I have another theory entirely.

...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]

--Joe

Re:In OOXML?

Perhaps this is how multiplication is done in OOXML. They do leap years in dates wrong, too.

oh my, it is clearly spelled on page 890 of the draft:
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?

I use Matlab and Octave constantly for things everyone around me uses Excel for (I do structural engineering). I am no amazing hacker or anything, but I simply find it scads easier to use that sort of paradigm over the spreadsheet analogy for almost any application. That aside, Excel in particular seems constantly to try to outthink me and consistently to have these sorts of strange calculation errors.

Re:Wow...

CAD derivatives are quicker, less precise, like a blaster vs a light saber, only few use light sabers, many use blasters.

Re:Wow...

a spot-on analogy involving jedis? you sir win two internets.

Pentiums

What happens if you use this on an older Intel chip? Do the issues cancel out?

Re:Pentiums

Perhaps. I tried to test it out, but the hard drive with the swapfile caught fire before Excel finished loading.

Re:Pentiums

Not quite. The cell in that case just reads 99,999.999998263, when it should be 65,534.99994721.

Is anyone using Excel 2007?

I bought a Dell this year, it came with Office 2003.

Re:Is anyone using Excel 2007?

Of course you did. You don't think they're going to pay you to do bug testing for them do you?

The renewal form, hmmm?

And you didn't balk at the 34% increase in rent?

Who are you going to call?

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.

Re:Who are you going to call?

Who are you going to call?

Ghostbusters?

Re:Who are you going to call?

1 2 3 4 5 5 6 7 8 9

But I think the Count can manage to count to six without stuttering.

Re:Who are you going to call?

if you were a real fan you'd know that's when the Counts repeats a number and then comments how wonderful the items being counted are. e.g. five...five *wonderful* vampire bats! mwuhahahahaha!

Re:Who are you going to call?

No, dude, you have it wrong. The Count always stops to repeat every now and then, to remind you of the objects being counted.

1... 2... 3... 4... 5... 5 Mod points for Halcyon! {thunderbolt, lightning, kid-friendly-maniacal laughter}

It only gets worse.

Try =6*9.

Re:It only gets worse.

Try =6*9.

6 * 9 = 42
What's your point?

Re:It only gets worse.

So this means that Microsoft is the fundamental flaw in the universe? ...Okay, I'm convinced.

I can see the OOXML tag now...

<MultiplyLikeExcel2007>
        =850*77.1
</MultiplyLikeExcel2007>

Re:I can see the OOXML tag now...

That profoundly unfunny post (but insightful) will probably get modded as funny.

Lets wait and see :-)

Re:I can see the OOXML tag now...

That profoundly uninsightful post (but funny) will probably get modded as insightful.

Lets wait and see :-)

Unnecessary abstraction

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).

Re:Unnecessary abstraction

I'd prefer to put 850 in 77.1 cells and do =sum(a1:a77.1)

some limited testing

original 850 77.1 100000
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

Imperiled by binary decimals?

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.

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 ;p).

What happened to the beta tests?!

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 why OOo Calc can never replace Excel

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.

Step 2 Found!

Well, I'm off to deposit $655.35 less my current balance into my bank account.

Re:Oh no!

well, it could very well lead to massive fuckups if anyone happens to make a decision based on the flawed result.

Re:Oh no!

This just in: Florida plans to do use Microsoft Excel to calculate the 08 election results.

News at 11

Re:Oh no!

This is the most devastating bug in years.

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:obviously malicious

Never attribute to malice what can be adequately explained by incompetence.

Re:obviously malicious

Sufficiently advanced incompetence is indistinguishable from malice.

Re:Good Luck!

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?

Re:Good Luck!

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.

Re:Good Luck!

"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

Re:Good Luck!

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.

Re:Dunno...

My copy of excel 200 on Win2k gave the correct result.

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..

Does the built-in flight simulator still work?

As long as you stay below 65535 feet.
     

Re:So?

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.

Re:Check a C multiply on that machine...

A number of people have verified the bug, and it's quite improbable they ALL have the same model of CPU.