Round Up

TerrieVent

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?

 

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