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!

Hi there! It sounds like you're having trouble calculating the average call length for your call center agents. Don't worry, I'm here to help!

The reason you're getting a DIV/0 error when using the AVERAGE function is because Excel is treating your call duration values as text instead of numbers. To fix this, you'll need to convert the text values to numbers that Excel can work with.

Here's how you can do it:

1. Insert a new column next to your Call Duration column.
2. In the first cell of the new column, enter the following formula:
   Formula:

   =TIMEVALUE(A1) 

   (Assuming your Call Duration column is in column A)
3. Copy the formula down to all the cells in the new column.
4. Now you should have a column of numbers that represent the call duration in seconds.
5. Use the AVERAGE function on the new column to get the average call length.

Here's an example formula you can use for step 5: (Assuming your new column is in column B)

That should do the trick!

pilates_jocelyn wrote:
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.

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

Greatly appreciated

Eva


"pilates_jocelyn" wrote:

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!

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.
--
Regards
Roger Govier

pilates_jocelyn wrote:
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!

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!

"Roger Govier" wrote:

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.
--
Regards
Roger Govier

pilates_jocelyn wrote:
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!
.

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 wrote:
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!

"Roger Govier" wrote:

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.
--
Regards
Roger Govier

pilates_jocelyn wrote:
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!
.

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!

"pilates_jocelyn" wrote:

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!

"Roger Govier" wrote:

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.
--
Regards
Roger Govier

pilates_jocelyn wrote:
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!
.