Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

The General number format rounds off real numbers in Excel


View products that this article applies to.

Summary

The General number format rounds off numbers for display if the column width is not sufficient for the display of all the decimal digits. The underlying stored value in the cell is not changed.

↑ Back to the top


More information

If the number is formatted in the General number format and if the column width selected is less than the width needed to display the entire number, the number is rounded off to the nearest decimal place or whole number that the column width will allow. For example:
2.86 may be rounded to 2.9 or 3
2.3 may be rounded to 2
0.3 may be rounded to 0
This effect is different of that of the other number formats, which display number signs (#) across the width of the column if the width is not sufficient to display all the decimal digits. However the number will be displayed as (#) if the column width becomes too small to display the entire number.

Even though the number format is rounded, all calculations that are based on these values use the original stored values. This is true even if the Precision As Displayed option is selected on the Calculation tab of the Options dialog box (Options on the Tools menu).

Steps to See the Column Width Rounding Behavior

  1. Type the following in a new worksheet:
         A1: 2.3  B1:=SUM(a1:a8)  C1:  2.3   D1: =SUM(C1:C8)
         A2: 2.5                  C2:  2.5
         A3: 2.5                  C3:  2.5
         A4: 3.2                  C4:  3.2
         A5: 2.7                  C5:  2.7
         A6: 1                    C6:  1
         A7: 2.45                 C7:  2.45
         A8: 2.5                  C8:  2.5
    						
    Notice the results in B1 and D1. They should both be 19.15.
  2. Select column A.
  3. Point to Column on the Format menu and click Width. Type 2.86 for the column width and click OK.

    Notice that the displayed values in column A are now rounded to the nearest whole number, but the stored values have not changed. The SUM function in cell B1 proves the underlying values of the numbers has not changed.
  4. Select cell A1.
The formula bar still shows the original value, that is, 2.3. This is true for all values in column A.

↑ Back to the top


Keywords: KB182197, kbhowto

↑ Back to the top

Article Info
Article ID : 182197
Revision : 5
Created on : 1/22/2007
Published on : 1/22/2007
Exists online : False
Views : 355