Rounding to the Nearest Limit in Excel

ID: Q63973

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 "fraction" 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        :

Last Reviewed: March 22, 1999