Rounding Down To Nearest 100 With A Twist

[sservold]

Is it possible to create a formula that can round a dollar value down to the nearest 100 increment IF the value is within $20 of that lower increment?


lE. We pay x amount per 100, but any cent over $100 rounds to $200 and so on.


I'd prefer to round the retail value down to the closest $100, $200, $300, etc if it's over that value by $20 or less.


Similar to =FLOOR(A1,100) but with the caveat of the within .01 - 20.00 range



Attached are sample retail values

 

[Misca]

Welcome to the board!

Try something like =CEILING(A1-20,100) or =ROUND(A1+30,-2). Both should round down when the increment is <=20 and up from there on.

 

[Phuoc]

Hi,

What results do you expect when you have: $ 100, $ 110, $ 120, $ 130, $ 190?

 

[sservold]

Welcome to the board!

Try something like =CEILING(A1-20,100) or =ROUND(A1+30,-2). Both should round down when the increment is <=20 and up from there on.

thank you! and thanks for the quick response.

=ROUND(A1+30,-2) was the answer!!!

 

[sservold]

Hi,

What results do you expect when you have: $ 100, $ 110, $ 120, $ 130, $ 190?

100 = 100
110 = 100
120 = 100
130 = 130 (insurance company would round this up to 200 themselves, so we could presumably round to 200 as well)
190 = 190 (insurance company would round this up to 200 themselves, so we could presumably round to 200 as well)

 

[sservold]

Welcome to the board!

Try something like =CEILING(A1-20,100) or =ROUND(A1+30,-2). Both should round down when the increment is <=20 and up from there on.

i have a follow up question though. as i'm unsure how the +30,-2 works. what if i wanted to change the 20 value to something larger or smaller?

 

[Phuoc]

Try rhis:

=IF(MOD(A1,100)<=20,FLOOR(A1,100),A1)

 

[Misca]

i have a follow up question though. as i'm unsure how the +30,-2 works. what if i wanted to change the 20 value to something larger or smaller?

That's two different things you're referring: The -2 tells the ROUND function you want to round using the -2 decimals ie. using full hundreds. And since the ROUND function rounds anything less than 5 (or 50 in this case) down and 50 and up to the next hundred the 30 I'm using means I want to add 50-20 (= 30) to the original value to make it follow the normal rounding rules.