Average :mm:ss values?

[charlesstricklin]

My managers send out a weekly table of statistics for the call center, and there are two columns, average talk time and average handle time, that are expressed as :mm:ss as such:

:09:10
:09:33
:08:20
:07:36
:08:25
:08:56
​

I have row #2 to average each column's values (the correct value I'm looking for in the above example is :5:25) yet, if I use the formula =AVERAGE(A3:A1000) I get a result of #DIV/0!, and if I use the formula =TEXT(SUMPRODUCT(--(H3:H1000))/COUNTA(H3:H1000),"[hh]:mm:ss") I get the result #VALUE!

What is the correct formula to result in :5:25 ?

 

[Aladin Akyurek]

How do you get :5:25 from the set you posted?

 

[charlesstricklin]

My bad, try 8:20. I was looking at a different column.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=AVERAGE(IF(1-(A2:A7=""),("0"&A2:A7)+0))

 

[charlesstricklin]

Thanks! Why the CONTROL+SHIFT+ENTER?

 

[bschwartz]

I'm still confused. Isn't the average of those numbers 8:40?

By the way, is there a reason your times are formatted with colons before and in the middle? Are those hours and minutes, minutes and seconds, or something else?

 

[Weazel]

I'm pretty sure its minutes and seconds Ben.

the leading : could be just the format the data is exported in, which, unfortunately makes the values text.

I get 8:40 as a straight average also, the only thing I can think of is how the AHT and ATT is calculated since there are multiple variables that need to be taken into consideration for each, as well as what variables need to be considered, like hold time, acw time, straight aux time, outbound time.

trying to duplicate what some call monitoring systems calculate in excel never seems to work 100% because those specialized systems are programmed to know how to handle each variable and then how to apply it.

depending on how the center is set up, they may be applying all call time to the originating interval, so if the call runs into the next interval all time is applied to the previous interval, other centers may apply whatever occurred in each interval to that interval regardless of when it originated. consider this several hundred times every 900, 1800, or 3600 seconds for a few hundred calls and it can become quite overwhelming and very painful to track in excel

Most of the time, at least for AHT you can generically apply something like sumproduct(actual calls,actual AHT)/total calls which will get you in the ball park within a few seconds but its almost never spot on which makes exporting and averaging inherently inaccurate

 

[Aladin Akyurek]

Thanks! Why the CONTROL+SHIFT+ENTER?

The formula has to process array objects, hence control+shift+enter in order to signal that fact to Excel. Note that here ISNUMBER evaluates to an array (a set with multiple values) and so do & which glues a 0 in front of each item and +0 which converts each modified item into a number (true time value).