Trying to calculate average call length

[pilates_jocelyn]

Usually our phone system spits out a report for me, but it's broken right
now... and I have to provide this info to another department ASAP.

I'm trying to calculate the average call length for our call center agents.
My spreadsheet looks like this:

Call Duration
00:04:39
00:00:58
00:03:18
00:04:02

The cells are custom formatted as hh:mm:ss. All I want to know is the
average length for those four calls. But when I try the AVERAGE function, I
get a DIV/0 error. Any ideas on what I'm doing wrong? Thanks in advance!

 

[Glenn]

Usually our phone system spits out a report for me, but it's broken right
now... and I have to provide this info to another department ASAP.

I'm trying to calculate the average call length for our call center agents.
My spreadsheet looks like this:

Call Duration
00:04:39
00:00:58
00:03:18
00:04:02

The cells are custom formatted as hh:mm:ss. All I want to know is the
average length for those four calls. But when I try the AVERAGE function, I
get a DIV/0 error. Any ideas on what I'm doing wrong? Thanks in advance!

Your data is actually text that looks like time. Put the number 1 in a
currently blank cell. Copy that cell. Select your text "times". Edit / Paste
Special / Values / Multiply / OK.

 

[Eva]

hi
AVERAGE(LEN(A:A)
--
Please click "yes" if this post helped you!

Greatly appreciated

Eva

 

[Roger Govier]

Hi

I copied your data into a sheet and applied =Average(A1:A4) and got
00:03:14 as a result

There must be something different about the data you posted compared
with what is in your main sheet.

 

[albertmonicos]

To computer the average maximum call length feature you should first
have a formula to convert them into total minutes then sum them and then
divide the total by 4.

 

[pilates_jocelyn]

Hi Roger-

Yes, you are right. I just copied what I wrote in my earlier e-mail into
Excel and averaged it and got 3 min, 14 sec call average. I then copied that
same data from my spreadsheet, pasted it alongside and did AVERAGE and got a
DIV/0 error. So, I looked at how the time is displayed in the Formula Bar.
The times I typed in appear as 12:04:39 AM; the times I copied appear at
00:04:39. I've tried formatting them at Time and Special, but I can't get it
to change. Any ideas?

Thank you!

 

[Glenn]

Go back to my previous response...

Your data is actually text that looks like time. Put the number 1 in a
currently blank cell. Copy that cell. Select your text "times". Edit / Paste
Special / Values / Multiply / OK.

 

[pilates_jocelyn]

Hmmm, I believe I figured it out. There were two spaces before the "time" in
the report. I removed those and it is working perfect now. Darn Crystal
Reports exporting! Thanks!