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

[by Anonymous Coward]

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

In OOXML?

[gvc (167165)]

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

Re:In OOXML?

[by Anonymous Coward]

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.

Pentiums

[MyLongNickName (822545)]

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

Re:Pentiums

[644bd346996 (1012333)]

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

Re:Pentiums

[Fuji Kitakyusho (847520)]

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

Is anyone using Excel 2007?

[QuantumG (50515)]

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

Who are you going to call?

[renegadesx (977007)]

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?

[dvonhand (1136711)]

Who are you going to call?

Ghostbusters?

Re:Who are you going to call?

[AJWM (19027)]

1 2 3 4 5 5 6 7 8 9

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

It only gets worse.

[The Earl of Sandwich (1160741)]

Try =6*9.

Re:It only gets worse.

[$RANDOMLUSER (804576)]

Try =6*9.

6 * 9 = 42
What's your point?

I can see the OOXML tag now...

[mad.frog (525085)]

<MultiplyLikeExcel2007>
        =850*77.1
</MultiplyLikeExcel2007>

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

[ameline (771895)]

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

Lets wait and see :-)

Unnecessary abstraction

[mikvo (587789)]

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

[Rick Genter (315800)]

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

some limited testing

[indaba (32226)]

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?

[Protoslo (752870)]

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

Re:Oh no!

[timmarhy (659436)]

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

Re:Oh no!

[renegadesx (977007)]

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

News at 11

Re:obviously malicious

[Cassius Corodes (1084513)]

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

Re:obviously malicious

[Belacgod (1103921)]

Sufficiently advanced incompetence is indistinguishable from malice.

Re:Good Luck!

[AJWM (19027)]

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!

[bmo (77928)]

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

[Ford Prefect (8777)]

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