I have a column of data that is in the following format:

2:37:56 (2 hours:37 minutes:56 seconds)

How can I have the summary cell display an average days/hours/minutes for
the entire column? Thanks.

Times are stored a numbers (1 hour = 1/24 day. 0.25 = 6:00:00, 0.5 =
12:00:00 , 0.75 = 18:00:00 etc), so you can SUM or AVERAGE them as you
would expect to. Format the result cell with a custom number format of
[hh]:mm:ss to prevent Excel from "rolling over" the result at
24:00:00.
E.g.,

=AVERAGE(A1:A100)
or
=SUM(A1:A100)

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Mon, 10 May 2010 12:01:01 -0700, Kila
wrote:

I have a column of data that is in the following format:

2:37:56 (2 hours:37 minutes:56 seconds)

How can I have the summary cell display an average days/hours/minutes for
the entire column? Thanks.

Thanks! I just learned something new. One more question... When I tried to
change one of the cells from 1:52:37 to 1011:52:37, or 1010101:52:37 to test
the calculation, it automatically puts AM at the end, even when I change the
format of the column to [h]:mm:ss. It only calculates after remove the AM,
but even then the calculated number seems a little low. Thanks!

"Chip Pearson" wrote:


Times are stored a numbers (1 hour = 1/24 day. 0.25 = 6:00:00, 0.5 =
12:00:00 , 0.75 = 18:00:00 etc), so you can SUM or AVERAGE them as you
would expect to. Format the result cell with a custom number format of
[hh]:mm:ss to prevent Excel from "rolling over" the result at
24:00:00.
E.g.,

=AVERAGE(A1:A100)
or
=SUM(A1:A100)

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Mon, 10 May 2010 12:01:01 -0700, Kila
wrote:

I have a column of data that is in the following format:

2:37:56 (2 hours:37 minutes:56 seconds)

How can I have the summary cell display an average days/hours/minutes for
the entire column? Thanks.
.