Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Aug 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Average Elapsed Time

    I have a long column of elapsed times (time spent on the phone answering a call). They are formatted for the custom format h:mm:ss, like this:

    0:06:26
    0:06:44
    0:06:10

    I am trying to average them, using the average formula, but I keep getting this: #DIV/0! I have googled the question, but have had no luck in finding an answer that works for me.

    I"m not exactly an expert at this, so I know I must be missing something. Can someone help me?????

    Nanci

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Nanci,

    Welcome to the Lounge as a new poster.

    Don't know what you are doing but it worked fine for me.
    nancy.JPG
    I just typed the times in no special formatting.

    Test file: Nancy.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Aug 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't know what is going on either. If I copy and paste the numbers to another page and try it, I still have the same problem. If I manually type in all of the numbers on a new page, it works fine. I guess the formatting from the original source was screwy and excel isn't able to unscrew it?

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Nanci,

    Could you post the source for us to take a look at?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Aug 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm not sure what to post. the true original source is generated in something other than excel, but I get a report e-mailed to me each day that it automatically converts into excel (Report 1). I then copy the report and do a special paste (values only) to a new page so I can get rid of all of the strange columns and headings (Report 2). I then set up a spreadsheet for the month that lists the totals for each day, which I copy and paste from each report (Report 3). So the column of numbers that I am trying to average was actually set up by me. In Report 1, all numbers are formatted as text. When I copy and paste to Report 2, the numbers are formatted as general. Whether I keep it formatted as general, or change the formatting to time in Report 3, it won't average them for me. It seems to still think that the numbers are text, no matter what I do.

    Nanci

  6. #6
    New Lounger
    Join Date
    Sep 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Nanci,

    Yes, the problem is that your numbers aren't formatted correctly. First of all, the column of times is probably formatted as text, as you mentioned. My fix for that is kind of awkward, but works:

    Do this on a backup of your spreadsheet, just in case, as we will be overwriting your list of times!
    Enter a 1 in each cell of a blank column (on that sheet or another) for the same number of cells as your times list. Go back to your column of times and select all of the times, then use Paste-Special, Values, and click the selection to Multiply. This will turn all of those text formatted times into numbers, but they won't look right, just yet (be patient!).
    Now select that new list we just made, hit Copy, and overwrite your list of elapsed times (just regular Copy, not special).
    You will notice that it is not in the right format, so select that new list and right click, select Format Cells... and down at the bottom click Custom. Scroll down the list and you'll find h:mm:ss (and select that). Now your list of times will be in the correct format, and not as text.

    Secondly, where you have typed =AVERAGE(A1:A4) --of course, your range will be different-- you will want to make sure that cell is formatted Custom, h:mm:ss also. If it refuses to say something other than 0 or DIV/0 or whatever, just do the copy special-multiply (by 1) and copy back trick.

    Hope that helps! ---Gordon Robbins

  7. #7
    New Lounger
    Join Date
    Aug 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm beginning to wonder if I really need to see those averages.

    I tried your suggestion (thanks for the suggestion), and it did work, I was able to come up with an average! However, it now is treating the elapsed times in the list as actual times. While the cell says 0:07:26, the value in the formula box says 12:07:26 AM, despite the fact that the formatting is the custom formatting, and not the formatting under time.

    time excel.JPG

    I don't think it matters in this case, but it is troubling. And annoying.

    N

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •