Excel: Making Numbers Appear Rounded While Keeping Accuracy

Last reviewed: November 30, 1994
Article ID: Q58501
Summary:

To make a number appear rounded even though its exact value is being used for calculations, combine numeric and text formats in the Number dialog box on the Format menu.

For example, if you want the number 1,347,698 to appear as $1,348,000 while reserving its accuracy for calculations, you can format the number as $###,###,",000". Excel's ability to crop numbers after a particular value to the left of the decimal place, to add appropriate commas, and to add text to a number's appearance makes this possible. The following table provides examples:

   This Number              Formatted As              Yields
   -----------              ------------              ------

   1578999                  ###,###,                  1,579
                            ###,###, "Thousand"       1,579 Thousand
                            $###,###,",000"           $1,579,000
   5789                     $###,###,",000"           $6,000

More Information:

Selecting Precision As Displayed from the Calculations dialog box on the Options menu does not permanently change the original value (because you are combining text with the number format) unless the digits following the decimal are changed.

Note: This information also applies to Excel for Windows 2.1, 2.1c, 2.1d, and 3.0


KBCategory: kbusage
KBSubcategory:

Additional reference words: 2.10 2.10c 2.10d 3.00


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.