Tom’s Tutorials For Excel: Why are my numbers adding up wrong?

Tom’s Tutorials For Excel: Why are my numbers adding up wrong?

Have you seen or asked yourself this kind of question before:

Why doesn’t my list of positive and negative numbers sum perfectly to zero like it should?

Setting the option for Precision as Displayed might solve the issue superficially, but getting a true mathematical result of what you want might not be possible, depending on what the actual underlying values are of the numbers you visually see. Formatting a cell has no influence on a cell’s actual value. What looks like 25% (or .25) could really be .2500000000000001 depending on the data you’re working with. Excel supports the calculation of numbers up to 15 decimal places and rounds a number after that.

What is happening in this case is not an Excel bug, but rather a computer science issue. Here is some technical background.

Excel stores numeric values as Double Precision Floating Point numbers, or “Doubles” for short. These are 8-byte variables that can store numbers accurately to approximately 15 decimal places. Regardless of formatting, the underlying value is calculated with the full 15 decimal places.

Virtually no computer can store most fractional numbers with total accuracy. Computers use the IEEE (Institute of Electrical and Electronic Engineers) standard for floating point numbers. This standard provides a way to store fractional numbers in the limited space of an 8-byte number. For many decimalized / fractionalized numbers, some approximation must be made. Excel’s internal storage of the number is not affected by the way the number is formatted in the worksheet cell.

Here are two examples of how a correct calculation can look incorrect:

Example 1

If a cell contains the formula =1/3, Excel always treats this value as 0.3333…, regardless of how many decimal places you choose to display on the worksheet. Even if you choose to format the value to appear as “0.3”, Excel still retains the complete number as the value of the cell. This can cause situations in which it may appear that Excel is making an error in calculation, when really it is not. For example, suppose you have the formula =1/3 in each of the three cells A1:A3. Formatting these cells for one decimal point would show “0.3” in each cell. Adding these three cells together with the SUM function will give the result 1.0 although visually 0.3 + 0.3 + 0.3 equals 0.9. Regardless of how you have the cells formatted for display, Excel uses the underlying value when doing calculations. In the example, you are not really adding 0.3 + 0.3 + 0.3, but rather 0.333333333333333 + 0.333333333333333 + 0.333333333333333, whose sum is almost but not quite 1.0.

Example 2

Enter the following list of numbers in A1:A6
-528.81
-92.54
621.35
-44.39
-7.77
52.16

Now add up those numbers in another cell with the formula =Sum(A1:A6).
The answer you get is 1.13687E-13 which is the same as
0.000000000000113686837721616.
The correct answer should be zero. This is a floating point rounding error — not a bug, not an incorrect result, just the way finite precision digital arithmetic works.

In Excel, this is happening in binary. Just as a computer stores integers as binary numbers, it stores fractional numbers as binary fractions.

Computers store an integer (whole number) value as (x*1 + x*2 + x*4 + x*8 + x*16 etc) where x is the state of the bit. If the bit is on, x=1. If the bit is off, x=0. In this notation, any integer can be stored exactly. For example, the number 13 is stored in binary as 1101 which indicates, reading from left to right:
[1 times 8] plus [1 times 4] plus [0 times 2] plus [1 times 1] equals 13.

Fractional numbers are stored in a similar manner. In the binary system, fractional numbers are stored as the sum of a series of fractions: (x*1/2 + x*1/4 + x*1/8 + x*1/16 and so on). Unlike integers however, not every fractional value can be stored exactly accurately. For example, it is impossible to store the number 1/10 (which is 0.1) in 8-byte or any length binary notation. A close approximation is (0*1/2 + 0*1/4 + 0*1/8 + 1*1/16 + 1*1/32 etc). Computers carry this operation to the equivalent of 15 decimal places. Even with this accuracy, many numbers are represented as an approximation of their true or analytic value. Floating point numbers can come extremely close to representing that number, but there will always be some very small error.

Again, it’s important to note that these limitations on fractional numbers are not really errors at all, nor are they bugs in the programs. These are well-known and well-documented limitations of the floating point arithmetic systems in almost every software package and hardware device, including Excel and the computers Excel is being run on.

A final example:

Create a new workbook in Excel.

In cell A1, enter 67.
In cell A2, enter 89.
In cell A3, enter the formula =A1/A2.

Right-click cell A3 and choose Format Cells.
On the Number tab, in the Category listbox, select Number.
In the Decimal Places spinner, enter the maximum (30), and click OK.
Widen column A as much as necessary to see the number.

Note that cell A3 shows:
0.752808988764045000000000000000

The correct answer would continue infinitely. For example, here are just the first 30 correct significant digits:
0.752808988764044943820224719101

In addition to being unable to return numbers with more than 15 significant digits, Excel is unable to accept operands with more than 15 significant digits. For example:

In cell A4, enter 123456789123456789.
Right-click cell A4 and choose Format Cells.
On the Number tab, in the Category listbox, select Number.
(Optional) In the Decimal Places spinner, enter 0, and click OK.
Widen column A as much as necessary to see the number.

Note that cell A4 shows:
123456789123456000

Excel truncated the number to 123456789123456000 — it didn’t even round it correctly to 123456789123457000.

Where this especially comes into play is that some calculations have literally infinite decimal place values, meaning that the calculation for those numbers would never be 100% accurate arithmetically no matter what calculation medium (computer or otherwise) is used.

The bottom line is that the calculations produced by Excel for your cells are as good as Excel will be able to produce, and VBA (if you were to use that) would be able to programmatically calculate, within the computer environment of digital arithmetic.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
2 comments on “Tom’s Tutorials For Excel: Why are my numbers adding up wrong?
  1. Gadi Bizinyan says:

    Very good article and explanations within Tom. The example at the beginning with 0.25 is not suitable though. If there are decimal numbers Excel will always get right, it’s 0.5 and 0.25, both should not yield any unwanted noise with the corresponding digits behind the decimal point.

    You have already explained how 1/3 will cause an issue to Excel and also 0.1 but I got to your article by searching for 0.2, which I believe goes with the same logic for your explanation on the 0.1 issue.

    I’ve got one more good example to test the lack of accuracy, which I also like to relate to as “noise”.

    In cell A1 enter 0.2.
    Fill this value downwards in column A all the way to the bottom of the sheet.
    Adjust column B formatting to show 13 digits behind the decimal point.
    In cell B1 enter the formula =A1.
    In cell B2 enter the formula =B1+A2.
    Fill B2 formula downwards in column B all the way to the bottom of the sheet.

    Excel starts producing noise in column B quite early, from row 116 downwards. It shows in cell B116 23.1999999999999 instead of 23.2 as one would expect. When you check the situation at the bottom of column B, one row before the last in cell B1048575, it would show a difference of 0.00000323125277645886 (~ 3.23E-06) from the actual expected integer in column A.

    Here is another example to test in VBA:

    Sub Test_Increment()

    Dim i As Integer
    Dim Increment As Single

    For i = 1 To 100
    Increment = Increment + 0.2
    If Increment Round(Increment, 1) Then
    Debug.Print Format(Increment, “0.00000000000000”)
    ‘Formatting to VBA limitation of displaying up to 14 digits behind the decimal point
    Exit For
    End If
    Next i

    End Sub

    This short chunk of code is getting the noise much faster after only 7 iterations of the loop! The difference occurs when variable Increment is reaching the value of 1.4.

    If you dimension Increment as Double, there are only 3 iterations of the loop and the first difference occurs at just 0.6!

    Now here is my question for you Tom. I’m really puzzled about it. From what you explained Excel is doing calculations in formulas within cells based on double-precision floating-point. How come VBA doesn’t have the same parallel capability?

    The worst thing about VBA is that you cannot even see it visually anywhere (e.g. Immediate Window) while in an Excel sheet you can clearly attest it when you display more digits behind the decimal point. In my Excel example above you can also verify it with a formula for rows 115 and 116: =B115=115*0.2 returns TRUE but =B116=116*0.2 returns FALSE.

    • Tom Urtis says:

      Hello Gadi, thanks for your analysis. You raise an interesting point that I’m not familiar with. I’ll have to look into it further based on your examples, which I can do after tax season next month. If I hear anything or come across any explanation for why you are seeing what you are seeing, I’ll let you know. And if you find out anything, please post back. Thanks again.

Leave a Reply

Your email address will not be published. Required fields are marked *

*