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”)