29 thoughts on “2007 Calculation Bug”

  1. It’ll be interesting to see how long it takes Microsoft to acknowledge this publicly, and how long it takes them to fix it. This seems worse than the Pentium floating point bug (which was hardware, so Intel’s fault, not Microsoft’s).

  3. I guess this should be in next month updates, I cant see them waiting for a company to actually encounter the bug and sue them back because of mistakes in a calculation software

  4. You might want to read that EULA that you agreed to (knowingly or not). If I understand the legalese, if you’re successful in your lawsuit the maximum amount you could collect is equal to what you paid for the software.

    LIMITATION ON AND EXCLUSION OF DAMAGES. YOU CAN RECOVER FROM MICROSOFT AND ITS SUPPLIERS ONLY DIRECT DAMAGES UP TO THE AMOUNT YOU PAID FOR THE SOFTWARE. YOU CANNOT RECOVER ANY OTHER DAMAGES, INCLUDING CONSEQUENTIAL, LOST PROFITS, SPECIAL, INDIRECT OR INCIDENTAL DAMAGES.

    And that applies even if Microsoft knew or should have known about the possibility of the damages.

  5. Sure, the language is in the EULA, and I don’t understand legalese, but isn’t there a difference between MS not knowing about this and the gross negligence that they knew it existed and released the product anyway? Of course proving it and having the funds to go after MS are another story.

    One could argue that you have to be responsible for yourself and not blindly rely on what the spreadsheet, but if I have to recalculate everything myself to make sure the spreadsheet works, then why use the spreadsheet?

    Stupid program.

  7. Dick my comments linking back to the test code I have uploaded keep disappearing.
    here it is with no link to see if that makes it:

    I’ve put some test code on sos to help people work out if their wbs are affected. Only Excel 2007 is potentially affected, and only certain 65,535 and 65,536 combinations go wrong. No idea if there are other related issues.

    If anyone spots problems or improvements leave a comment ta
    cheers
    Simon

  11. “I hear they’re going to deprecate arithmetic operations in SP1.”

    I don’t think they need to go that far.

    If you format the 100,000 results as a date it gives the correct date for day number 65535, so we just have to learn to work in dates instead of decimals.

  15. Did anybody notice that when using the formula XIRR over a certain range where the result would be negative Excel returns 0.00%. This happens frequently, but not every time. This happens in various versions of Excel, they never seem to have corrected this. Other spreadsheets, like Open Office happily calculates the correct negative results. As I need to use this formula quite often, the lack of result is annoying. Any idea how this can be overcome in Excel?

  17. Just to leave some detail in this thread:

    http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/2bcad1a1a4861879/6850d1e3d2385246?hl=en&

    Erich Neuwirth says:
    Newsgroups: microsoft.public.excel
    From: Erich Neuwirth
    Date: Wed, 26 Sep 2007 21:37:50 +0200
    Local: Wed, Sep 26 2007 8:37 pm
    Subject: Re: Bug in Excel 2007
    Reply | Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author
    Since we have seen a lot of theories,
    here is some condensed description.

    65535-2^-35, 65535-2^-36, 65535-2^-37,
    65536-2^-35, 65536-2^-36, 655356-2^-37

    are numbers exhibiting this problematic behavior.
    So if a floating point calculation has such a result,
    (which is not visible because Excel does not display enough decimal
    digits) then you get 10000 or 10001 displayed.

    What also is interesting that in these cases
    MOD(…,1) gives -2^(-35)… displayed in decimal as
    -0.00000000002910383046 (with 20 decimal digits)
    MOD(…,1) never should return negative values.
    ROUND(…,1) in these cases also produce the wrong result (10000 or
    10001), the this is persistent. Any further calculation using the result
    of applying the ROUND function will be wrong.

    So if the display engine used ROUND and/or MOD to create the displayed
    value, that would be consistent with all the errors reported so far.

    MS in the Excel blog at
    http://blogs.msdn.com/excel/archive/2007/09/25/calculation-issue-upda...
    states that the error occurs with exactly 12 floating point values, 6
    near 65535 and 6 near 65536.
    Earlier in this mail I listed 6 of them, but I could not find the other
    6 yet.

  20. From Joel on software:

    “Q: Isn’t this really, really bad?

    A: IMHO, no, the chance that you would see this in real life calculations is microscopic. Better worry about getting hit by a meterorite. Microsoft, of course, will be forced to tell everyone “accuracy is extremely important to us” and I’m sure they’ll have a fix in a matter of days, and they’ll be subjected to all kinds of well-deserved ridicule, but since I don’t work there I’m free to tell you that the chance of this bug actually mattering to you as an individual is breathtakingly small.

    Q: Shouldn’t they be testing for these kinds of things?

    A: I’ll bet that most of the numeric testing done on the Excel team is done automatically with VBA code. Cells containing this value display as 100,000, but from VBA, they’re going to look like 65,535 (since the number would be passed into the Basic runtime in binary, before the display formatting.) I’m sure there’s plenty of code to test display formatting, but with a bug like this that only happens on 12 out of 18446744073709551616 possible floating point binary numbers, it’s unlikely that any set of black-box tests would cover this case.”

    So Joel is saying outright what the Microsoft people were clearly implying, that the probability of these numbers coming up is so ridiculously small that it is no problem at all.

    I’m sure they are wrong. If the probability really was 12 in 18446744073709551616 the problem would not have been discovered in a billion years of computer use, never mind within a year of release date. The point is that in the real world some numbers are much more likely to occur than others, and a binary number that is equivalent to the product of a moderately sized integer and and a moderatly sized decimal with one figure after the decimal point is hugely more likely to occur than average. Why? Because balance sheets, invoices, bills of quantities and the like are literally full of just such numbers.

    I haven’t got the information needed to work out what the actual expected occurrence of this number is, but my guess would be in the region of 1 in millions, rather than 12 in 18 billion billion.

    So how serious is it? I’d say it was about as serious as you can get and still not be picked up by Microsoft quality control.

    Certainly serious enough to check out the results of XL2007 in a different spreadsheet before sending them out of the door.

  21. Doug,

    I see your point. There’s a difference between saying 12 numbers out of 18446744073709551616 cause the error, and saying there’s a 12 in 18446744073709551616 chance.

    However, I do think the chances of hitting those 12 numbers are still painfully small. The person who found the error was using it to multiply 850 by 77.1. In the world of money, It looks like he
    was trying to figure out taxes. I can’t tell you how many times I’ve calculated my monthly base compensation by 77.7 or 77.5 or 77.1 to estimate my net after taxes. He just got lucky.

  24. Not to make light of the situation but if someone sued every time a calculation wrong… So there is an obscure bug. Lots of the spreadsheets that I see have obvious and common bugs and no one seems to care.

  25. Why were they changing something that wasn’t broke? 2003 didn’t have this bug so why are they change the code that displays the values? It makes one wonder what flaw they had that nobody knows about that they were attempting to fix. Then again, they took a well respected design for Excel and totally trashed it for this 2007 crap. I’ve only just started using Excel 2007 and have found it very cumbersome.

  26. Charles – They have revamped the whole recalc system to enable multi-threaded calcs, I suspect they spotted a possible optimisation and used it. As the error value is not reported in the .value or .value2 their tests probably missed it.
    I totally agree the new UI is a disaster, but the Excel internals I reckon are on a good footing for the future now (well when they calc right ;-)).
    Jim yes a few people have noted this bug pales into insignificance compared to the ones most users put in their spreadsheets.
    Dick – I’m wondering if my comments get binned because I have no-script enabled?
    cheers
    Simon

  27. Macroman
    That ADO/open workbook memory leak has been there for ever, real shame because it would be a very powerful technique if it worked. It must be hard to fix.
    cheers
    Simon

  28. Hey Simon,

    It IS a shame. Made me look like I didn’t know what I was doing at work. I really don’t, I’m a newbie, but they didn’t have to know that. I did work around it though, thank goodness I found that article. …cheers.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.