Microsoft KB Archive/63973

{|
 * width="100%"|

Rounding to the Nearest Limit in Excel

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0

-

SUMMARY
Microsoft Excel is capable of rounding to the nearest specified number of digits, but to round to the nearest fraction, you must use the following formula:

  =IF(MOD(A1,fraction)<(fraction/2),A1-MOD(A1,fraction),    A1+fraction-MOD(A1,fraction))

where A1 contains the value that you want to round, and &quot;fraction&quot; stands for the fraction that you want to round towards. For example, to round to the nearest one-eighth, use the following formula:

  =IF(MOD(A1,(1/8))<((1/8)/2),A1-MOD(A1,(1/8)),A1+(1/8)   -MOD(A1,(1/8)))

Similarly, you can use this same approach to round to a specific integer. For example, if you want to round to the nearest multiple of five, use the following formula:

 =IF(MOD(A1,5)<(5/2),A1-MOD(A1,5),A1+5-MOD(A1,5)) Additional query words: 2.1 2.10 3.0 4.0

Keywords : kbhowto

Version :

Platform :

Issue type :

Technology :