Calculate future AHT ( Average Handling Time )

[roy00a4]

Hello Everyone,

I have been banging my head to figure out this task.

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl64, width: 64"]Name[/TD]
[TD="class: xl64, width: 64"]Wk 1 AHT[/TD]
[TD="class: xl64, width: 64"]Wk 2 AHT[/TD]
[TD="class: xl64, width: 64"]Wk 3 AHT[/TD]
[TD="class: xl64, width: 64"]Wk 4 AHT[/TD]
[TD="class: xl64, width: 64"]Target[/TD]
[/TR]
[TR]
[TD="class: xl64"]A[/TD]
[TD="class: xl65"]0:19:18[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]0:17:00[/TD]
[/TR]
[TR]
[TD="class: xl64"]B[/TD]
[TD="class: xl65"]0:20:17[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]0:17:00[/TD]
[/TR]
[TR]
[TD="class: xl64"]C[/TD]
[TD="class: xl65"]0:13:38[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]0:17:00[/TD]
[/TR]
[TR]
[TD="class: xl64"]D[/TD]
[TD="class: xl65"]0:15:31[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]0:17:00[/TD]
[/TR]
</tbody>[/TABLE]

I have the AHT for the first week by each of the agent. But the MTD target is 17 mins. What is the weekly AHT they need to maintain to meet the MTD target?

I need a formula to figure this out. I cant think of anything that woukld work. Please help !!

 

[Eric W]

Perhaps:

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Name[/TD]
[TD="bgcolor: #FAFAFA"]Wk 1 AHT[/TD]
[TD="bgcolor: #FAFAFA"]Wk 2 AHT[/TD]
[TD="bgcolor: #FAFAFA"]Wk 3 AHT[/TD]
[TD="bgcolor: #FAFAFA"]Wk 4 AHT[/TD]
[TD="bgcolor: #FAFAFA"]Target[/TD]
[TD="bgcolor: #FAFAFA"]average needed to make target[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:19:18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:21:21[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:14:23[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:11:58[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:17:00[/TD]
[TD="bgcolor: #FAFAFA"]Made target![/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:20:17[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:14:24[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:16:52[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:16:35[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:17:00[/TD]
[TD="bgcolor: #FAFAFA"]Missed target[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:13:38[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:15:43[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:17:00[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:19:20[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]D[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:15:31[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:17:00[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:17:30[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]0:00:00[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=IF(COUNT(B2:E2)=4,IF(SUM(B2:E2)/4< F2,"Made target!","Missed target"),(F2*4-SUM(B2:E2))/(4-COUNT(B2:E2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[roy00a4]

Hi Eric,

Thank you so much for the fast response and help. Amazing.

Cheers,
Roy

 

[Eric W]

Glad to help!