Average Handle Time for Selected agents.

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Average Handle Time for Selected agents.

  1. #1
    Board Regular jackfox68's Avatar
    Join Date
    Jan 2010
    Location
    New Hampshire
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Average Handle Time for Selected agents.

     
    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





    Date Login ID Agent Name Split ACD Calls Avg ACD Time Avg ACW Time Hold Time
    7/13/2017 50008 Jarvis, J CaLL 618 0 0 0 0
    7/13/2017 50008 Jarvis, J CaLL 627 77 233 11 13
    7/13/2017 50012 McDonald, K CaLL 618 26 438 52 96
    7/13/2017 50012 McDonald, K CaLL 627 9 376 46 85
    7/13/2017 50031 Perez, K CaLL 627 89 171 19 31
    7/13/2017 50032 Robicheau, N CaLL 618 53 296 23 39
    7/13/2017 50032 Robicheau, N CaLL 627 9 257 20 0
    7/13/2017 50033 Rodimon, K CaLL 627 67 221 16 34
    7/13/2017 50034 Collins, M CaLL 618 21 307 16 159
    7/13/2017 50034 Collins, M CaLL 627 5 261 11 185
    7/13/2017 50034 Collins, M CaLL 629 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 701 7 409 112 134
    7/13/2017 50034 Collins, M CaLL 723 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 738 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 739 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 742 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 759 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 768 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 769 0 0 0 0
    7/13/2017 50040 Townsend, M CaLL CCS 618 0 0 0 0
    7/13/2017 50040 Townsend, M CaLL CCS 627 43 234 32 62
    7/13/2017 50049 Labrecque, K CaLL 618 0 0 0 0
    7/13/2017 50049 Labrecque, K CaLL 627 51 197 20 36
    7/13/2017 50129 Finnigan, A CaLL 618 0 0 0 0
    7/13/2017 50129 Finnigan, A CaLL 627 57 300 30 44
    7/13/2017 50132 Stathopoulos, A CaLL 618 35 234 8 155
    7/13/2017 50132 Stathopoulos, A CaLL 627 18 283 10 116
    7/13/2017 50134 Laroche, M CaLL 400 26 382 21 27
    7/13/2017 50134 Laroche, M CaLL 416 15 364 10 18
    7/13/2017 50134 Laroche, M CaLL 618 0 0 0 0
    7/13/2017 50134 Laroche, M CaLL 627 0 0 0 0

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    13,405
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Average Handle Time for Selected agents.

    Could you provide an example? Say, expected result for Login ID 50012 (McDonald, K CaLL) and the logic to perform the calculations

    M.

  3. #3
    Board Regular jackfox68's Avatar
    Join Date
    Jan 2010
    Location
    New Hampshire
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average Handle Time for Selected agents.

    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}))

  4. #4
    Board Regular jackfox68's Avatar
    Join Date
    Jan 2010
    Location
    New Hampshire
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average Handle Time for Selected agents.

    Quote Originally Posted by jackfox68 View Post
    =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?

  5. #5
    Board Regular jackfox68's Avatar
    Join Date
    Jan 2010
    Location
    New Hampshire
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average Handle Time for Selected agents.

    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 by jackfox68; Jul 17th, 2017 at 09:34 AM.

  6. #6
    Board Regular jackfox68's Avatar
    Join Date
    Jan 2010
    Location
    New Hampshire
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average Handle Time for Selected agents.

    Quote Originally Posted by jackfox68 View Post
    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





    Date Login ID Agent Name Split ACD Calls Avg ACD Time Avg ACW Time Hold Time
    7/13/2017 50008 Jarvis, J CaLL 618 0 0 0 0
    7/13/2017 50008 Jarvis, J CaLL 627 77 233 11 13
    7/13/2017 50012 McDonald, K CaLL 618 26 438 52 96
    7/13/2017 50012 McDonald, K CaLL 627 9 376 46 85
    7/13/2017 50031 Perez, K CaLL 627 89 171 19 31
    7/13/2017 50032 Robicheau, N CaLL 618 53 296 23 39
    7/13/2017 50032 Robicheau, N CaLL 627 9 257 20 0
    7/13/2017 50033 Rodimon, K CaLL 627 67 221 16 34
    7/13/2017 50034 Collins, M CaLL 618 21 307 16 159
    7/13/2017 50034 Collins, M CaLL 627 5 261 11 185
    7/13/2017 50034 Collins, M CaLL 629 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 701 7 409 112 134
    7/13/2017 50034 Collins, M CaLL 723 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 738 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 739 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 742 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 759 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 768 0 0 0 0
    7/13/2017 50034 Collins, M CaLL 769 0 0 0 0
    7/13/2017 50040 Townsend, M CaLL CCS 618 0 0 0 0
    7/13/2017 50040 Townsend, M CaLL CCS 627 43 234 32 62
    7/13/2017 50049 Labrecque, K CaLL 618 0 0 0 0
    7/13/2017 50049 Labrecque, K CaLL 627 51 197 20 36
    7/13/2017 50129 Finnigan, A CaLL 618 0 0 0 0
    7/13/2017 50129 Finnigan, A CaLL 627 57 300 30 44
    7/13/2017 50132 Stathopoulos, A CaLL 618 35 234 8 155
    7/13/2017 50132 Stathopoulos, A CaLL 627 18 283 10 116
    7/13/2017 50134 Laroche, M CaLL 400 26 382 21 27
    7/13/2017 50134 Laroche, M CaLL 416 15 364 10 18
    7/13/2017 50134 Laroche, M CaLL 618 0 0 0 0
    7/13/2017 50134 Laroche, M CaLL 627 0 0 0 0
    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

  7. #7
    Board Regular jackfox68's Avatar
    Join Date
    Jan 2010
    Location
    New Hampshire
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average Handle Time for Selected agents.

      
    I asked this question again, with what i think has a little more clarity, here. https://www.mrexcel.com/forum/excel-...ne-column.html

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com