Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jan 2011
    Location
    Illinois
    Posts
    62
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Need a rounding function

    Hi, everyone. Using Office 2010. I need to find a function that will allow me to direct Excel to round a value to nearest 5, so the following would occur:

    Cell entry: 72 Excel rounds to: 70
    Cell entry: 76 Excel rounds to: 75
    Cell entry: 79 Excel rounds to: 80

    Use case: Enter a goal value. Excel then calculates, using percentages, a series of incremental values that are rounded to the nearest 5 value.

    I'm trying to stay away from an elaborate VLOOKUP table and hoping there is a way to do this with rounding.

    Any help or ideas greatly appreciated!

    Thanks!

    Val in chilly IL

  2. #2
    Star Lounger
    Join Date
    Jan 2011
    Location
    Illinois
    Posts
    62
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Google to the rescue! In case anyone else needs this...

    http://www.consultdmw.com/excel-round-to-nearest-5.htm

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    You can just use a simple spreadsheet function:

    =ROUND([cell]/5,0)*5

    Ooops - saw that's what's in your link !
    Last edited by MartinM; 2013-03-20 at 21:25. Reason: Already answered !!

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    And if you don't feel like memorizing the formula, place it in a user defined function and call it like any built-in cell function

    In cell A1 =RoundBy5(B1)

    Code:
    Public Function RoundBy5(x As Double)
         RoundBy5 = Round(x / 5, 0) * 5  
    End Function

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Yes, but then your workbook contains macros, unless you store the function externally, e. g., in PERSONAL.XLSB or in an add-in.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    TX,
    Yep, that's the idea!

  7. #7
    New Lounger
    Join Date
    Apr 2013
    Posts
    16
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I realize this is an old post, but for anyone scanning it later, Excel has a function built-in that does exactly what is wanted.
    It is the MROUND() function. For the above rounding, if the value to be rounded is in Cell A1, the formulat to round to the nearest
    5 would be =MROUND(A1,5). The rounding value, 5 in this case, can also be a cell entry if there may be a need for it to change.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Yes indeed . . . worth mentioning that you have to have the Analysis ToolPak installed for MROUND to work.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Not in 2007 or later - it's built-in.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •