Microsoft Office Tutorials and References
In Depth Information
Computers, by their very nature, don't have infinite precision. Excel stores numbers in binary format by using 8
bytes, which can handle numbers with 15-digit accuracy. Some numbers can't be expressed precisely by using 8
bytes, so the number is stored as an approximation.
To demonstrate how this limited precision may cause problems, enter the following formula into cell A1:
=(5.1–5.2)+1
The result should be 0.9. However, if you format the cell to display 15 decimal places, you'll discover that Excel
calculates the formula with a result of 0.899999999999999. This small error occurs because the operation in
parentheses is performed first, and this intermediate result stores in binary format by using an approximation.
The formula then adds 1 to this value, and the approximation error is propagated to the final result.
In many cases, this type of error does not present a problem. However, if you need to test the result of that for-
mula by using a logical operator, it may present a problem. For example, the following formula (which assumes
that the previous formula is in cell A1) returns FALSE:
=A1=.9
One solution to this type of error is to use Excel's ROUND function. The following formula, for example, re-
turns TRUE because the comparison is made by using the value in A1 rounded to one decimal place:
=ROUND(A1,1)=0.9
Here's another example of a “precision” problem. Try entering the following formula:
=(1.333–1.233)–(1.334–1.234)
This formula should return 0, but it actually returns –2.220446E–16 (a number very close to zero).
If that formula were in cell A1, the following formula would return the string Not Zero.
=IF(A1=0,”Zero”,”Not Zero”)
One way to handle these very-close-to-zero rounding errors is to use a formula like this:
=IF(ABS(A1)<1E–6,”Zero”,”Not Zero”)
This formula uses the less-than operator to compare the absolute value of the number with a very small number.
This formula would return Zero.