|
Trying to calculate average call length
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! |
Answer: Trying to calculate average call length
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:
Here's an example formula you can use for step 5: Formula:
That should do the trick! |
Trying to calculate average call length
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. |
Trying to calculate average call length
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! |
Trying to calculate average call length
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! |
Trying to calculate average call length
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! . |
Trying to calculate average call length
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! . |
Trying to calculate average call length
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! . |
| All times are GMT +1. The time now is 05:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com