Microsoft Office Tutorials and References

In Depth Information

**Formula Problems and Solutions**

Use the Set Precision as Displayed option with caution, and make sure that you

understand how it works. This setting also affects normal values (nonformulas) that have

been entered into cells. For example, if a cell contains the value 4.68 and is displayed

with no decimal places (that is, 5), selecting the Set Precision as Displayed check box

converts 4.68 to 5.00. This change is permanent, and you can’t restore the original

value if you later clear the Set Precision as Displayed check box. A better approach is

to use Excel’s ROUND function to round the values to the desired number of decimal

places (see Chapter 10). I’ve used Excel for many years, and I’ve never had a need to

use the Set Precision as Displayed option.

Floating-point number errors

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 stores 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 formula 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, returns TRUE because the comparison is made by using the value in A1 rounded to one

decimal place:

=ROUND(A1,1)=0.9