Due to security limitations in my workplace, I am unable to upload my file.
I have created a spreadsheet to record quality checking of phone calls and the actual call durations for specific work done by my team. This gives me a total number of calls to be completed within a specific timeframe (e.g. the target is 126 calls to be checked)
In Cell D1, I can see the cumulative duration of calls checked thus far:
=SUM(D3:D20,F3:F20,H3:H20,J3:J20,L3:L20,N3:N20,P3: P20,R3:R20,T3:T20)/(24*60)
The cell format is [h]:mm
The result displayed is 11:02 (i.e. eleven hours and 2 minutes total duration)
In Cell F1, I can see the number of calls pending (i.e. the Target minus the calls checked thus far
In Cell H1, I can see the total number of calls checked thus far:
=COUNTA(C3:C20,E3:E20,G3:G20,I3:I20,K3:K20,M3:M20,O3:O20,Q3:Q20,S3:S20)
The cell format is General
The result displayed is 13 (i.e the sum of the calls recorded thus far)
In Cell L1, I can see the average call duration:
=(D1*1440/H1/(24*60))
The cell format is [h]:mm
The result displayed is 0:50 (i.e average of 50 minutes per call across the 13 completed thus far).
Given there are 113 calls remaining, I need a simple formula (and cell format) that will multiply 113 x cell L1 to provide me with a total HH:MM forecast of the time required to complete this work. (Note: I'm not trained in VBA Sorry)
I have created a spreadsheet to record quality checking of phone calls and the actual call durations for specific work done by my team. This gives me a total number of calls to be completed within a specific timeframe (e.g. the target is 126 calls to be checked)
In Cell D1, I can see the cumulative duration of calls checked thus far:
=SUM(D3:D20,F3:F20,H3:H20,J3:J20,L3:L20,N3:N20,P3: P20,R3:R20,T3:T20)/(24*60)
The cell format is [h]:mm
The result displayed is 11:02 (i.e. eleven hours and 2 minutes total duration)
In Cell F1, I can see the number of calls pending (i.e. the Target minus the calls checked thus far
In Cell H1, I can see the total number of calls checked thus far:
=COUNTA(C3:C20,E3:E20,G3:G20,I3:I20,K3:K20,M3:M20,O3:O20,Q3:Q20,S3:S20)
The cell format is General
The result displayed is 13 (i.e the sum of the calls recorded thus far)
In Cell L1, I can see the average call duration:
=(D1*1440/H1/(24*60))
The cell format is [h]:mm
The result displayed is 0:50 (i.e average of 50 minutes per call across the 13 completed thus far).
Given there are 113 calls remaining, I need a simple formula (and cell format) that will multiply 113 x cell L1 to provide me with a total HH:MM forecast of the time required to complete this work. (Note: I'm not trained in VBA Sorry)