Excel 2007 bug shows wrong answers to simple multiplications

This is a follow-up post to my earlier one about a bug in the way Excel 2007 displays the results of certain calculations. Read that one first of you have not already done so.

A few people in the comments thread in the Excel team blog post about the bug seem to have some misconceptions about the seriousness of the problem. Some have asked how often it is likely to come up, implying that they think it is vanishingly unlikely. This seems to be particularly those who have misunderstood that the example of 850*77.1 is only one simple example which is easy to remember and to type, but there are several more simple ones as well as thousands of other combinations which lead to the buggy result (due to floating point rounding errors in the calculation hitting a result which is sufficiently close to 65,535 to cause the false display of 100,000). Nine examples are shown in the screenshot below, and in a table you can easily copy and paste to try this for yourself.

Nine examples of simple calculations which Excel can’t work out*

* yes, I know the underlying value in the answer is as accurate as any previous version within the limits of the floating point calculation method and it just displays wrong, but in the real world that’s just as bad.

Excel bug example number pairs

425 154.2 =A1*B1
850 77.1 =A2*B2
1700 38.55 =A3*B3
6375 10.28 =A4*B4
6425 10.2 =A5*B5
12750 5.14 =A6*B6
12850 5.1 =A7*B7
25500 2.57 =A8*B8
25700 2.55 =A9*B9

To use the examples in the table above, select them, copy (CTRL-C), open Excel.
With cell A1 selected go to Paste Special (use the dropdown arrow on the Paste button or do ALT, E, S). Choose Text and click OK.

If you divide 65,535 by integers starting from 1, then multiply the result by the same integer, you will quickly find many instances of the display bug. For example, A7=65535/7; B7=A7*7 (I actually used the ROW() function to generate these so  I could copy and paste blocks quickly). For the integers 1 to 256, you will get 30 examples, the first being 65536/13=5041.15384615385, and 13*5041.15384615385 = 100,000.

How many more examples are there?

In the first 524,280 results, there are 80,242 bugs, which is approximately 15.3%. I do not consider this many to be rare, although I accept that these are all effectively getting one of a very few values close to 65,535 which have the bug – the number of buggy values is small, but the combinations of number pairs which lead to them is quite large. The nine examples given above all have exactly the same product – 65,534.999999999993 (you can see this as the value stored if you open the XML file for that worksheet)

There are several places where as many as seven consecutive integers lead to the bug; the first sequence this long starts at 100,334, the first set of six begins at 31,745. The longest gap I found in the first half million or so cases is 1,370 between the bugs at 262,589 and 263,959.

There are some interesting patterns to the integers which cause these. For example, 13*2^n, 49*2^n, 81*2^n and many other similar chains produce the bug.

Doing a similar exercise on numbers with exactly one decimal place (1.1, 1.2 … 52429.7 but not 1.0, 2.0 etc.) there are 64,325 bug pairs, about 13.64% of results. There is a sequence of 8 numbers from 28,059.0 to 28,059.7 which all produce the problem. A few simple example pairs include 20.4 * 3,212.5, 40.8 * 1,606.25 and 212.5 * 308.4. It starts to become clearer that there are quite a lot of calculations which could occur in normal business models and give rise to the error.

Can I test my files to see if this is affecting my results?

Simon Murphy has published some code which you can use to test your files for occurrences of the bug. He also discusses how important this bug is overall, in terms of actual impact to real users – noting that the number of people who have moved to excel 2007 so far seems to be relatively small. Not zero, but the problem would have had loads more impact if it had been undiscovered for another three to six months. I have a client who are rolling out as I write, migrating several thousand users from Office 2000 to Office 2007. They generate huge management accounting spreadsheets which spit out pretty reports on the one hand, but on the other the data they generate is piped via CSV into their ERP system. So one person would have a chart or summary table with correct figures, while the back-end systems would be out by a significant margin of error. This could take a long time before anyone could have isolated the error and it’s source.

What about cases where this becomes an actual error in the value?

As previously discussed, there are several ways in which the Excel 2007 display error can become fixed as an incorrect value (of 100,000 or 100,001):

  • using the ROUND function (but not ROUNDDOWN nor ROUNDUP)
  • using “precision as displayed”
  • exporting to CSV
  • Paste Special > Values
  • using the TEXT function to convert to a string

In addition, the MOD function seems to just break down altogether – it does not use the 100,000 but returns totally bogus results.
For example, MOD(850 * 77.1, n) gives -7.27596E-12 for integer values of n including 1 and 5 (which should both return 0). even MOD(65534.999999999999,1) gives a result of 0.999999999999 quite happily. Something quite odd going on with this, as MOD should never return a negative value, mathematically speaking.

So what functions work safely?

Fun With Min and Max in Excel 2007 In addition to the obvious mundane arithmetical calculations, most functions seem to work just fine. The MAX and MIN functions and their cousins LARGE and SMALL work as normal, as do conditional formats including data bars and colour scales. The screenshot on the right shows how odd this may appear.

The FLOOR and INT functions seem to use the correct underlying values and not propagate the error at all. ABS does not change the value, by design (just lose the sign) so it repeats the 100,000 display bug, but still does not lock this in. ABS(-850*77.1) shows 100,000. Adding 2 to that result gives 65,537.

