UPDATED: Quick! We need a calculator! - Excel 2007 bug

UPDATED: Quick! We need a calculator! - Excel 2007 bug

Summary: Excel 2007 fails remedial math ...

SHARE:
TOPICS: Software, Microsoft
23

I don't usually cover random software bugs on Hardware 2.0 but since a few of you sent me links to this story I thought I'd post it for others.

[Updated: Sept 26, 2007 @ 4.30 am - The Excel 2007 team have been busy investigating and coming up with a fix for the Excel 2007 bug I discussed yesterday.  Now the team have released more information about the issue:

The Problem This issue was introduced when we were making changes to the Excel calculation logic in the Office 2007 time frame.  Specifically, Excel incorrectly displays the result of a calculation in 12 very specific cases (outlined below).  The key here is that the issue is actually not in the calculation itself (the result of the calculation stored in Excel’s memory is correct), but only in the result that is shown in the sheet.  Said another way, =850*77.1 will display an incorrect value, but if you then multiply the result by 2, you will get the correct answer (i.e. if A1 contains “=850*77.1”, and A2 contains “=A1*2”, A2 will return the correct answer of 131,070).

So what, specifically, are the values that cause this display problem?  Of the 9.214*10^18 different floating point numbers (floating point on wikipedia) that Excel 2007 can store, there are 6 floating point numbers (using binary representation) between 65534.99999999995 and 65535, and 6 between 65535.99999999995 and 65536 that cause this problem.  You can’t actually enter these numbers into Excel directly (since Excel will round to 15 digits on entry), but any calculation returning one of those results will display this issue if the results of the calculation are displayed in a cell.  All other calculation results are not affected.

The Solution We take calculation in Excel very seriously and we do everything we can in order to ensure that calculation is correct for all cases.  We’ve come up with a fix for this issue and are in the final phases of a broad test pass in order to ensure that the fix works and doesn’t introduce any additional issues - especially any other calculation issues.  This fix then needs to make its way through our official build lab and onto a download site - which we expect to happen very soon.  We’ll add another post once that’s taken place with a link to the download.

Interesting bug indeed.  Let's hope a fix is released soon.]

[Updated: Sept 25, 2007 @ 12.30 am - I just got this from a Microsoft spokesperson:

Microsoft recently learned of the flaw in Microsoft Office Excel 2007 that affects some calculations where the product should equal 65,535. We are currently in the process of developing and testing a fix for the flaw. Microsoft places a high priority on quickly responding to customer feedback and we are committed to finding ways to provide a better software experience.

I'll keep you updated.]

It appears that there is a serious bug in Excel 2007 affecting certain multiplications operations where the product should equal 65,535 but where it is instead displayed as 100,000.

Here's the bug in action:

excel2007bug_sm.jpg

Hey, this isn't on, I can make my own mistakes, I don't need to buy Office 2007 to do that. 

If you use Excel 2007 to do heavy number crunching I think it might be time to check those spreadsheets for this bug.  My advice is to go back to a previous version, OOo Calc or dust off that calculator.

Bonus points for anyone who can come up with a quick (funny) fix for this.  Here's mine:

Clippy: "It looks like you are trying to multiply two numbers.  Would you like to go get a calculator?"                                         Yes | No

Topics: Software, Microsoft

Kick off your day with ZDNet's daily email newsletter. It's the freshest tech news and opinion, served hot. Get it.

Talkback

23 comments
Log in or register to join the discussion
  • Excel has always had problems...

    ... and many statiticians have been warning about it for several years. For example, Excel 97 http://www.agresearch.co.nz/Science/Statistics/exceluse1.htm

    and Excel 2003

    http://portal.acm.org/citation.cfm?id=1162012

    I have to admit, this bug seems worse than usual. Hey-ho... :-)
    bportlock
    • If only ...

      ... I could leverage it in the payroll system somehow! :-)
      Adrian Kingsley-Hughes
      • I use it daily

        I'm an accountant, and use this software on a daily basis.

        Obviously, in my profession, you NEVER assume that the calculations are totally correct. If the bottom line isn't right, you break out old-trusty and start adding manually.

        I have never had it do this to me. Even on complicated tax return schedules with lots of multiplication and percentages.

        What are the specific circumstances regarding the bug? I ask because it's pretty plain from my perspective that it doesn't happen all the time. There may be a quick fix if you know when it acts up.
        laura.b
        • No idea ...

          ... I've approached Microsoft for comment ... I'll keep you in the loop.
          Adrian Kingsley-Hughes
          • Update

            If there is a decimal remainder, it displays the correct amount. I ran the numbers, and got the 100,000 on the first 13 lines, but every line below that I got 65,535.03.

            I don't know how you got the last 5 lines to read 100,000. I couldn't get it to. I expanded the decimals in column B and still couldn't get it to.

            There is something funky about 65535. In addition, it's not a display error. It's not calculating properly and just displaying the wrong number. If you carry the calculations out, such as adding 1 in column C, you don't get 65536, you get 100,001. Also, if you modify the formulas in column B to, for example, add 1, (formula reads =A1*B1+1), it doesn't correct the initial miscalculation, so it's not the formula either. It's definatly a problem with that specific product. It doesn't work for any function other than multiplication, and it works regardless of how many numbers you use to get to the product (I tried 7 columns, still got 100,000).

            Didn't learn anything here, just thought it may help if you ever get someone from MS.
            laura.b
  • We do a lot of data reduction

    Monte Carlo simulations do a nice job of generating mountains of data requiring pretty basic statistical reduction, and it turns out that importing the raw results into a spreadsheet for regression and analysis is adequate. (Yes, we could use MatLab -- but the license availability is limited.)

    Since the data starts off on the Unix side of things, I've always used OO.o for the reduction. This year another engineer joined the party and prefers MSExcel. What[b]EVER[/b], right?

    Maybe not. She never was able to get the MSExcel version to work. Sometimes it just barfed over simple functions, sometimes it accepted the data but got ludicrous answers, etc. After a week wasted trying, she's now doing OO.o

    The thought of MSExcel running large parts of the economy isn't comforting.
    Yagotta B. Kidding
    • Yeah, but

      So your shiny new Excel spreadsheet cannot multiply correctly, but isn't the ribbon pretty? ;)
      bmgoodman
    • Apparently, Excel does a lot of 'data reduction' too. NT

      NT.
      heres_johnny
    • Aren't you special!

      Paraphrase: "My job is so esoteric that Excel isn't good enough and I look down on the rest of humanity when they use Excel!"

      BTW - I would've hated to been that young engineer reporting to you. Instead of applauding her efforts, u clearly needed an ego stroke. Send me her resume because she is clearly of more value outside ur organization than within. She is obviously too young to know it which is why she hasn't quit yet.
      Cornhead
      • Praise bad behavior.

        @Cornhead Applauding her efforts doing *WHAT*?
        Stubbornly insisting on using a cancerous, anticompetitive product instead of the better alternative already in use?
        Praiseworthy, indeed.
        joaovrsa2
      • Esoteric multiplications.

        @Cornhead Esoteric jobs indeed.
        They even multiply numbers, what were they expecting?!
        joaovrsa2
  • VBA fix - only alpha

    Insert in WorkbookOpen() event handler ...

    For each Cell in This.Workbook

    If Cell.Value = 100,000 Then

    WorkaroundNumber = BigExcuseCount*Rand()+1
    Select Text(WorkaroundNumber)

    1: Msgbox("Asking a binary $64,000 question is not permitted in this version of EXCEL. Please consult your EULA.")

    2: Playsound("%MEDIAROOT%Danger Will Robinson.wmv", "Loud")

    3: Msgbox("You appear to be typing a spreadsheet - would you like to insert the formula =2^16-1?")

    4: Msgbox("All your 65535's are belong to us! The 100K crack team.")

    5: Msgbox("ERROR - RESERVED VALUE: please select another positive whole number from the drop down list.")

    6: Msgbox("Are you feeling lucky today, punk?")

    7: Msgbox("So it's a bug, we've always had bugs. Customers have gotten used to bugs. One more won't do any harm. In fact there are lots of bugs. It saves us money on the testing budget to have end users find bugs. Everyone knows to wait for SP1 before using software for real, chump.")

    8: Msgbox("MSFT Press Release: OFFICE 2007 maintenance has been suspended. Work will resume once Vista Ultimate Extras have been delivered. M$ would like to apologise for the late delivery of toys. Unfortunatley company culture classes this as a dismissable offence. Look what happended to Jobs when he tried rebates on iPhones!")

    9: Msgbox("The number 65535 is the subject of a international standards review. Until the results of the review are known this will be shown as 100,000. Don't worry, your installation was patched without your knowledge last Tuesday ... in case you aren't keeping up with things properly ... or your favourite flag is the skull and crossbones.")

    10: Msgbox("The number 65535 is too small for this 64-bit operating system.Please use larger number's in future.")

    11. Msgbox("Pick a number, any number.")

    12: Msgbox("The number 65535 is not supported by this product release. Please upgrade to EXCEL 2010 [due Fall 2012].")

    13: Navigate("http://www.docs.google.com")
    Navigate("http://www.apple.com/iwork/numbers/.")

    14: Msgbox("The number 65535 is covered by Microsoft patent PAT34519579019984. Please remove this from all your spreadsheets immediately.")

    15: Msgbox("Hey, given the choice of updating the user interface or ... correcting longstanding errors in rounding, statistical functions and other calculation stuff: it was a no-brainer, right?")

    16: Msgbox("I know we said you could have 2^14 columns and 2^20 rows ... but show me where we said you could use numbers like 2^16-1.")

    17. Msgbox("MSFT spokesman: look, there are infinitely many integers. We got one wrong. You M$ haters just want perfection.")

    Else

    Msgbox("Make my day - try another formula!")

    End Select

    End
    jacksonjohn
    • OK ...

      ... you win ... that's funny! ;-)
      Adrian Kingsley-Hughes
      • Brevity is the soul of wit

        Yours is better.
        frgough
    • LOL

      Man, you had me practically rolling on the floor with laughter.... Thanks! :-)

      Greetz, Pjotr.
      pjotr123
    • ...this post....

      ...ruined my wireless keyboard via spit-take....
      DCMann
    • Too freakin' funny....

      You should incorporate that into a standup routine. Thanks for the super chuckle! :-)

      MGP
      MGP2
  • Something still isn't right

    They said that the calculation is correct and it is displaying improperly, but a very simple calculation proves that to be false.

    If you type into Excel 2007 the information on Adrian's screenshot, then in column C add, for example, 1 to the previous column, you get 100,001, not 65,536. In addition, if you add the aforementioned 1 to the actual formula in column C, you still get the wrong answer (or if you multiply, divide, or subtract a number from it).

    I don't see where they are getting that the calculation is correct. In my copy, it screws it up as if it were a calculation error, not a display error. I can't replicate what they say should happen instead.

    In any case, it's a pretty specific error. Glad it's not widespread.

    I use this program daily (and heavily - I am an accountant but have also had classes in Excel and have used nearly every feature, but have not used all the formulas, perhaps only about 60 or 70% of them, some much more than others), I beta tested it before it was released, this is the only calculation bug I've come across so far(which is good in a program designed for calculations, I suppose).

    This is a good lesson though, not just from an Excel standpoint.

    You should always have enough knowledge of what you are doing to notice if something isn't right. Accountants are really bad for relying on the calculator (as in adding 10 and 15 to make sure it still equals 25), but it is still essential to realize that technology of all kinds screw up and if you are taking a piece of equipment's word as gospel without ever performing a simple crossfoot to make sure that everything is functioning properly at least on a base level, then you are destined to make a serious error when something does go funny.

    Bottom line - learn to do simple math in your head well enough to know that when Excel (or for that matter a calculator) tells you that 12,850*5.1 is 100,000, you know well enough that that doesn't sound right and double check.
    laura.b
  • It does calculate correctly, just displays wrong

    If you look carefully at the article, there are two areas that cause problems, the 6 floating point numbers before 65535 and the 6 floating point numbers before 65536. Taking 5.1 time 12850 displays 100,000, but the calculation is correct. Adding 1 to this value results in one of the 6 floating point numbers before 65536. If you add 2 to this result you will get the correct answer displayed.
    cautionld
    • If it calculates correctly, then it continues to display wrong regardless

      All you have to do to prove or disprove this is what I've already done.

      Force the error (simply copying the numbers from Adrian's screenshot will do the trick) and then incorporate the incorrectly displaying cell in another formula. If the calculation is correct, it should consider this in the new formula. It doesn't. It treats the cell as if it reads 100,001. In addition, if you expand the formula in the incorrectly displaying cell itself, it still uses 100,001 instead of the correct answer.

      So, even if it is calculating correctly, it is using the displayed amount for all future calculations involving that particular cell.
      laura.b