Microsoft Office Tutorials and References

In Depth Information

=1/3

Figure 22-4:
A simple demonstration of numbers that appear to add up incorrectly.

Cell B5 contains the following formula:

=SUM(B2:B4)

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

display an incorrect result. (You may expect it to display 0.99.) 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 on the

Advanced tab of the Excel Options dialog box. (Choose File
⇒
Options to display this dialog box.) This setting

applies to the active workbook.

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

stand 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