Microsoft Excel for Mac calculates a number based on its stored value not its
displayed value. When a formula or worksheet function performs a
calculation, Excel for Mac uses the values in cells that are referenced
by the formula. Note that the displayed value may be different because of
number formatting.
When you format numbers by clicking
Cells on the
Format menu, you alter the way Excel for Mac displays the numbers; however, when you calculate numbers, Excel for Mac 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
Excel for Mac 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.
Round 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 in a new worksheet:
$A$1: $10.005
$A$2: $10.005
$A$3: =SUM(A1:A2)
The resulting values in the cells appear to be:
$A$1: $10.01
$A$2: $10.01
$A$3: $20.01
This gives the appearance of an error in calculation, as stated earlier. To round the precision to reflect the apparent resulting value in A3, replace the entries with the following:
$A$1: =ROUND(10.005,2)
$A$2: =ROUND(10.005,2)
$A$3: =SUM(A1:A2)
If you now use the currency format, the following values are displayed:
$A$1: $10.01
$A$2: $10.01
$A$3: $20.02
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).
Round 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:
- Use the appropriate method for your version of Excel for Mac.
Microsoft Excel X for Mac and later versions of Excel for Mac
On the Excel menu, click Preferences, and then click Calculation in the left pane.
Microsoft Excel 2001 for Mac
On the Edit menu, click Preferences, and then click the Calculation tab.
Microsoft Excel 98 Macintosh Edition
On the Tools menu, click Preferences, and then click the Calculation tab.
- 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 for Mac permanently changes any constant values on the worksheets in the workbook. If you then calculate values with full precision, Excel for Mac cannot restore the original underlying values.
For more information about calculation precision, click
Microsoft Excel for Mac Help on the
Help menu, type
precision calculation in the Office Assistant or the Answer Wizard, and then click
Search to view the topics returned.
Article ID: 181918 - Last Review: January 22, 2007 - Revision: 3.2
APPLIES TO
- Microsoft Excel 2004 for Mac
- Microsoft Excel X for Mac
- Microsoft Excel 2001 for Mac
- Microsoft Excel 98 for Macintosh