Excel 2007 calculation bug displays apparently wrong numbers

A bug has been found in Excel 2007 and Excel services 2007 which appears to calculate certain results incorrectly. In fact, the stored value of the result is correct, and other calculations based on that result will calculate correctly. The only error is in the display of the number, not the internal calculation. This is, of course, still a problem for anyone who is reading the values on screen or on a printout, or exporting them to other programs (see further down in this post).

According to the article on the Excel team blog about this bug:

The first example that we heard about was =77.1*850, but it became clear from our testing as well as additional reports that this was just one instance where Excel 2007 would return a value of 100,000 instead of 65,535.  The majority of these additional reports were focused on multiplication (ex. =5.1*12850; =10.2*6425; =20.4*3212.5 ), but our testing showed that this really didn’t have anything do to with multiplication – it manifested itself with many but not all calculations in Excel that should have resulted in 65,535 (=65535*1 and =16383.75*4 worked for instance).  Further testing showed a similar phenomenon with 65,536 as well.  This issue only exists in Excel 2007, not previous versions.

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, it is important to note that most calculations which result in numbers near to or equal to 65,535 and 65,536 will be absolutely fine. It is only through some very specific oddities about how floating point numbers work that you will get one of the 12 situations where this bug occurs. If it does, you will have cells that read “100,000” rather than the correct answer. Anything else in Excel which you base on those cells will be correct.

You can add to them, multiply by them, show conditional formats such as colour scales or icon sets, even draw charts with those values and Excel will correctly handle the real, underlying value and not the displayed one. Macros or external programmatic methods of retrieving the cell’s contents also return the true stored value.

When does this actually give you the wrong values (rather than just a wrong display)?

Rounding the result will change to the incorrect value permanently

Some people find it confusing that when they have a column of figures added up, the sum at the bottom may not seem to be correct.

Excel sums show up rounding errorsA typical example would be a column of prices including added sales tax (by multiplying by some decimal factor). These prices will only be shown to two decimal places in most currencies, but there will be a bit of rounding up or down to achieve this.

The sum of the underlying values may therefore differ from a sum of the displayed values, which gives the impression that there is a miscalculation somewhere, as shown in the example to the right. One way to avoid this is to use one of the ROUND functions to actually ROUND off the stored value to the nearest 1/100th (or force it to always ROUNDUP or ROUNDDOWN). The advantage of this is that if you change your mind you can easily change the formula to increase or decrease the accuracy of the underlying numbers, and my own recommendation would usually be to use this method.

If you ROUND a cell or calculation which has a buggy result you will get the wrong value as a fixed end result.
So, 2 * ROUND(850 * 77.1, 0) gives 200,000 as a result, whereas 2 * 850 * 77.1 gives the correct 131,070. This is true whether you use ROUND directly on a buggy calculation or on a cell containing the end result of one. Curiously, ROUNDUP and ROUNDDOWN are able to ignore the bug, and both ROUNDDOWN(850 * 77.1, 0) and ROUNDUP(850 * 77.1, 0) correctly result in 65,535 (since this is what the value “actually” is if you ignore floating point errors).

Telling Excel to use precision as displayed will keep the wrong value.

The alternative to the ROUND function to get over sums which appear inconsistent is to change one of your Excel options to “Set precision as displayed”. This means that if you change the number of decimal places shown in a cell (using the toolbar buttons or changing the number formatting) the actual stored value will change immediately and permanently to be rounded to that many decimal places. If you decrease the decimals to zero, then increase them again, all your numbers will be .0000… Of course, if the cell contains the result of a formula, you lose nothing, but if it contains data values they will be irrevocably changed. The point of this feature is to save you using those ROUND functions, and to make sure that what you see always appears to be calculated consistently.

It is set on a per-workbook basis, so it is perfectly possible for you to receive a workbook which already has this turned on. By the same token, if you use this feature you may actually confuse someone else who works on that same workbook, or they may permanently lose data accuracy by changing the formatting of cells if they are not aware that this is taking place.

Set precision as displayedIn order to be able to see if it is turned on or not, and change it if you wish, in Excel 2007 go to the Office button at the top left, choose Excel options, then the advance section and look for the subsection labelled “When calculating this workbook” where you will find “Set precision as displayed” (as shown on the left).

