How to average hh:mm:ss when hh is 00
 

I' trying to get the average of elapsed times like 00:01:22, 00:08:23,
00:10:59. The AVERAGE function returns div/0 errors. Is there a way to do
this and keep the hh:mm:ss format?

How to average hh:mm:ss when hh is 00
 

This suggests the data is not true time values because =AVERAGE(A1:A5) works
when the range holds time values.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"the mechanic" wrote in message
...
I' trying to get the average of elapsed times like 00:01:22, 00:08:23,
00:10:59. The AVERAGE function returns div/0 errors. Is there a way to do
this and keep the hh:mm:ss format?

How to average hh:mm:ss when hh is 00
 

It only works if I allow Excel to show the time in the formula bar as
12:01:22 AM, 12:08:23 AM, 12:10:59 AM. These times are elpased time values,
not time of day. As the developer of the timesheet, I might understand, but
my users may be baffled as to why a column heading of "MAXIMUM TIME" has "AM"
and "PM" in the formula.

"Bernard Liengme" wrote:

This suggests the data is not true time values because =AVERAGE(A1:A5) works
when the range holds time values.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"the mechanic" wrote in message
...
I' trying to get the average of elapsed times like 00:01:22, 00:08:23,
00:10:59. The AVERAGE function returns div/0 errors. Is there a way to do
this and keep the hh:mm:ss format?

How to average hh:mm:ss when hh is 00
 

Assuming your textual representations of time are in column D, starting with
row 1.

Formula is:
=TEXT(SUMPRODUCT(--(D1:D100))/COUNTA(D1:D100),"[hh]:mm:ss")

--
John C


"the mechanic" wrote:

It only works if I allow Excel to show the time in the formula bar as
12:01:22 AM, 12:08:23 AM, 12:10:59 AM. These times are elpased time values,
not time of day. As the developer of the timesheet, I might understand, but
my users may be baffled as to why a column heading of "MAXIMUM TIME" has "AM"
and "PM" in the formula.

"Bernard Liengme" wrote:

This suggests the data is not true time values because =AVERAGE(A1:A5) works
when the range holds time values.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"the mechanic" wrote in message
...
I' trying to get the average of elapsed times like 00:01:22, 00:08:23,
00:10:59. The AVERAGE function returns div/0 errors. Is there a way to do
this and keep the hh:mm:ss format?

How to average hh:mm:ss when hh is 00
 

Nice work John,
thanks

"John C" wrote:

Assuming your textual representations of time are in column D, starting with
row 1.

Formula is:
=TEXT(SUMPRODUCT(--(D1:D100))/COUNTA(D1:D100),"[hh]:mm:ss")

--
John C


"the mechanic" wrote:

It only works if I allow Excel to show the time in the formula bar as
12:01:22 AM, 12:08:23 AM, 12:10:59 AM. These times are elpased time values,
not time of day. As the developer of the timesheet, I might understand, but
my users may be baffled as to why a column heading of "MAXIMUM TIME" has "AM"
and "PM" in the formula.

"Bernard Liengme" wrote:

This suggests the data is not true time values because =AVERAGE(A1:A5) works
when the range holds time values.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"the mechanic" wrote in message
...
I' trying to get the average of elapsed times like 00:01:22, 00:08:23,
00:10:59. The AVERAGE function returns div/0 errors. Is there a way to do
this and keep the hh:mm:ss format?