Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Averaging Time (Excel XP)

    I bet this has been covered countless times but I couldn't get a quick answer using search...

    <font color=blue>#1</font color=blue> - In a column (A1:A9), there is a list of times. In cell A10, I'm looking to get an AVERAGE of those times. The times are actually recorded as seconds:

    A1 = :09
    A2 = :03
    A3 = :07
    A4 = :05

    There are no entries in A5 - A9 (but there could be...). The result of the formula in A10 should be :06 (to represent an average of 6 seconds).

    <font color=blue>#2</font color=blue> - In another column (C1:C9), I have a similar situation, except that the times include minutes and seconds. The times are entered as :

    C1 = 3:45
    C2 = 2:45
    C3 = 2:50
    C4 = 3:20

    There are no entries in C5 - C9 (but again, there could be...) The result of the formula in C10 should be 3:10 (I think!).

    Any help with the correct formulas would be appreciated. I keep getting a #DIV/0! result.
    - Ricky

  2. #2
    Star Lounger
    Join Date
    Dec 2000
    Location
    Tacoma, Washington, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging Time (Excel XP)

    Here's everything you ever wanted to know about Dates and Time in Excel CPearson.com .
    Format the numbers as time. Seconds are entered 0:0:xx. See Chip's site for a macro that will allow alternate entries.
    <IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Averaging Time (Excel XP)

    Thanks for the link. Looks like I'll have to completely change the way the original entries are made. It's very inconvenient to have to enter 0:00:06 rather than :06 .

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  4. #4
    Star Lounger
    Join Date
    Dec 2000
    Location
    Tacoma, Washington, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging Time (Excel XP)

    Pearson also has an Event change macro that might help:

    Date Time Entry
    <IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Averaging Time (Excel XP)

    In addition to Doug's suggestion and link,
    you could have people enter the values into separate columns (hrs, min, sec) and then use a formula to convert the result into the number of DAYS (the unit of excel time) and then format the resulting formula to the time format desired
    <pre>=(hrs+(min + sec/60)/60)/24</pre>


    Steve

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Averaging Time (Excel XP)

    You could try this:
    <pre>Function DecimalTime(SelectedCell)
    'converts Times to Decimal Number (not serial value)
    If Application.WorksheetFunction.IsNumber(SelectedCel l) Then
    DecimalTime = Application.WorksheetFunction.Sum((Hour(SelectedCe ll)), _
    ((Minute(SelectedCell)) / 60), ((Second(SelectedCell)) / 3600))
    Else
    DecimalTime = CVErr(xlErrNum)
    End If
    End Function
    </pre>

    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging Time (Excel XP)

    That's the hard way. If A1 contains an Excel time value, then the following formula will give the decimal time:

    <pre>=A1*24
    </pre>

    Legare Coleman

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Averaging Time (Excel XP)

    In the first case, Excel won't recognize ":09" as any unit of time unless preceded by a zero; those entries are treated as text. In the second case I was able to copy them into a spreadsheet and without any editing run the average calculation; if you are getting a #DIV/0 error, then they may have been entered as text.

    FWIW, when you do enter them as time, Excel will treat them as hours & minutes, rather than minutes and seconds, but the averages still work.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Averaging Time (Excel XP)

    Hey, it worked! The cells with entries in the format, (3:15, 2:45, etc.), worked out fine once I entered the formula correctly and formatted the cell properly.

    As far as dealing with the "seconds only" entries, I opted to do away with the colon and simply enter 3 seconds as the number " 3 ". Naturally, there's no problem in using the AVERAGE function with whole numbers. And it kept data entry speeding along, even faster since we don't have to reach for the colon key. I was able to manipulate the cell A10 to show the average number of seconds in the proper format by using the CONCATENATE function...

    <font color=blue>
    <hr>=IF(AVERAGE(A1:A9)>9.99999999,CONCATENATE(":",ROUN D(AVERAGE(A1:A9),0)),CONCATENATE(":0",ROUND(AVERAG E(A1:A9),0)))<hr>
    </font color=blue>

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Thanks Everyone for the Suggestions.
    - Ricky

Posting Permissions

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