For Excel 2003 the same option is in Tools > Options > Calculation under Workbook options and called simply “precision as displayed”. NOTE: the bug described in this post does not affect Excel 2003, the way to turn on or off this feature is only included here for completeness since the state of the feature is saved with the workbook, not as part of your Excel options.

If you have this feature turned on and your workbook contains calculation which have this bug, then those results will become permanently wrong (as 100,000 or 100,001). Any other calculations based on those cells will also be incorrect. Unless you can be sure to check every single cell in a workbook, which is only practical for very small files, you really need to turn this feature off when using Excel 2007 until a fix for the bug is applied.

Exporting incorrect values

As long as you handle the numbers within Excel, they work properly and simply display incorrectly. However, if you copy cells with these incorrect values and paste out to a text editor (such as Notepad), the incorrect display value is carried, because this always passes the number as shown, rather than the internal value. If you are only showing figures rounded to two decimal places (but not actually ROUNDing them) then Notepad would show the same as the sheet, and the underlying extra accuracy is lost.

While Notepad is useful to quickly see these bugs in action, it is not usually used as an import and export routine for large volumes of multi-column data. A common method is to export to a CSV file which can then be imported into another program (often a database which cannot read and write to Excel files directly). CSV files will also carry the incorrect display figure instead of the underlying stored value. This is crucial for anyone using Excel as a number crunching machine to export results to a third party application.

I have a client who uses Excel to churn out management reports about their product sales, profits and so on. The reports that people read in this case are summaries (often from pivot tables), and the chance of them actually seeing a number on those summaries with this bug are vanishingly small. However, they use the underlying basic calculation data to export and feed into their ERP system, and here there is a risk that a number could be passed through at the wrong value via a CSV file. Chances? Small, but not sufficiently small to be ignored.

FUD is in the air

There is unfortunately a lot of sidetracking and “fear, uncertainty and doubt” (FUD) in the comments to that article about floating point accuracy in principle. No-one has ever claimed that Excel or any other calculation program using floating point arithmetic is always totally accurate to an infinite number of decimal places, this is inherently unachievable using binary notation to store fractions and display the results in decimal. At face value it seems annoying that Excel does not treat the calculation of “simple” numbers such as 4.1-4 correctly and get exactly 0.1 (rather than 0.0999999999999996 which it does get).

The reality is that in most cases no-one is looking at enough decimal places to notice or care, or even for the error to compound up into something important. On the flip side, Excel will happily handle arbitrarily small binary fractions such as 1/18,446,744,073,709,551,616 + 1/4,294,967,296 without blinking. A human trying to work on such numbers would inevitably at some point convert them to that strange human notation called decimal and lose some accuracy.

Normally, Excel handles these rounding errors gracefully, and still displays something very near to the correct value, since the error is usually down in the dozens of decimal places. The oddity on this occasion is that for some reason it makes a bad assumption as to what constitutes a reasonable value to display and gets it rather visibly wrong.

I will of course post an update once a fix is available.

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.

