Rounding to the Nearest Limit in Excel

Last reviewed: November 10, 1997
Article 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: 5.00 2.1 2.10 3.0 3.00 4.0 4.00
Keywords : kbhowto

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