Average Handle Time for Selected agents.

jackfox68

Board Regular
Joined
Jan 24, 2010
Messages
121
Hello,

I am trying to figure out how to calculate the weighted average handle time (AHT) for a selected agent, and for selected splits. So if I select Jarvis, it would provide me with the weight AHT for each split he is associated with. a formula solution would be preferable, however VBA could be manageable as well.

Any help would be appreciated.

Regards,

Brian





DateLogin IDAgent NameSplitACD CallsAvg ACD TimeAvg ACW TimeHold Time
7/13/201750008Jarvis, J CaLL6180000
7/13/201750008Jarvis, J CaLL627772331113
7/13/201750012McDonald, K CaLL618264385296
7/13/201750012McDonald, K CaLL62793764685
7/13/201750031Perez, K CaLL627891711931
7/13/201750032Robicheau, N CaLL618532962339
7/13/201750032Robicheau, N CaLL6279257200
7/13/201750033Rodimon, K CaLL627672211634
7/13/201750034Collins, M CaLL6182130716159
7/13/201750034Collins, M CaLL627526111185
7/13/201750034Collins, M CaLL6290000
7/13/201750034Collins, M CaLL7017409112134
7/13/201750034Collins, M CaLL7230000
7/13/201750034Collins, M CaLL7380000
7/13/201750034Collins, M CaLL7390000
7/13/201750034Collins, M CaLL7420000
7/13/201750034Collins, M CaLL7590000
7/13/201750034Collins, M CaLL7680000
7/13/201750034Collins, M CaLL7690000
7/13/201750040Townsend, M CaLL CCS6180000
7/13/201750040Townsend, M CaLL CCS627432343262
7/13/201750049Labrecque, K CaLL6180000
7/13/201750049Labrecque, K CaLL627511972036
7/13/201750129Finnigan, A CaLL6180000
7/13/201750129Finnigan, A CaLL627573003044
7/13/201750132Stathopoulos, A CaLL618352348155
7/13/201750132Stathopoulos, A CaLL6271828310116
7/13/201750134Laroche, M CaLL400263822127
7/13/201750134Laroche, M CaLL416153641018
7/13/201750134Laroche, M CaLL6180000
7/13/201750134Laroche, M CaLL6270000

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Could you provide an example? Say, expected result for Login ID 50012 (McDonald, K CaLL) and the logic to perform the calculations

M.
 
Upvote 0
This is what I ended up using and it is working as intended. '=SUM(SUMIFS('Data Dump'!$F:$F,'Data Dump'!$C:$C,Scorecard!$C2,'Data Dump'!$D:$D,{627,618}))
 
Upvote 0
=SUM(SUMIFS('Data Dump'!$F:$F,'Data Dump'!$C:$C,Scorecard!$C2,'Data Dump'!$D:$D,{627,618}))

Would it be possible to use a date criteria with in the above formula. Something that would allow me to choose a date range to apply the calculations too?
 
Upvote 0
I think this will work, or at least it seems to be.

=IFERROR(SUMPRODUCT(--('Data Dump'!$C:$C=Scorecard!$A2),--('Data Dump'!A:A>=Scorecard!$M$3),--('Data Dump'!A:A<=Scorecard!$M$5),'Data Dump'!$F:$F,'Data Dump'!$J:$J)/SUM(SUMIFS('Data Dump'!$F:$F,'Data Dump'!$C:$C,Scorecard!$A2,'Data Dump'!$D:$D,{618,627})),"0")
 
Last edited:
Upvote 0
Hello,

I am trying to figure out how to calculate the weighted average handle time (AHT) for a selected agent, and for selected splits. So if I select Jarvis, it would provide me with the weight AHT for each split he is associated with. a formula solution would be preferable, however VBA could be manageable as well.

Any help would be appreciated.

Regards,

Brian





DateLogin IDAgent NameSplitACD CallsAvg ACD TimeAvg ACW TimeHold Time
7/13/201750008Jarvis, J CaLL6180000
7/13/201750008Jarvis, J CaLL627772331113
7/13/201750012McDonald, K CaLL618264385296
7/13/201750012McDonald, K CaLL62793764685
7/13/201750031Perez, K CaLL627891711931
7/13/201750032Robicheau, N CaLL618532962339
7/13/201750032Robicheau, N CaLL6279257200
7/13/201750033Rodimon, K CaLL627672211634
7/13/201750034Collins, M CaLL6182130716159
7/13/201750034Collins, M CaLL627526111185
7/13/201750034Collins, M CaLL6290000
7/13/201750034Collins, M CaLL7017409112134
7/13/201750034Collins, M CaLL7230000
7/13/201750034Collins, M CaLL7380000
7/13/201750034Collins, M CaLL7390000
7/13/201750034Collins, M CaLL7420000
7/13/201750034Collins, M CaLL7590000
7/13/201750034Collins, M CaLL7680000
7/13/201750034Collins, M CaLL7690000
7/13/201750040Townsend, M CaLL CCS6180000
7/13/201750040Townsend, M CaLL CCS627432343262
7/13/201750049Labrecque, K CaLL6180000
7/13/201750049Labrecque, K CaLL627511972036
7/13/201750129Finnigan, A CaLL6180000
7/13/201750129Finnigan, A CaLL627573003044
7/13/201750132Stathopoulos, A CaLL618352348155
7/13/201750132Stathopoulos, A CaLL6271828310116
7/13/201750134Laroche, M CaLL400263822127
7/13/201750134Laroche, M CaLL416153641018
7/13/201750134Laroche, M CaLL6180000
7/13/201750134Laroche, M CaLL6270000

<tbody>
</tbody>

So things are not working out as I expected. What I am looking to do is the following. I want to find the weighted average AHT for an employees in between selected dates(user selected), and for splits related to a particular projected, so 617 and 628 would be project 1 and 400,416,627 would be project 2. There is going to be a dash board that will have all of the employees listed and this is where I would like the data to report out to. I would prefer a formula solution, however I am open to VBA as well.

Would be grateful for any help provided.

Thanks
Brian
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top