Stories
Slash Boxes
Comments
Search

News for nerds, stuff that matters

Slashdot Log In

Log In

Create Account  |  Retrieve Password

Excel 2007 Multiplication Bug

Posted by kdawson on Mon Sep 24, 2007 09:37 PM
from the be-fruitful-and-all-that dept.
Microsoft Math IT
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."
+ -
story

Related Stories

Submission: Excel 2007 Killer Bug: Can't Multiply! by tibbar66 (954328)
This discussion has been archived. No new comments can be posted.
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Excel 2007 Multiplication Bug 50 Comments More /

 Full
 Abbreviated
 Hidden
More
Loading... please wait.

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

      by cloricus (691063) 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)

      by Waffle Iron (339739) 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:5, Funny)

      by jkrise (535370) 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?

  • In OOXML? (Score:5, Funny)

    by gvc (167165) 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)

      by jorghis (1000092) 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)

        by Firehed (942385) 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)

        by Mr Z (6791) 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

    • Re:In OOXML? (Score:5, Funny)

      by mennucc1 (568756) <d3@tonelli.sns.it> 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, Insightful)

        by Anonymous Coward on Monday September 24 2007, @09:56PM (#20737997)
        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 (Score:5, Funny)

    by MyLongNickName (822545) 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?

  • Is anyone using Excel 2007? (Score:5, Insightful)

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

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

    by renegadesx (977007) 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.

  • It only gets worse. (Score:5, Funny)

    by The Earl of Sandwich (1160741) on Monday September 24 2007, @09:50PM (#20737943)
    Try =6*9.

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

    by mad.frog (525085) <steven@@@crinklink...com> on Monday September 24 2007, @10:05PM (#20738081)
    <MultiplyLikeExcel2007>
            =850*77.1
    </MultiplyLikeExcel2007>

  • Unnecessary abstraction (Score:5, Funny)

    by mikvo (587789) 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)

    by indaba (32226) on Monday September 24 2007, @10:10PM (#20738133) Homepage
    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? (Score:5, Interesting)

    by Protoslo (752870) 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)

    by WoTG (610710) 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 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! (Score:5, Funny)

    by OverflowingBitBucket (464177) 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.

    • Re:Oh no! (Score:5, Insightful)

      by timmarhy (659436) on Monday September 24 2007, @09:49PM (#20737931)
      well, it could very well lead to massive fuckups if anyone happens to make a decision based on the flawed result.

    • Re:Oh no! (Score:5, Funny)

      by renegadesx (977007) on Monday September 24 2007, @09:54PM (#20737991)
      This just in: Florida plans to do use Microsoft Excel to calculate the 08 election results.

      News at 11

    • Re:Oh no! (Score:5, Informative)

      by ozmanjusri (601766) <`moc.liamtoh' `ta' `bob_eissua'> on Monday September 24 2007, @10:54PM (#20738481) Journal
      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:Good Luck! (Score:5, Insightful)

        by AJWM (19027) on Monday September 24 2007, @10:10PM (#20738125) Homepage
        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! (Score:5, Insightful)

          by teh moges (875080) on Monday September 24 2007, @11:06PM (#20738569) Homepage
          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! (Score:5, Insightful)

        by bmo (77928) on Monday September 24 2007, @10:16PM (#20738175)
        "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! (Score:5, Insightful)

        by ross.w (87751) <rwonderley&gmail,com> on Tuesday September 25 2007, @01:47AM (#20739649) Journal
        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... (Score:5, Funny)

          by Ford Prefect (8777) on Monday September 24 2007, @10:19PM (#20738201) Homepage

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

      by eggfoolr (999317) on Monday September 24 2007, @10:56PM (#20738495)
      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.

All trademarks and copyrights on this page are owned by their respective owners. Comments are owned by the Poster. The Rest © 1997-2009 Geeknet, Inc.