Number formatting affects perceived precision in Excel for Mac
For example, if two cells each contain the value 10.005, and the cells areformatted to display values as currency, the value $10.01 is displayed ineach cell. If you add the two cells together, the result is $20.01 becauseExcel for Mac adds the stored values (10.005 + 10.005), not the displayedvalues ($10.01 + $10.01).
If you want the calculations to be based on the displayed values, you canchange the precision of calculations so that they use the displayed valuesinstead of the stored values. To do this, use the Precision As Displayedfeature or make sure that the values are rounded to the same precision asthe number formatting.
Round Values to the Same Precision as Number FormattingYou can use the ROUND worksheet function to round values to a specificprecision.
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.01This 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.02When you do this, the worksheet function takes the result of the expressionin the first argument and rounds it to the hundredth's place (two digits tothe right of the decimal).
Round All Values in a Worksheet by Using Precision As DisplayedIf you want the calculations to be based on the displayed values, changethe precision of calculations so that they use the displayed values insteadof 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 MacOn the Excel menu, click Preferences, and then click Calculation in the left pane.
Microsoft Excel 2001 for MacOn the Edit menu, click Preferences, and then click the Calculation tab.
Microsoft Excel 98 Macintosh EditionOn the Tools menu, click Preferences, and then click the Calculation tab.
- Under Workbook Options, click to select the Precision as displayed check box.
Article ID: 181918 - Last Review: 12/05/2015 08:26:46 - Revision: 3.2
- kbnosurvey kbarchive kbinfo KB181918