Excel: Displaying Significant Figures

 Last reviewed: September 18, 1994
Article ID: Q70137
The information in this article applies to:
  • Microsoft Excel for the Macintosh, versions 1.x, 2.x, 3.0

SUMMARY

In many scientific applications, a calculated number must be displayed to a specific number of significant digits. For instance, a measurement or calculation may give a result that has many figures, but the experimenter may only want to display the values to a certain number of significant digits.

The following is a series of numbers rounded to 2 significant figures: 

   1234 = 1200
   123.4 = 120
   12.34 = 12
   1.234 = 1.2
   0.1234 = 0.12
   0.01234 = 0.012
Note that the last significant figure is rounded. Rounding for significant figures is down from 1 to 4 and up from 5 to 9.

The following Microsoft Excel worksheet formula rounds a number to a specified number of significant figures 

   =ROUND(valtoconvert,sigdigits-INT(LOG(valtoconvert))-1)
where "valtoconvert" is the value or reference to a cell containing the value to be converted and "sigdigits" is the number of significant figures or a reference to a cell containing the number of significant figures.

For example, if cell A1 contains the number to be converted and cell A2 contains the number 5, the following formula will return the number in cell A1 converted to 5 significant digits: 

   =ROUND(A1,A2-INT(LOG(A1))-1)

KBCategory: kbusage
KBSubcategory:

Additional reference words: 2.20 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: September 18, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.