Forum Discussion

John T's avatar
John T
Copper Contributor
Jul 23, 2018

Formula understanding

Hi,

 

I need help understanding the following formula :

=(SUMIFS('Agent Vol'!G:G,'Agent Vol'!A:A,"="&W4,'Agent Vol'!E:E,"=ACD"))/B4

 

I need to understand how the avg.talk time is calculated from the the sheets shown below

 

Formulas & Functions
Need Help

3 Replies

  • pranav trikha's avatar
    pranav trikha
    Brass Contributor
    Aug 20, 2018

    Greetings!

    Have tried with data inputs in the attached sheet, converted the time into minutes, divided it by number of answered calls i.e. 98 the average talk time comes to 08:81 .

    May pls see.

    Thanks,

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Jul 24, 2018
    Hi John,
    The formula is summing Column G (Total Talk) when 2 conditions are met
    1. Column A = Agent 1 Agent 1 (this is the value in W4)
    2. Column E = ACD

    When these 2 conditions are met the corresponding values in column G for those rows are added up

    At the end the total is divided by B4 (Answers)
    • John T's avatar
      John T
      Copper Contributor
      Jul 24, 2018

      Hi Wyn,

      Just to clarify :

      Column G with non-ACD removed...
      ACD        3:34:49
      ACD        2:22:28
      ACD        2:32:50
      ACD        2:21:16
      ACD        3:26:38
      which becomes 21:05:07 / 98 (b4) = 12:55 - this is what i came up with

      The sheet shows and avg talk time of 8:45...

       

      Any ideas ?