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

T

the mechanic

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?
 
B

Bernard Liengme

This suggests the data is not true time values because =AVERAGE(A1:A5) works
when the range holds time values.
best wishes
 
T

the mechanic

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.
 
J

John C

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")
 
T

the mechanic

Nice work John,
thanks

John C said:
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 said:
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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert HH:MM AM to HH:00 AM 1
Average HH:MM:SS 1
Shortcut for current Time in hh:mm:ss 2
average if with time and date 0
calc time value 3
cells with hours, minutes, and seconds 4
Average HH:MM:SS 1
Trying to calculate average call length 6

Top