Averaging Time Duration

Is there a way to average time durations when formatted like this? (HH:MM:SS) Again, these are durations - NOT times of day.

06:13:52
06:35:29
06:36:13

Try this:

=AVERAGE(A1:A3)

Format the cell as [h]:mm:ss

Hope this helps.

Pete

Hi Pete,

It didn't work. I formatted the cells, but I'm still getting the #DIV/0! error.

Those are probably text values, then, that just look like times. Select all the values, click on Data | Text-to-Columns then on Finish, and that should turn them all into numeric values, and the AVERAGE function will work properly.

Hope this helps.

Pete

I'm not quite following on that Pete. Once I Text-to-Columns and average them, I'd have three separate columns that I'd have to put back together. I don't want to text to columns, then concatenate if I don't have to.

From your first post, it looked like you had 3 cells of data, each containing one time. From Post #5, however, it would appear that is not the case.

Please attach a sample Excel workbook by following the guidelines in the yellow banner at the top of the screen, then we can see exactly how your data is laid out.

Pete

Try, =AVERAGE(TIMEVALUE(A1:A3)). If this formula works, they are definitely texts.

I've attached a spreadsheet with time duration examples I frequently need to average out. I tried Joseph's formula, and it seemed to work, though it turned the duration into an AM/PM time.

There are a lot of times in that sheet, many of them showing fractions of a second - i.e. all columns except for column M (Interacting).

Is that the column that you want to average? Those time values look like text values, as I pointed out in Post #4. Are those values calculated from the other columns, and if so can you please tell us what formula is used?

Pete

Hi Pete,

This data is pulled from a program I use at work, and the times are not calculated from other columns. I use text-to-columns to remove the milliseconds before I do the manual averaging out, and I did that on Column M to play around with the formulas I was getting here, but the data downloads with the milliseconds.

Every one of the times in column M has a preceding space before the numbers, which converts the value into text. One easy way to get rid of those spaces and convert the values to proper times is to select column M and then use Find&Replace (Ctrl-H) to find space and replace it with nothing. This formula:

=AVERAGE(M9:M45)

would then return 05:08:53 as blanks are not included in the AVERAGE function.

Hope this helps.

Pete

So I did the find and replace, then used the average formula, but when I double checked the math manually, there was a math error on the seconds.

7:16:16
0:34:56
0:34:17
7:12:42
6:16:09
4:22:52 <--- Average (math says 52 should actually be 28).

Please let me know if I'm still doing something incorrect.

Originally Posted by raemaurer21

... when I double checked the math manually, there was a math error on the seconds....

I can see how you arrived at your answer of 4:22:28 - by separating the individual hours, minutes and seconds and taking an average of those, like this:

7 16 16
0 34 56
0 34 17
7 12 42
6 16 9

20 112 140 <--- individual totals

4 22 28 <===divide by 5

What this disguises, though, is that the average of minutes is actually 22.4, and the 0.4 minutes (or 24 seconds) needs to be added on to the 28 seconds shown to give 52 seconds.

Hope this explains things more clearly.

Pete