Excel: Rounding Up or Down to a Defined Precision Limit

Last reviewed: November 29, 1994
Article ID: Q80478

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0

SUMMARY

It is possible to force Microsoft Excel to round numbers either up or down to a desired level of precision.

To round a number up, use the following formula:

   =-INT(-Reference/Precision)*Precision

To round down, use the following formula:

   =INT(Reference/Precision)*Precision

Where "Reference" is a cell containing the number to be rounded and "Precision" specifies the rounding limit. To round to the nearest tenth, use 0.1 for Precision, and for the nearest multiple of ten, use 10.

If you are using version 4.0, the CEILING function returns "number" rounded up to the nearest multiple of significance.

NOTE: Microsoft Excel version 5.0 now have the RoundUp() and RoundDown() functions.

MORE INFORMATION

Rounding up to nearest tenth produces the following results:

   55.87329  becomes 55.9
   55.83000  becomes 55.9
   55.80000  remains 55.8

Rounding down to nearest tenth produces the following results:

   55.87329  becomes 55.8
   55.83000  becomes 55.8
   55.80000  remains 55.8

Examples using the CEILING function:

   if cell A1 contains 1.111
   =CEILING(A1,1) will return 2
   =CEILING(A1,0.1) will return 1.2
   =CEILING(A1,0.01) will return 1.12
   =CEILING(A1,0.001) will return 1.112

For additional information on rounding, query on the following words:

   rounding and set and limit

REFERENCES

"Function Reference," version 4.0, page 244

"Microsoft Excel Function Reference," for the Macintosh, version 3.0, page 133

KBCategory: kbusage
KBSubcategory:
Additional reference words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0
3.00 4.0 4.00 significant figure noupd

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 29, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.