Calculate Precision As Displayed Versus Format Number

The information in this article applies to:

• Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0

SUMMARY

In Microsoft Excel, numbers are rounded based on their stored values rather than on their displayed values.

When you format numbers by choosing the Cells command from the Format menu (the Numbers command in versions earlier than 5.0), you alter the way the numbers are displayed; however, when you calculate numbers, the calculations are performed with the stored versions of these numbers. When you calculate formatted values, you may receive unexpected results because the displayed versions of the numbers may be slightly different than their stored values.

WORKAROUND

To calculate numbers based on their displayed values, select the Precision As Displayed option. This option truncates numbers in cells so that the stored number is the same as the displayed number.

To select the Precision As Displayed option, follow the appropriate procedure below.

Microsoft Excel versions 5.0 and later

1. From the Tools menu, choose Options.

2. Select the Calculation tab.

3. On the Calculation tab, under Workbook Options, select the Precision As Displayed check box.

Microsoft Excel versions 4.x and earlier

1. From the Options menu, choose Calculation.

2. In the Calculation dialog box, select the Precision As Displayed check box.

MORE INFORMATION

The Precision As Displayed command typically is used to prevent rounding errors when working with currency.