Round Up

I have a calculation for Total Hours that takes the DURATION (set to days) multiplied by UTILIZATION percent, then translates to hours by multiplying by 8 hrs. I want the result to always be a whole number:

"round up" to the next whole number if the decimal is greater .25, otherwise round down.

Currently, the formula in the cell is:

=Duration@row * Utilization@row * 8

Examples:

13 days x 6% x 8 hrs = 6.24. I want this number to round down to 6.00 instead of 6.24

3 days x 20% x 8 hrs = 4.80. I want this number to round up to 5.00 instead of 4.80

I can see the ROUND function, but do not see a ROUNDUP function.

Any recommendations?

Find more posts tagged with

Formulas

Comments

Eid E. Eid

Try using the Formula :

=IF(ROUND(X)<X,ROUND(X)+1,ROUND(X))

This should do it....

TerrieVent

Hello Eid,

Thank you for your quick reply! This is the first time I have used the Community forum and I am impressed with the response time.

I changed my post and I am not certain if your response pertains to the original post or the updated post. Additionally, is it possible to do the rounding calculation in the same cell as the original formula so that I can do it all in one cell? If so, can you provide an example?

Eid E. Eid

Hello Terrie,

Actually my answer was for your original post.

In order to reflect the « 0.25 » threshold, you shoulduse :

IF(X<round(X),round(X),IF(X<round(X)+0.25,round(X),round(X)+1))

I would prefer you do this in 2 cells, rather than in the same cell, because you would have to replace each X above with the formula:durationnxutilisationx8, which will become cumbersome.

I hopethis helps

TerrieVent

Hello Eid,

Thank you for your sample above. Was traveling for a while and just got back to this.

As noted above, in the column "Hours:Services", I have the following formula:

=Duration@row * Utilization@row * 8

I understand that you recommend adding a new column with the formula noted above, however I am not sure what to replace each of the "X" with. Can you offer further assistance?

Thank you!

TerrieVent

Wait, I think I got it!

=IF([Hours - Services]@row< ROUND([Hours - Services]@row), ROUND([Hours - Services]@row), IF([Hours - Services]@row < ROUND([Hours - Services]@row) + 0.25, ROUND([Hours - Services]@row), ROUND([Hours - Services]@row) + 1))

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?