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.
 
Search JabSto ::




Custom Search