Excel: Rounding Up or Down to a Defined Precision Limit

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

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 significant figure noupd

Keywords :
Version :
Platform :
Issue type :