Formula for Rounding with Multiple Rules

Thanks:  0
Likes:  0

# Thread: Formula for Rounding with Multiple Rules

1. ## 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. ## 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)))`

3. ## 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)))`

4. ## 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)

5. ## Re: Formula for Rounding with Multiple Rules

Awesome - this works great.

Thanks everyone!

6. ## Re: Formula for Rounding with Multiple Rules

Originally Posted by Kenichi
Awesome - this works great.

Thanks everyone!
Glad you got a successful outcome.

7. ## 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

#### Posting Permissions

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