XL: Number Formatting Affects Perceived Precision (323625)

MORE INFORMATION

When you format numbers by clicking Cells on the Format menu, you alter the way Excel displays the numbers; however, when you calculate numbers, Excel performs the calculations by using the stored values. When you calculate formatted values, you may receive unexpected results because the displayed numbers may be slightly different from the stored values.

For example, if two cells each contain the value 10.005, and the cells are formatted to display values as currency, the value $10.01 is displayed in each cell. If you add the two cells together, the result is $20.01 because Microsoft Excel adds the stored values (10.005 + 10.005), not the displayed values ($10.01 + $10.01).

If you want the calculations to be based on the displayed values, you can change the precision of calculations so that they use the displayed values instead of the stored values. To do this, use the Precision As Displayed feature or make sure that the values are rounded to the same precision as the number formatting.

Rounding Values to the Same Precision as Number Formatting

You can use the ROUND worksheet function to round values to a specific precision.

Example:

For this example, type the following into a new worksheet:

   $A$1: 5
   $A$2: 200%
   $A$3: =A1*A2
				

The value in cell $A$3 is 10.005. The value in cell $A$3 displays $10.00 if you format the value as currency. To round the precision correctly, replace the formula in cell $A$3 with the following:

   $A$3: =ROUND(A1*A2,2)
				

When you do this, the worksheet function takes the result of the expression in the first argument and rounds it to the hundredth's place (two digits to the right of the decimal).

Rounding All Values in a Worksheet by Using Precision As Displayed

If you want the calculations to be based on the displayed values, change the precision of calculations so that they use the displayed values instead of the stored values. To do this, follow these steps:

1. On the Tools menu, click Options, and then click the Calculation tab.
2. Under Workbook Options, click to select the Precision as displayed check box.

CAUTION: When you change the precision of the calculations in a workbook by using the displayed (formatted) values, Excel permanently changes any constant values on the worksheets in the workbook. If you subsequently calculate values with full precision, Excel cannot restore the original underlying values.