Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Jul 2007
    Location
    London, Gtr London, United Kingdom
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up Average time calculation

    Hi

    The excel sheet attached has a calculator tool for a help desk.

    You see 1st level support in London on the left & 2nd level support in New York on the right.

    There are 3 agents & they open 5 tickets each.

    Many tickets are closed by smart agents at Level 1, therefore a time of zero for some of the 2nd level tickets, e.g. Agent 1, ticket 2 and so on.

    I need to calculate:

    1. Average time per level (1st level, 2nd level)
    2. Average time per Agent
    3. Average time per Ticket

    I managed to calculate the first two but not the third.

    Please advise how to go about this one.

    TIA.

    BJ.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    I think you need to relook your calculations for the agent's average time in level 2. They include entries of zero time for resolved tickets which brings their average time falsely low. You calculated Agent 1's average time as a little over 6 minutes using 5 entries, 2 of which were zero. If you averaged just the 3 entries your result would be a hair over 10 minutes. This will also throw your calculation for level 2 average. Try this formula:
    =SUM(H6:L6)/COUNTIF(H6:L6,">0") Sum of the times divided by the number of tickets not equal to zero

    There was akso a different format for cells K10 and L10 that I difficulty clearing. Look in the formula bar when you click on those cells compared to others. It was throwing your calculations off even further.



    Not sure what you mean by an average time per ticket. There is a total time per ticket that it is opened. If the same ticket was opened and closed several times, then there can be an average open time for the ticket


    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2012-12-28 at 13:22.

Posting Permissions

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