Microsoft Office Tutorials and References
In Depth Information
Dealing with Floating-Point Number Problems
Dealing with Floating-Point Number Problems
Excel users are often baffled by Excel’s apparent inability to perform simple math. It’s not the
program’s fault: Computers, by their 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 limitation might 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 discover
that Excel calculates the formula with a result of 0.899999999999999 — a value that’s close to
0.9, but certainly not 0.9. This result occurs because the operation in parentheses is performed
first, and this intermediate result is stored 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 doesn’t present a problem. However, if you need to test the
result of that formula by using a logical operator, it might 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 the ROUND function. The following formula, for
example, returns 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 instead returns –2.22045E-16 (a number very close to zero).
If that formula were in cell A1, the following formula would return Not Zero:
=IF(A1=0,”Zero”,”Not Zero”)
 
Search JabSto ::




Custom Search