Average Time Query

[rsm42]

Hi,

Within Excel 2007, I need to calculate the average of the following time values in days, hours, minutes and seconds :

00:18:09:26
00:05:34:50
00:07:41:06
01:22:52:55
00:00:55:48

I've tried using =AVERAGE(A1:A5) and changing the format of the values to various settings, but keep getting DIV/O as the result.

Help ????

[daddylonglegs]

Those aren't valid time values in Excel so you get an error because Excel treats them as text. Two possible approaches

With an array formula

=AVERAGE(LEFT(A1:A5,2)+RIGHT(A1:A5,8))

confirmed with CTRL+SHIFT+ENTER

or a "regular" formula

=SUMPRODUCT(LEFT(A1:A5,2)+RIGHT(A1:A5,8))/COUNTA(A1:A5)

In both cases I assume all cells have data (no blanks). Format result cell as [h]:mm:ss

[rsm42]

Those aren't valid time values in Excel so you get an error because Excel treats them as text. Two possible approaches

With an array formula

=AVERAGE(LEFT(A1:A5,2)+RIGHT(A1:A5,8))

confirmed with CTRL+SHIFT+ENTER

or a "regular" formula

=SUMPRODUCT(LEFT(A1:A5,2)+RIGHT(A1:A5,8))/COUNTA(A1:A5)

In both cases I assume all cells have data (no blanks). Format result cell as [h]:mm:ss

Great the second formula worked a treat - many thanks