Thread: Formula for calculating average time Thanks: 0 Likes: 0

1. 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?  Reply With Quote

2. 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.  Reply With Quote

3. Re: Formula for calculating average time

Perfect, thank you for the help. It worked   Reply With Quote

4. 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!  Reply With Quote

5. 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.  Reply With Quote

6. Re: Formula for calculating average time

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

User Tag List

average time, minutes and seconds 