Microsoft Office Tutorials and References

In Depth Information

**Understanding Displayed versus Actual Values**

Understanding Displayed versus Actual Values

You might encounter a situation in which values in a range don’t appear to add up correctly. For

example, Figure 201-1 shows a worksheet with the following formula entered into each cell in the

range B3:B5:

=1/3

Figure 201-1:
A simple demonstration of numbers that appear to add up incorrectly.

Cell B6 contains the following formula:

=SUM(B3:B5)

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

B6 appears to display an incorrect result. (You might expect it to display 0.99.) The formula, of

course, returns the correct result. The formula uses the actual values in the range B3:B5, not the

displayed values.

You might be tempted to instruct Excel to use the displayed values. You do this 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.)

Generally, using the Set Precision As Displayed check box isn’t the best way to handle this type

of problem. Checking the Set Precision As Displayed check box also affects normal values (not

formulas) 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 deselect the Set Precision as Displayed check box.

A better approach is to use the Excel ROUND function to round the values to the number of

decimal places you want. In the example shown here, the range B3:B5 would contain this formula:

=ROUND(1/3,2)

Summing the three values results in 0.99.