11 Responses to Excel 2007 calculation bug displays apparently wrong numbers

  1. RTI BobV says:

    In my copy of Excel 2007 running under Vista Business:
    I entered =850*77.1 in A1 and saw the 100000 result. In B1 I entered =+A1*1 and saw again the 100000 result. I also copied A1 and then did Paste Special | Values into C1 and again saw the 100000 result. Both of these operations are common in my business, and the results of these operations, if not caught, would be disasterous.

  2. Adam Vero says:

    BOBV
    If you do B1=A1*1 you should see 100,000 as there is no change to the value since 1 has a perfect representation in floating point arithmetic and does not cause any rounding of the result to get out of the cycle of the bug. B1 actually contains almost 65,535 but displays 100,000.
    If you use A1+1 you get 100,001 because of the second bug near 65,536. However, A1*2 or A1+2 or A1/2 all give rise to a correct result.

    Well done for spotting Paste > Values. This must be using the same process that works for pasting out to non-formatted applications such as Notepad, or exporting to CSV.

    As you say, the risk here is that on large spreadsheets this would be impossible to spot by manual checking, although it must be accepted that the actual chances of generating one of these results in reality is extremely small – but not quite small enough for us to take comfort from it.

  3. Adam Vero says:

    You are right, Joel does have a pretty good explanation of what is going on under the hood if you wish to understand more about binary representations of floating point math.
    The only thing he misses is explaining when this so-called display bug becomes an actual wrong value, which is far more dangerous to users.
    He also has me looking skywards for falling meteorites – or should that be Meteor ITs?

  4. Pingback: Excel 2007 bug shows wrong answers to simple multiplications « Getting IT Right

  5. brenda ruppert says:

    My problem is not decimals or multiplication. I import data that has a column of 16-digit numbers, and instead of the number ending in 128 for example, it changes the last 3 digits to 120. How can I fix this?

  6. Adam Vero says:

    Unfortunately, Brenda, Excel has a hard-coded limit of 15 significant digits that it will store and use for calculations. The method used for encoding and storing numbers is based on the popular IEEE 754-1985 floating point standard. You can read more about the significant digits and other limitations of Excel, most of which became significantly larger for Excel 2007 (number of rows and columns being particularly well-known, others relating to charts and pivot tables just as valuable but less talked about).
    You could import those values as text if they are only for reference. If you need to treat them as numbers for calculations then just about the easiest option you have would be to bring in the data and split it into two fields – for example a “billions” column and “the rest”. This would make calculations much harder to sort out, but might be possible depending on what you need to do. If the results needed more than 15 significant figures you would still lose some accuracy, but at least this would be a small part of the result rather than of several inputs which could compound to a greater variance in the end.

  7. Norm says:

    I had a cell, I21, that was =(D10*12)-I20 the number was a dollar value. Excel rounded it to the nearest whole dollar for no apparent reason. I tested the idea that the stored information was correct by creating a new cell I25 and making I25 =2*I21 the result was still rounded. I applied the options>advanced> turn on “Set precision as displayed” and it seemed to fix it. I25 is showing the correct value as well. Can someone explain the caveats for the rest of the workbook?

  8. Adam Vero says:

    Norm
    Can you be a bit more specific about what actual numbers you expected to appear?
    It sounds like you simply need to change the number format for those cells to show more decimal places – on the Home Ribbon in the Number group, click on the button with a blue arrow pointing left and “.0” above “.00”. Or just click the comma to get two decimals with commas between the thousands.
    If the result in I21 was (say) 12.12 dollars and this was displayed with no decimal places it would show 12. Multiplying by 2 gives 24.24 which would still show 24 if you have no decimals.
    Try I25=1000*I21 and see what shakes.
    Turn off “Set precision as diaplayed” since that means any cell where you are not showing the full stored value will actually get rounded down to the value it shows, so when you multiply back up again you get weird results. For example:
    A1=1234
    A2=A1/100 gives 12.34, but if you display this with no decimals it shows 12, even though 12.34 is stored.
    A3=A2*100 gives back 1234.
    However, if you have “Set precision as displayed” then A2 would actually contain 12, and A3 would show 1200.
    There are very few times that this option gives the results you really want, and many more where it confuses matters.

  9. Katie says:

    My problem is in simple addition. If I add a column of numbers using the SUM function, not always, but sometimes it gives the incorrect answer, even allowing for rounding. Example: if you add these figures .82, .94, .36, you should get something ending with .12. Not on my spreadsheet. It shows the answer ends with .11. A calculator, or a real throwback to pencil and paper, will prove the .11 is absolutely wrong. A penny may not seem like much, but for what I’m doing, it has to be completely correct. How do I make it work right – like any third grader could add?

    • Adam Vero says:

      Katie
      What values are actually in the cells you are adding up? If you change the number of decimal places shown in those cells, does it still show .8200000, .9400000 and .3600000? Or is the displayed value slightly different?
      If you had, for example, .818, .937 and .355 you would get a SUM of exactly 2.110, but these numbers would look like yours if shown to two decimals only.
      If they were a bit higher such as .819, .938 and .357 you would get 2.114 which would show 2.11 if only two decimals are visible.
      Hope this helps
      Adam

%d bloggers like this: