How do I summarize unusual column data?

K

Kila

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

Kila

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!
 
C

Chip Pearson

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]
 
K

Kila

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.


מיכ×ל (מיקי) ×בידן said:
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 said:
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!
 
K

Kila

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 said:
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]




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

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

Top