Welcome!

Rounding Up to nearest 50 or 100

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

Find more posts tagged with

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Is it possible to edit an email list in an automated monthly report?
I have a monthly report I send out that has a long list of recipients. I know I can edit the recurrence of it being sent, but does anyone know of an easy way to just add or take out people that get my email without having to recreate the whole thing?
User Friendly Search
We would like to allow conference attendees to check themselves in using tablets at the conference center. I know there is a "find in sheet" magnifying glass in table view, but it is not super obvious, especially for non-Smartsheet users. Is there a better way for them to easily find their row among all the attendees so…
2 part automation delay question?
Greetings- I have a automation setup when I select the "status" column drop-down to "completed" I want the row info to be emailed to the contact to submitted it with a follow up then I want it to archive the row (aka move to another sheet). When I run the automation only 1 of the 2 requests happen. The archive happens…