Average hhhh:mm:ss

[jerrell1968]

I have a spreadsheet with times in it (hhhh:mm:ss format). I need to average the times, and can't find a formula to work. It might be because the cells are general formatted as this is how it comes out of the database that the information is extracted from. Is there a quick formula that would help me calculate the average time? I can attach a sample file if this would be useful.....

[jerrell1968]

TIME.xlsxHere is the attachment if needed.... thanks in advance.

[popipipo]

You can't get a average because the cells contain text and not time values
You cannot use hhhh:mm:ss as a time format

[martindwilson]

=sumproduct(--(right(a2:a25,8)))/24

[Pepe Le Mokko]

In fact you do not have real times but text looking like time. Try

Please Login or Register  to view this content.

. The first part transforms each cell into real time and adds them all up. Then it is divided by the number of non-empty cells.

[jerrell1968]

I understand that it is in text format instead of numerical, and that definitely throws a wrench in the gears. Maybe there is a formula that I could put into Column B that would convert the text format in Column A to numerical in Column B? I know that I could just type the seconds in numerical format, but that would be very time intensive as I have over 1,000 rows of data. Plus, the number of rows will periodically change. Maybe there isn't a way to do what I want to do, but it never hurts to ask.

[Pepe Le Mokko]

Have you tried solutions provided in thread #4 and 5 ? They both do the job as far as I can tell

[jerrell1968]

Yes, I made a mistake in my calculations. Thanks everyone.

[martindwilson]

you could make it a bit more dynamic i suppose with hhhh:mm:ss in a1 then those time stamps in a2 down
=SUMPRODUCT(--(RIGHT(A2:INDEX(A:A,COUNTA(A:A)),8)))/(COUNTA(A:A)-1)

[jerrell1968]

Thanks for all of the great suggestions. Though I have marked this as solved, I do have one last question..... I've noticed that the average changes when I change the 8 in the above formula to a lower number..... the numbers in my case will never exceed 1:23 places.... when I have only data in the seconds place (000:00:ss), I can change the 8 to a 2, and the calculations are dead on... however, if I change the 8 to a 4, the average is calculated as a lower number...... Why does this happen? I know 3 vs. 8 would mean the colon ( place, so I figure that I would be safe to set the 8 to 4, but it throws the calculations off. Any ideas?

[martindwilson]

you need 8 so that hh:mm:ss is returned
00:00:34 ie 34 seconds
if you use 4 it assumes that its h:mm
0:34 is 0 hrs 34 mins
both these need cell formatted as time
or multiply result by 86400 to get result in seconds cell format general
2 would just produce numbers which you average cell format general
btw your actual result for those posted isnt 00:00:42 its actually 00:00:41.5
if you multiply
0.000480324*86400 = 41.5