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

Thread: Formula for calculating average time

  1. #1
    New Member
    Join Date
    Sep 2010
    Location
    San Diego, CA
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula for calculating average time

    I am trying to calculate the average Talk Time into Minutes and Seconds. Every time I have tried, I get the wrong average.

    For example, if I have a total of 53 calls and the total talk time is 4:19:05, what would the average talk per call be? I tried doing everything in seconds (this would give me a total of 15545 seconds) but the total I was getting is 4:89.

    What would the formula be to get the correct average time?

  2. #2
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for calculating average time

    Welcome to the Board!

    Maybe try this:

    =TEXT(average(A1:a53),"[m]:ss")

    or without the text formula use:

    =average(A1:A53)

    and then format the cell to [m]:ss

    Hope that helps.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  3. #3
    New Member
    Join Date
    Sep 2010
    Location
    San Diego, CA
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Formula for calculating average time

    Perfect, thank you for the help. It worked

  4. #4
    New Member
    Join Date
    Oct 2012
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for calculating average time

    I've tried the text formula however the result returned is indicated below. What am I doing incorrectly
    #DIV/0!

  5. #5
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for calculating average time

    Are you using the correct range for the formula? That is telling me that all of your values are zero or maybe you have numbers that are formatted as text?

    What happens if you do this:

    =average(a1:a5+0)

    Confirm this with control+shift+enter. Change the range to the applicable range.

    Hope that helps.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  6. #6
    New Member
    Join Date
    Oct 2012
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for calculating average time

    Thanks for coming back to me Schielrn. I managed to get it right

Some videos you may like

User Tag List

Tags for this Thread

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
  •