Formula for Rounding with Multiple Rules

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Formula for Rounding with Multiple Rules

  1. #1
    New Member
    Join Date
    Sep 2009
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula for Rounding with Multiple Rules

     
    Hi,

    I was wondering if it's possible to incorporate a few different rules into the same rounding formula? Basically, what I'm trying to achieve is rounding based on these rules:

    If number in reference cell is 0-1000
    o Round up (or down) to nearest 10 increment. Instead of landing on 100 increment, end with 90.
    o So: 10, 20, 30, 40, $0, 60, 70, 80, or 90.

    1000-5000
    o Round up (or down) to nearest 25 increment. Instead of landing on 100 increment, end with 90.
    o So: 25, 50, 75, or 90.

    5000+
    o Round up (or down) to nearest 25 increment. At this point, can land on 100 increments.
    o So: 25, 50, 75, 100

    Thanks so much for you help with this! Hopefully it is possible.

  2. #2
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,566
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Rounding with Multiple Rules

    I think I understand what you're after. I cannot for the life of me imagine why one would want to round a number like 1006 down to 990.

    This formula works but could probably be improved by someone smarter than me.

    Put a positive number in a1

    Code:
    =IF(A1>=5000,ROUND(A1/25,0)*25,IF(AND(A1>=1000,A1<5000),IF(MOD(ROUND(A1/25,0)*25,100)=0,ROUND(A1/25,0)*25-10,ROUND(A1/25,0)*25),IF(MOD(ROUND(A1/10,0)*10,100)=0,INT(A1/10)*10,ROUND(A1/10,0)*10)))
    Windows10, Excel 2016 (365 Insider)
    My formulas are always in
    Green
    You can get the HTML Maker.

  3. #3
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,566
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Rounding with Multiple Rules

    Edit. Oops, I made a mistake. Here is the revision.

    Code:
    =IF(A1>=5000,ROUND(A1/25,0)*25,IF(AND(A1>=1000,A1<5000),IF(MOD(ROUND(A1/25,0)*25,100)=0,ROUND(A1/25,0)*25-10,ROUND(A1/25,0)*25),IF(MOD(ROUND(A1/10,0)*10,100)=0,ROUND(A1/10,0)*10-10,ROUND(A1/10,0)*10)))
    Windows10, Excel 2016 (365 Insider)
    My formulas are always in
    Green
    You can get the HTML Maker.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,599
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula for Rounding with Multiple Rules

    You could also try this which I believe gives the same results as the formula already suggested.

    =MROUND(A1,IF(A1<1000,10,25))-10*(A1<5000)*(MOD(MROUND(A1,IF(A1<1000,10,25)),100)=0)
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  5. #5
    New Member
    Join Date
    Sep 2009
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Rounding with Multiple Rules

    Awesome - this works great.

    Thanks everyone!

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,599
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula for Rounding with Multiple Rules

    Quote Originally Posted by Kenichi View Post
    Awesome - this works great.

    Thanks everyone!
    Glad you got a successful outcome.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  7. #7
    New Member
    Join Date
    Sep 2009
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Rounding with Multiple Rules

      
    Can anyone help with adding one more criteria to this formula?

    =MROUND(A1,IF(A1<1000,10,25))-10*(A1<5000)*(MOD(MROUND(A1,IF(A1<1000,10,25)),100)=0)

    I'd like for A1 (price) minus B1 (cost) to never be below 200. Is it possible to add that rule to the formula? A1-B1 should never be below 200, but it needs to still be rounded according to the rule above.

    Thanks!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com