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.