ROUNDDOWN and ROUNDUP both seem to happily return 65,535 as their result. Of course, as Erich Neuwirth pointed out in the Google Groups thread on the subject of the bug, in a way this should not be the case. We know that the result of 850*77.1 is not actually 65,535 but a tiny fraction under that due to floating point inaccuracies.
So that would mean that ROUNDDOWN (850 * 77.1, 0) should truncate the entire part of the number after the decimal point to give a result of 65,534. Now that would be seen as a bug, since we know that in reality 850*77.1 is actually 65,535 exactly. So there must be something in the algorithm for ROUNDDOWN which assumes if a number is close enough to a whole number, round it up to that value, treat it as an integer input and ROUND it down by precisely nothing. The same applies for ROUNDDOWN used for any number of decimal places up to 30 – it spits out 65,535 as if the inaccurate interim value never existed.

I am happy to continue saying “ROUNDDOWN is correct” in this context, since it is closer to our expectations than if every floating point error introduced were to cause whole numbers to be lost. A dollar here, a barrel of oil there, a square mile of land for someone else. This would not be acceptable, but I can accept that the number stored underneath is inaccurate to some bazillionths of a penny. Same goes for the other functions such as INT and FLOOR – they are actually giving incorrect results, but they are giving the result they should in a normal decimal universe.

Of course, if you are a nuclear physicist you don’t want Excel making these assumptions on your behalf for very small numbers, as they would be considered wrong in that context.

Don’t forget, these floating point errors have been around for a very long time. It is only a bug like this that makes people take notice what is going on “under the hood” and try to grasp the idea that their entire accounting career has been a lie, and every report they ever wrote was probably some kind of approximation. People generally fall into three camps on this:

  1. Blissfully unaware that their figures are inaccurate, and totally unaffected by the fact
  2. Aware that these errors exist in the n’th decimal place as a matter of fact, but happy to accept that this is not fatal
  3. Fully aware of the floating point problem, and careful to check that functions handle very-near-integers sensibly to give a result which would be expected in the real world if calculated in decimal.

Which type are you?

About ukcrmguru
I'm an MVP for Dynamics CRM, consultant, Microsoft Certified Trainer and self-confessed geek. I also lead the UK CRM User group when I'm not too busy with all that.

8 Responses to Excel 2007 bug shows wrong answers to simple multiplications

  1. Pingback: Excel 2007 calculation bug fix released after two weeks « Getting IT Right

  2. Tom Nash says:

    A variation of this error is the display of the following division:
    517,924,440,926,512/ 3 displays 172,641,480,308,837

    Multiplying 172,641,480,308,837 *3 gives a value of
    517,924,440,926,511.

    • Syed says:

      I also got same problem on Excel 2010 but this with simple multiplication rounded to 2 decimal places

      Qty Price Secmast Spread New price Rounding Round price Diff Mark param
      100.00 16600.00 165.07 1.03 170.02 U-Next $ 0.98 171.00 5.00 500.21
      100.00 2900.00 28.01 1.02 28.57 U-Next $ 0.43 29.00 0.00 0.02
      100.00 1200.00 11.69 0.00 0.00 -12.00 -1200.00

      The param cell here has a formula to calculate Qty*diff i.e 100.00*5.00.

      Any solution for this ?????

      • Adam Vero says:

        In your example, if the column for “diff” is only showing 2 decimal places but storing more, then this could easily be correct, ie 5.0021 * 100 = 500.21

        Have you tried changing the format for the diff column to show more decimals?

        Is the formula for diff = round price – price?
        Is round price actually rounded using a ROUND[DOWN][UP] formula? I guess the “rounding” of “U-Next $” means up to the next whole dollar ie to use ROUNDUP(New Price,0)?
        Is price stored to two decimal places as it is displayed?

        I would be very surprised if this was actually a bug in the calculation engine, and it does not look likely to be a floating point inaccuracy either, at face value.

  3. Adam Vero says:

    Sorry Tom, but that’s not a bug of the same class at all. That is simply a number which cannot be properly represented in floating point notation within Excel as it is too long. It loses the least significant part of the number when converting for display, as Excel can only handle up to 15 significant digits, which is well documented.
    There are many examples of this type, such as the simple 543,215,432,154,321/2 which yields 271,607,716,077,160 with no .5 on the end.
    Multiplying the result by the original denominator actually gets the correct number back, Excel is doing some clever work behind the scenes to keep reality together.
    Even if you copy and paste special > values then multiply that back up, you get the right answer. Some functions like ROUNDUP do choke on this and give the wrong result, though, since it cannot see any decimal part and assumes this really is an integer. As I said, you are working near the upper limits what Excel can handle here.
    I think these kind of ‘errors’ are of such small magnitude (1 in hundreds of millions of millions) that people are more ready to accept them than the lowly, domestic sized numbers involved in the actual display bug around 65,535.

  4. paul says:

    posted on May 18, 2011.

    I am getting the wrong answer averaging three numbers when using Excel 2007 (12.0.6550.5004) sp2 mso 12.0.6554.5001) part of microsoft office home and student 2007

    when i simply try to take the average of three numbers in cells C5, C6, C7.

    2.41
    2.47
    2.34

    my equation in cell c8 is (C5 + C6 + C7) / 3
    The answer from excel = 2.40666667

    On my calculator i type (2.41+2.47+2.34)/3
    and the calculator gives 2.41666667

    I tried using Round (cell,2) and any other function I could find… but nothing works.

    This should round to 2 decimal places as 2.42 not 2.41 which is what excel gives me.

    • paul says:

      never mind. I replaced the batteries in my TI 89 calculator and now it agrees with excel.

      • Adam Vero says:

        Glad to see you fixed it.
        A cheaper alternative to new batteries might have been a good old pencil and paper, of course!

%d bloggers like this: