Average of time range

**DexterG** · 12-10-2012, 07:13 AM

I have a column G1:G6874 containing time values such as 00:00:01, 00:00:10, 00:00:07, 00:01:05, etc. I want to find the average of time greater than (and not equal to) 00:00:00 and less than (and equal to) 00:00:05.

Any help on that?

Thanks

**Ace_XL** · 12-10-2012, 07:27 AM

Try..

=SUMPRODUCT((G1:G6874>0)*(G1:G6874<=5/86400),G1:G6874)/SUMPRODUCT((G1:G6874>0)*(G1:G6874<=5/86400))

or for 2007 and later versions

=AVERAGEIFS(G1:G6874,G1:G6874,">"&0,G1:G6874,"<="&5/86400)

**DexterG** · 12-10-2012, 08:20 AM

Hi Ace_XL,

That works. I need to do this across different time ranges, so I can modify the seconds and divide them by 86400.

But can formatting the cell (or tweaking the formula) help to directly use seconds such as 00:00:05 or 00:01:00? Just asking for information, might come handy sometime in future!

Thanks a ton!!!