phillipcook
Board Regular
- Joined
- Jun 25, 2015
- Messages
- 87
Hi Guys
I am trying to work out a call out charge
with this charge, if the total call out hours worked on a day are less than 4.0 hours, (for example a total of 5 different call outs equal 03:40 hours OT worked) than the charge rate becomes 4 hours.
In my calculation I have 10 possible lines worth of data from $R$2:$R$11.
The formula that I currently have works which is as follows:
=IF(R2="","",IF(SUM($R$2:$R$11)>=0.16666667,R2,0.16666667))
However I have found a flaw in this calculation.
for example lets say I have 5 rows of OT (between $R$2:$R$11) that combined have a total of 03:40 call outs. the above formula would actually calculate the formula cells from S2:S11 to 4.0 hours each which means the total call out hours would be 20.0 hours for the day and not 4.0
What I need to do is the following:
> If cell R2 is blank, then the formula cell remains blank
> if R2 has data then calculate the sum of $R$2:$R$11, if it is above 04:00 then S2 cell equals R2
> If the calculation of $R$2:$R$11 is less than 04:00 then the formula needs to do the following:
*EXAMPLE OF CALL OUT TIMES IN R2:R11*
row 1 00:30
row 2 01:00
row 3 00:25
row 4 00:15
row 5 01:30
This calculation would be in S2
> Combined the total of hours (example 03:40)
> Convert the value from time to percentage (example 03:40 would become 3.67)
> Calculate 1% of the total time in R2:R11 (example 1% of 3.67 becomes 0.036667)
> Convert R2 time to percentage and devide by the 1% value of the entire summed time (example 0.5/ 0.036667=13.63636)
> Convert 04:00 time to percentage and calculate 1% of 4:00 hours (example 04:00 becomes 4.00/ 100 = 0.02)
> Multiply R2 percentage by the 1% of 4 hours (example 0.02*13.63636=0.02272727)
The formula cell formatting would need to be [HH]:mm for this to work.
I just can't figure out how to convert the above mathematics to a formula
All help would be greatly appreciated!!
I hope I explained it well enough?
Thanks in advance.
I am trying to work out a call out charge
with this charge, if the total call out hours worked on a day are less than 4.0 hours, (for example a total of 5 different call outs equal 03:40 hours OT worked) than the charge rate becomes 4 hours.
In my calculation I have 10 possible lines worth of data from $R$2:$R$11.
The formula that I currently have works which is as follows:
=IF(R2="","",IF(SUM($R$2:$R$11)>=0.16666667,R2,0.16666667))
However I have found a flaw in this calculation.
for example lets say I have 5 rows of OT (between $R$2:$R$11) that combined have a total of 03:40 call outs. the above formula would actually calculate the formula cells from S2:S11 to 4.0 hours each which means the total call out hours would be 20.0 hours for the day and not 4.0
What I need to do is the following:
> If cell R2 is blank, then the formula cell remains blank
> if R2 has data then calculate the sum of $R$2:$R$11, if it is above 04:00 then S2 cell equals R2
> If the calculation of $R$2:$R$11 is less than 04:00 then the formula needs to do the following:
*EXAMPLE OF CALL OUT TIMES IN R2:R11*
row 1 00:30
row 2 01:00
row 3 00:25
row 4 00:15
row 5 01:30
This calculation would be in S2
> Combined the total of hours (example 03:40)
> Convert the value from time to percentage (example 03:40 would become 3.67)
> Calculate 1% of the total time in R2:R11 (example 1% of 3.67 becomes 0.036667)
> Convert R2 time to percentage and devide by the 1% value of the entire summed time (example 0.5/ 0.036667=13.63636)
> Convert 04:00 time to percentage and calculate 1% of 4:00 hours (example 04:00 becomes 4.00/ 100 = 0.02)
> Multiply R2 percentage by the 1% of 4 hours (example 0.02*13.63636=0.02272727)
The formula cell formatting would need to be [HH]:mm for this to work.
I just can't figure out how to convert the above mathematics to a formula
All help would be greatly appreciated!!
I hope I explained it well enough?
Thanks in advance.
Last edited:
