3305 Main Street Ste 019 Vancouver, WA 98663

Why do some numbers appear with very small trailing decimal values?

Background

When working with numbers (such as amounts that may be queried from your database), you might encounter a result that doesn't appear to be precisely what you expect. For example, instead of the value of 0.00, you get a result that looks like this: In other words, 0.0000000000291038304567337. While the value is extremely close to zero and would have no material impact on your work, the value is nevertheless not zero. This article helps to explain the reason and offer a solution.

Floating Point Values

Amounts, including currency values, may be stored in your database as a special type of number called a floating point number. Floating point numbers can store a wide range of values but are ultimately stored as binary (base 2) representations. Some values (e.g., 0.2) when represented in binary as a floating point value would result in a value containing an infinite number of binary digits following the decimal point (e.g., decimal 0.2 = binary 0.00110011001100110011...).

Since the number of digits that can actually be stored in your database or in memory is limited, the result is an approximated value (truncating the repeating digits after a certain point). The result is a value that is extremely close to the intended value but it's actually an approximation.

The difference between the actual value and the approximated value is the floating point error which, again, is very very small. You may see evidence of this at times, such as in the sums of floating point values where the floating point errors add up to something noticeable but still very close to zero (e.g., 0.0000000000291038304567337).

Solution

Quite often, floating point differences go unnoticed because the number format applied to the cell in your worksheet performs automatic rounding and so a value such as 2.91038E-11, formatted with two decimals, appears as 0.00:

This becomes an issue though when performing a comparison on the value. For example, assume that cell C100 contains the formula =SUM(C5:C90). The values in that range add up to zero on a calculator, yet the SUM function returns the value 2.91038E-11. With the number format in that cell being set to show two decimals, the value is displayed as 0.00 and so it looks right.

Now let's assume that you have the formula =IF(C100=0,"In Proof","NOT IN PROOF"). To your surprise, the formula returns the text "NOT IN PROOF" even though the sum that is being displayed is 0.00. This is because the formula is acting on the calculated value, not the formatted representation of the value.

To resolve the issue, simply wrap the value in an Excel ROUND function like this: =ROUND(SUM(C5:C90),2). This tells Excel to round the sum to two decimals which effectively trims off the floating point error. The rounded result is what will be used by any formulas that reference this cell.