Excel 2010 Bible

Chapter 32: Making Your Worksheets Error-Free

659

Cell B5 contains the following formula:

=SUM(B2:B4)

All the cells are formatted to display with three decimal places. As you can see, the formula in cell

B5 appears to display an incorrect result. (You may expect it to display 0.999.) The formula, of

course, does return the correct result. The formula uses the actual values in the range B2:B4 not the

displayed values.

You can instruct Excel to use the displayed values by selecting the Set Precision as Displayed check

box of the Advanced section of the Excel Options dialog box. (Choose File ➪ Excel Options to dis-

play this dialog box.)

Caution
Be very careful with the Set Precision as Displayed option. This option 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 Precision as Displayed check box converts 4.68 to 5.00. This change is perma-
nent, 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 the ROUND function to round off the values to the desired number of decimal places. n

Floating point number errors

Computers, by their very nature, don’t have infinite precision. Excel stores numbers in binary for-

mat by using eight bytes, which can handle numbers with 15-digit accuracy. Some numbers can’t

be expressed precisely by using eight bytes, so the number stores as an approximation.

To demonstrate how this lack of 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 dis-

cover that Excel calculates the formula with a result of 0.899999999999999. This result 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 approxi-

mation 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 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 the ROUND function. The following formula, for exam-

ple, returns TRUE because the comparison is made by using the value in A1 rounded to one deci-

mal place.

=ROUND(A1,1)=0.9