typodupeerror

## Excel 2007 Multiplication Bug806

Posted by kdawson
from the be-fruitful-and-all-that dept.
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."
This discussion has been archived. No new comments can be posted.

## Excel 2007 Multiplication Bug

• #### Microsoft just announced plans for their fix (Score:5, Funny)

by Anonymous Coward on Monday September 24, 2007 @09:38PM (#20737847)
They will be disabling multiplication in all future versions of Excel.
• #### Re:Microsoft just announced plans for their fix (Score:5, Funny)

on Monday September 24, 2007 @10:58PM (#20738507)
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 (Score:5, Funny)

on Tuesday September 25, 2007 @12:33AM (#20739211)
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 (Score:4, Funny)

on Tuesday September 25, 2007 @04:09AM (#20740335)
What you need is specialised hardware [wikipedia.org].
• #### Re:Microsoft just announced plans for their fix (Score:4, Funny)

on Tuesday September 25, 2007 @12:46AM (#20739281)
This doesn't work either: =850/(1/77.1) So they have to disable the divide-operation altogether.
• #### Re:Microsoft just announced plans for their fix (Score:5, Funny)

on Tuesday September 25, 2007 @12:47AM (#20739287) Journal
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 (Score:4, Insightful)

on Tuesday September 25, 2007 @04:47AM (#20740509)
...and if you click allow and get the wrong result, then it's all your fault.
• #### Re:Microsoft just announced plans for their fix (Score:5, Funny)

on Tuesday September 25, 2007 @07:06AM (#20741217)

You are trying to multiply 2 numbers and the answer is 65535. This is a very dangerous multiplication frequently used in viruses... Cancel / Allow?

Oh, is that how it works? I thought it wouldn't show you answers above 255, unless you have a HDCP-compliant monitor.
• #### In OOXML? (Score:5, Funny)

on Monday September 24, 2007 @09:40PM (#20737849)
Perhaps this is how multiplication is done in OOXML. They do leap years in dates wrong, too.
• #### Looks like a typo (Score:5, Interesting)

on Tuesday September 25, 2007 @12:35AM (#20739225)
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 (Score:5, Informative)

on Tuesday September 25, 2007 @01:05AM (#20739397) Homepage
Wouldn't that apply to 65,536, rather than 65,535? 65,535 is 0xFFFF, not 0x10000.
• #### I have another theory entirely. (Score:5, Informative)

on Tuesday September 25, 2007 @01:14AM (#20739457) Homepage Journal

...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
• #### I've got a theory... (Score:4, Funny)

on Tuesday September 25, 2007 @01:28AM (#20739543) Homepage
It must be bunnies!
• #### And yet it works FINE when you GRAPH it... (Score:5, Informative)

<UptownGuyEmail@gmail.com> on Tuesday September 25, 2007 @06:31AM (#20741027)
....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...
• #### Re:In OOXML? (Score:5, Funny)

<d3slash@mennucc1.debian.net> on Tuesday September 25, 2007 @01:56AM (#20739673) Homepage Journal

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? (Score:5, Funny)

on Tuesday September 25, 2007 @03:24AM (#20740197)
I say we petition Microsoft to include a multiplyLikeExcel2007 element in the next version of OOXML.
• #### Re:In OOXML? (Score:5, Funny)

by Anonymous Coward on Tuesday September 25, 2007 @05:00AM (#20740595)
The OOXML specs are already 65498 pages long. If Microsoft can give "multiplyLikeExcel2007" a 37 page treatment in the OOXML specs, the total page count for OOXML will reach an amazing 100000 pages (therefore 166 times better than ODF). Sounds like a winning plan to me!
• #### Pentiums (Score:5, Funny)

on Monday September 24, 2007 @09:40PM (#20737853) Journal
What happens if you use this on an older Intel chip? Do the issues cancel out?
• #### Re:Pentiums (Score:5, Funny)

on Monday September 24, 2007 @09:49PM (#20737927)
Perhaps. I tried to test it out, but the hard drive with the swapfile caught fire before Excel finished loading.
• #### Re:Pentiums (Score:5, Funny)

on Monday September 24, 2007 @09:49PM (#20737933)
Not quite. The cell in that case just reads 99,999.999998263, when it should be 65,534.99994721.
• #### Is anyone using Excel 2007? (Score:5, Insightful)

<qg@biodome.org> on Monday September 24, 2007 @09:41PM (#20737861) Homepage Journal
I bought a Dell this year, it came with Office 2003.

• #### Yes. (Score:4, Informative)

on Monday September 24, 2007 @09:43PM (#20737875) Homepage
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.
• #### The renewal form, hmmm? (Score:5, Funny)

on Monday September 24, 2007 @11:29PM (#20738749) Journal
And you didn't balk at the 34% increase in rent?
• #### Re:Is anyone using Excel 2007? (Score:4, Interesting)

on Tuesday September 25, 2007 @01:28AM (#20739541)
I'm using Excel '97 and Word '97 on my XP Pro 2.6GHz dual core and they work fine. Excel loads in an instant, most numbers seem to multiply ok (haven't tested them all but I'm sure the service packs have fixed most major errors by now). In 10 years I haven't seen a single new feature that makes a compelling reason to upgrade.

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)

on Tuesday September 25, 2007 @06:23AM (#20740983)
"most numbers seem to multiply ok"

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.
• #### Who are you going to call? (Score:5, Funny)

on Monday September 24, 2007 @09:43PM (#20737873)
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? (Score:5, Funny)

on Monday September 24, 2007 @09:49PM (#20737929)

Who are you going to call?

Ghostbusters?
• #### Re:Who are you going to call? (Score:5, Funny)

on Monday September 24, 2007 @10:24PM (#20738241) Homepage
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? (Score:5, Funny)

on Monday September 24, 2007 @11:53PM (#20738935)
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? (Score:5, Funny)

<halcyon1234@hotmail.com> on Monday September 24, 2007 @11:56PM (#20738957) Journal
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}

• #### Re:Who are you going to call? (Score:4, Funny)

on Tuesday September 25, 2007 @12:20AM (#20739129) Homepage Journal
1 2 3 4 5 5 6 7 8 9

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

on Monday September 24, 2007 @09:49PM (#20737925) Homepage
• #### It only gets worse. (Score:5, Funny)

on Monday September 24, 2007 @09:50PM (#20737943)
Try =6*9.
• #### Re:It only gets worse. (Score:5, Funny)

on Monday September 24, 2007 @09:53PM (#20737987)

Try =6*9.
6 * 9 = 42
• #### Re:It only gets worse. (Score:5, Funny)

on Monday September 24, 2007 @10:27PM (#20738291) Homepage
So this means that Microsoft is the fundamental flaw in the universe? ...Okay, I'm convinced.
• #### Vista Sales (Score:4, Funny)

on Monday September 24, 2007 @09:50PM (#20737945)
It's used in the algorithm that MS uses to report Vista sales.
• #### Microsoft Excel is the Worst Part of Office 2007 (Score:3, Interesting)

on Monday September 24, 2007 @09:58PM (#20738017) Homepage

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.

• #### Check a C multiply on that machine... (Score:3, Insightful)

<todd.bandrowsky@gm a i l.com> on Monday September 24, 2007 @10:05PM (#20738079) Homepage Journal
Just for the heck of it. There's always an offbeat chance that the multiply bug is in the CPU, not Excel....
• #### Re:Check a C multiply on that machine... (Score:5, Insightful)

<megazzt@gmai[ ]om ['l.c' in gap]> on Monday September 24, 2007 @11:01PM (#20738527) Homepage
A number of people have verified the bug, and it's quite improbable they ALL have the same model of CPU.
• #### I can see the OOXML tag now... (Score:5, Funny)

<steven@nosPam.crinklink.com> on Monday September 24, 2007 @10:05PM (#20738081)
<MultiplyLikeExcel2007>
=850*77.1
</MultiplyLikeExcel2007>
• #### Re:I can see the OOXML tag now... (Score:5, Insightful)

<ian.ameline@g m a il.com> on Monday September 24, 2007 @10:23PM (#20738235) Homepage Journal
That profoundly unfunny post (but insightful) will probably get modded as funny.

Lets wait and see :-)

• #### Re:I can see the OOXML tag now... (Score:5, Funny)

by Anonymous Coward on Monday September 24, 2007 @10:51PM (#20738461)
That profoundly uninsightful post (but funny) will probably get modded as insightful.

Lets wait and see :-)
• #### Re:I can see the OOXML tag now... (Score:5, Funny)

on Tuesday September 25, 2007 @02:25AM (#20739855) Homepage
• #### Re:I can see the OOXML tag now... (Score:4, Funny)

on Tuesday September 25, 2007 @12:49AM (#20739299) Homepage
And your rather funny (but not insightful) post will get modded as... bah, nevermind. :P
• #### Unnecessary abstraction (Score:5, Funny)

on Monday September 24, 2007 @10:06PM (#20738089)
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).
• #### some limited testing (Score:5, Interesting)

on Monday September 24, 2007 @10:10PM (#20738133)
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

• #### Retro (Score:4, Funny)

on Monday September 24, 2007 @10:16PM (#20738183) Homepage
I always said that Microsoft would never successfully migrate from 16 to 32 bits...
• #### Kinda Ironic (Score:3, Insightful)

on Monday September 24, 2007 @10:20PM (#20738205) Homepage
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.
• #### Imperiled by binary decimals? (Score:5, Interesting)

on Monday September 24, 2007 @10:26PM (#20738277)
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?! (Score:5, Insightful)

on Monday September 24, 2007 @10:32PM (#20738331) Homepage Journal
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 (Score:5, Funny)

<shawn-ds@willden.org> on Monday September 24, 2007 @10:33PM (#20738343) Homepage Journal

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)

<jokirby AT vmth DOT ucdavis DOT edu> on Monday September 24, 2007 @10:36PM (#20738367)
Now how can I work this into my salary.......
• #### Not the only devastating bug (Score:4, Interesting)

on Monday September 24, 2007 @11:11PM (#20738599)
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.
• #### Step 2 Found! (Score:5, Funny)

on Tuesday September 25, 2007 @12:23AM (#20739151) Homepage Journal
Well, I'm off to deposit \$655.35 less my current balance into my bank account.
• #### MS Multiplan on Commodore 64 (Score:4, Interesting)

on Tuesday September 25, 2007 @12:53AM (#20739323)
Just for fun I cranked up Microsoft Multiplan 1.06 from 1983 for the Commmodore 64 (using the Vice emulator [viceteam.org], 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.
• #### basic math errors in MS libs? Nothing new.. (Score:5, Interesting)

on Tuesday September 25, 2007 @02:41AM (#20739955)

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!

#### Related LinksTop of the: day, week, month.

The tree of research must from time to time be refreshed with the blood of bean counters. -- Alan Kay

Working...