Averaging Time (Excel XP)

Topic

New Reply

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

#1 – 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).

#2 – 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.

Reply | Quote

Replies

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.

Reply | Quote

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 .

Reply | Quote

Pearson also has an Event change macro that might help:

Date Time Entry

Reply | Quote

Pearson also has an Event change macro that might help:

Date Time Entry

Reply | Quote

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

=(hrs+(min + sec/60)/60)/24

Steve

Reply | Quote

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

=(hrs+(min + sec/60)/60)/24

Steve

Reply | Quote

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 .

Reply | Quote

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.

Reply | Quote

You could try this:

Function DecimalTime(SelectedCell)
'converts Times to Decimal Number (not serial value)
If Application.WorksheetFunction.IsNumber(SelectedCell) Then
   DecimalTime = Application.WorksheetFunction.Sum((Hour(SelectedCell)), _
      ((Minute(SelectedCell)) / 60), ((Second(SelectedCell)) / 3600))
Else
   DecimalTime = CVErr(xlErrNum)
End If
End Function

Reply | Quote

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

=A1*24

Reply | Quote

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

=A1*24

Reply | Quote

You could try this:

Function DecimalTime(SelectedCell)
'converts Times to Decimal Number (not serial value)
If Application.WorksheetFunction.IsNumber(SelectedCell) Then
   DecimalTime = Application.WorksheetFunction.Sum((Hour(SelectedCell)), _
      ((Minute(SelectedCell)) / 60), ((Second(SelectedCell)) / 3600))
Else
   DecimalTime = CVErr(xlErrNum)
End If
End Function

Reply | Quote

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.

Reply | Quote

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…

[indent]

---

=IF(AVERAGE(A1:A9)>9.99999999,CONCATENATE(“:”,ROUND(AVERAGE(A1:A9),0)),CONCATENATE(“:0”,ROUND(AVERAGE(A1:A9),0)))

---

[/indent]

Thanks Everyone for the Suggestions.

Reply | Quote

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…

[indent]

---

=IF(AVERAGE(A1:A9)>9.99999999,CONCATENATE(“:”,ROUND(AVERAGE(A1:A9),0)),CONCATENATE(“:0”,ROUND(AVERAGE(A1:A9),0)))

---

[/indent]

Thanks Everyone for the Suggestions.

Reply | Quote

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.

Reply | Quote