How do I summarize unusual column data?
 

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.

How do I summarize unusual column data?
 

Thank you so much for your quick reply! Actually, I needed average
Hours/Minutes/Seconds
I think the calculation is close, but it gave me

930202 day(s) & 12:56

Can you help one more time? Thanks so much!

"מיכאל (מיקי) אבידן" wrote:

HTH:
=TEXT(INT(AVERAGE(A:A)),"#")&" day(s) & "&TEXT(MOD(AVERAGE(A:A),1),"hh:mm")
Micky


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

How do I summarize unusual column data?
 

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.

How do I summarize unusual column data?
 

Thanks! That worked. Here is a related quick question though...
My sample, 2:37:56 is a total number of hours, not a time. It could just
have easily have been 2358:37:56

Is this calculating based on hh:mm:ss being a total or a time? Thanks.


"מיכאל (מיקי) אבידן" wrote:

This is much, much, simpler.
* For col. "A" data, - in cell B1 type: =AVERAGE(A:A)
* Custom format cell B1 as [h]:mm:ss
Micky


"Kila" wrote:

Thank you so much for your quick reply! Actually, I needed average
Hours/Minutes/Seconds
I think the calculation is close, but it gave me

930202 day(s) & 12:56

Can you help one more time? Thanks so much!

"מיכאל (מיקי) אבידן" wrote:

HTH:
=TEXT(INT(AVERAGE(A:A)),"#")&" day(s) & "&TEXT(MOD(AVERAGE(A:A),1),"hh:mm")
Micky


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

How do I summarize unusual column data?
 

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