Divide a number by 8 and limiting the result

[BrianDC]

Hi people - please help with this calculation, been very frustrating.
I am trying to test a number eg 11 hours to ensure that it results in no less than 1.5 days (based on 8 hour day). This means that when 11 is divided by 8 = 1.375, the number (11) should be increased to produce a result of 1.5, therefore 11 will become 12. Similarly, 34/8=4.25, 34 would increase to 36. If the number divided by 8, eg 14/8 results in a decimal that is => 1.5 the number will remain unchanged.
I have tried using If(AND together with TRUNC and -TRUNC but cannot get the syntax right.
Many thanks
Brian

[johnkat]

I'm sorry if I misunderstood.

Would converting hours to minutes cover this? " if 'cell_ref(660)' >= 'cell_ref(720)' (660 mins = 11 hours - 720 = 12 hours) No division of days required. Formula can be put in the cells to convert hours to minutes.

[BrianDC]

Hi Jonkat, thanks for reply. My explanation was not clear.
What I am trying to do is,
Assume Cell A10 has a value of 11 and this represents Hours. I want to test this number to ensure that the
it converts to at least 1.5 days. A standard work day is 8 hours, therefore 11/8, results in working days
of 1.375. I want to limit the working days to a minimum of 4 hours or more (up to 8). In this example the work
days are 1.375 therefore I want to increase the number 11 so that the calculation results in at least 1.5 days.
. Cell A10 then needs to increase to 12.
If the cell A10 had a value of 14, the work days would be 1.75, in that case the number in cell A10 would
remain unchanged.
Hope that is a little clearer.
Brian

[bebo021999]

If A10 = 1,...,8 =>8
A10=9,...,12 =>12
A10=13 or greater => 12

Try:

=MIN(12,CEILING(A10,4))

If you want to display 12 as 1.5

=MIN(12,CEILING(A10,4))/8

[BrianDC]

Hi Bebo021999
Thanks for your reply. My explanation is still not good. I was only the number 11 and 12 as an example. The number in A10 could be any whole number, eg 123, 297, 465 etc. therefore when this number is divided by 8, it produces a decimal that is less than 0.5 e.g. 15.375, 37.125,58.125. I want to change the value in A10 so that when divided by 8 it will always have a decimal of 0.5 or higher, eg if A10 was 123, dive by 8 is 15.375, I want to change A10 automatically to result in .5 or higher decimal, so the #123 in A10 will be changed to 124 which when divided by 8 will be 15.5.
The way I was trying to do this was to Divide A10 by 8, and when the result was below .5 decimal, take the "integer" 123/8=15.375 (15) and add 0.5 then multiply by 8 to get the correct number for cell A10 which would be 15.5
Thanks
Brian

[bebo021999]

In other cell, type:

=MROUND(A10/8,0.5)*8

Then copy/paste value back to A10?