Microsoft Office Tutorials and References
In Depth Information
Figure 22-4: A simple demonstration of numbers that appear to add up incorrectly.
Cell B5 contains the following formula:
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