Microsoft KB Archive/80478

{|
 * width="100%"|

Excel: Rounding Up or Down to a Defined Precision Limit

 * }

-

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 &quot;Reference&quot; is a cell containing the number to be rounded and &quot;Precision&quot; 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 &quot;number&quot; 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