Share via

Trying to calculate average call length. DIV/0 error.

Anonymous
2015-11-26T23:41:48+00:00

Hello there.

I have a bunch off call times that have been exported from Exony. I need to calculate an average call length. I'll be getting data like this frequently and need excel to be able to do this for me quickly. The data is written in the format mm:ss. Here is my data:

00:37
00:37
00:33
00:40
00:23
00:28
00:32
00:31
00:32
00:41
00:34
00:30
00:24
00:28
00:21
00:32
00:31
00:35
00:34
00:24
00:35

When I put this in excel 2013 and try to calculate the average I get a DIV/0 error.

I know that excel doesn't know this data is time. I've tried formatting the cells as mm:ss and try to get the average but the DIV/0 error still appears.

I've searched and found this little trick but it doesn't give me an average in mm:ss:

"Put the number 1 in a currently blank cell. Copy that cell. Select your text "times". Edit / Paste Special / Values / Multiply / OK. - See more at: http://msgroups.net/excel.worksheet.functions/trying-to-calculate-average-call-le/80883#sthash.mQJotquH.dpuf:

Is there a way to tell excel that my data is time and be able to give me a real average in time that makes sense? I can't figure this out!

Thanks in advance.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

16 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-11-27T21:42:40+00:00

    So I left out the most important part.   When you do the paste, you need to choose paste special.  then you should get the paste special dialog.  Click Values and Multiply.   Sorry, I got wrapped around the axle telling you about the value part so you would not change your format and screwed up the most important part of choosing multiply as well.  You have to go into the paste special dialog to choose both.  Once you do that, then you should have actual values.  What you are seeing now is a 1 pasted in each cell and your formatting sees that as one day and displays zero as time.  Hopefully you did this on a copy of your sheet as I suggested so your data was not messed up. Here is a picture just to illustrate how your time should be stored and displayed (just some additional info):

    ![](http://fud.community.services.support.microsoft.com/Fud/FileDownloadHandler.ashx?fid=cf058453-2946-430d-8c0b-ce6106d20300)

    --

    Regards,

    Tom Ogilvy

    Was this answer helpful?

    7 people found this answer helpful.
    0 comments
  2. Anonymous
    2015-11-27T20:37:12+00:00

    Unless you formatted your column to be left justified, the fact that your picture shows them left justified would be an indication that your time values are actually stored as text strings and that is why you are having a problem - formats don't make a difference and average ignores all text values so it would return a #Div/0

    [this has been corrected since originally posted:]

    since you have formatted the cells as time, then in any empty cell, put in a number 1 and hit enter.  Copy that cell (with the number 1).  then select all your cells that appear to be time values and Click the paste dropdown arrow on the home tab.  Choose Paste Special.  In the resulting dialog choose values  and Multiply.  Click OK.   This should convert your text values to actual numbers/time values and the math should start to work.

    Test it on a copy of your worksheet so it doesn't mess of your data if this is not the problem.

    --

    Regards,

    Tom Ogilvy

    Was this answer helpful?

    6 people found this answer helpful.
    0 comments
  3. Anonymous
    2016-03-30T15:07:44+00:00

    I had this exact same issue and stumbled across this forum in attempt to fix my own. 

    I found that if you copy the values from Excel 2013 and paste them into Word 2013 (Keep Text Only). Then copy the values from Word 2013, back into Excel 2013 using (Match Destination Format), the =Average(Range) Formula works fine using whatever format you like, Number, Time, Date, whichever. 

    Not 100% sure why this worked. The only assumption I have is that there must be a format embedded somewhere, and pasting it into Word without formatting or even using Notepad works. 

    Hope this helps :)

    Was this answer helpful?

    4 people found this answer helpful.
    0 comments
  4. Anonymous
    2015-11-27T18:32:25+00:00

    Hi, it works for me, as you see give 0:31, please check the cell format

    0:37 0:31
    0:37
    0:33
    0:40
    0:23
    0:28
    0:32
    0:31
    0:32
    0:41
    0:34
    0:30
    0:24
    0:28
    0:21
    0:32
    0:31
    0:35
    0:34
    0:24
    0:35

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  5. Anonymous
    2015-11-27T15:24:25+00:00

    Here you go.

    Thanks everyone for helping me out with this.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments