Rounding Up to nearest 50 or 100

AWood

Hi all, 

I'm trying to round up to nearest 50 or 100 in a smartsheet. So far, I have =IF(AND(Count1 < 50), "50", ROUND(Count1, -2)). However, this only account to values less than 100. I was hoping anyone could help me for values over 100 (i.e. 101 is rounded to 150 and 155 to 200). 

Charles DESCOTES

=IF(Count1 < 50, 50, ROUND(Count1 / 50, 0) * 50)

J. Craig Williams

Why do you need to check for less than 50? Are you worried about negative numbers? Then check for that. If not, you can skip the check.

=ROUND(COUNT@row / 50, 0) * 50

Craig

AWood

I've been experiencing an issue that when a value is less than 50, my formula then rounds the value to 0. 

AWood

Thanks for your help! Unfortunately, this formula, for values over 50 (but closer to 50 instead of 100), it still rounds to 50 instead of rounding up. Would you perhaps know of any tricks that if a value is between 0-50, then it round to 50, and then 50-100, then round up to 100, and so forth?

Charles DESCOTES

That's what I assumed looking at your first proposal.

Indeed, zero is a quite a value for misunderstanding as it means "nothing".

Is your 1-24 segment a big one or critical?

If so rounding with sense could be 

=IF(Count1 < 25, 1, ROUND(Count1 / 50, 0) * 50)

→ Yielding 0 will mean "nothing" indeed

1 will mean the original data was between [1 and 25[

50 means that the original value was between [25 and 55[

up to you.

AWood

Unfortunately it is a critical difference, 'Count1' determines the amount of material we would need for production. As well, we only use material in batches of 50, 100, 150, etc. Therefore, if the amount of material needed was '24', 1 would be an unsatisfactory amount. Sorry, and I really appreciate you hashing this out with me!

Charles DESCOTES

No worries, get back to 

=IF(Count1 < 50, 50, ROUND(Count1 / 50, 0) * 50)

However, if you need 51 pieces, then you must order 2*50

And even from the need of 50, as a minimum safety net, or consider a safety stock and add up with your production requirements:

=ROUND(Count1 / 50 + 0.5, 0) * 50

 

AWood

THANK YOU! It works perfectly!