Need 'criteria' within AVERAGEIFS formula to automatically increase when dragging down

Hi,

Using AVERAGEIFS formula I am trying to find an average heart rate for 10 second chunks. The HR raw data is sporadic and therefore I do not have 10 data points per 10 seconds consistently. This is the formula as it stands;

=AVERAGEIFS($B$3:$B$14580,$A$3:$A$14580,">00:00:01",$A$3:$A$14580,"<00:00:10")

This provides me with the average HR for the first 10 sec.

I know need to copy this formula with the 'criteria' section of the formula (">00:00:01" and "<00:00:10") increasing by 10 seconds each time, therefore the next formula would read;

=AVERAGEIFS($B$3:$B$14580,$A$3:$A$14580,">00:00:11",$A$3:$A$14580,"<00:00:20")

I have 24 files each of approximately 5-6 hours therefore cannot manually completely this!!

Is it possible to setup the formula to increase by 10 seconds each time?

I have attached the spreadsheet should it be of assistance.

Many thanks in advance!

=AVERAGEIFS($B$3:$B$14580,$A$3:$A$14580,">00:00:01",$A$3:$A$14580,"<00:00:10")
=AVERAGEIFS($B$3:$B$14580,$A$3:$A$14580,">00:00:11",$A$3:$A$14580,"<00:00:20")

before we go on what happens between 00:00:10 and 00:00:11 those formulas discount both of those

Try in first cell

=AVERAGEIFS($B$3:$B$14580,$A$3:$A$14580,">="&((ROW(A1)-1)*10+1)/24/60/60,$A$3:$A$14580,"<="&((ROW(A1)-1)*10+10)/24/60/60)

Copy down

Hello Javboy ! Welcome to the forum try this in d4

=AVERAGEIFS($B$3:$B$14580,$A$3:$A$14580,">00:00:"&(((ROW(A1)-ROW($A$1)))*10)+1,$A$3:$A$14580,"<00:00:"&(((ROW(A2)-ROW($A$1)))*10))
and drag down

did no one bother to wonder why the rages are not inclusive

Originally Posted by estige

All these give the same result as far as I can see:
=AVERAGEIFS($B$3:$B$14580,$A$3:$A$14580,">00:00:"&E4,$A$3:$A$14580,"<00:00:"&F4)
=AVERAGEIFS($B$3:$B$14580,$A$3:$A$14580,">=00:00:"&E4,$A$3:$A$14580,"<=00:00:"&F4)
=AVERAGEIFS($B$3:$B$14580,$A$3:$A$14580,">="&"00:00:"&E4,$A$3:$A$14580,"<="&00:00:"&F4)

I think you do get different results for some of those formulas (101 to 110 on row 14 for example).....but there is a problem because actually none of them are giving correct results in all cases because some of the rows aren't included by any of those, e.g. between 11 and 20, assuming that should be inclusive (because the previous range ends at 10 and the next starts at 21) the result should be the same as

=AVERAGE(B8:B15) = 118.125

....but all of those formulas give the result as 117

That's because of rounding errors, so even using >= and <= here doesn't entirely work. You need a formula that will round to the nearest second so that no rows are excluded. You can do that with this "array" formula

=AVERAGE(IF(TEXT(A$3:A$14580,"[s]")+0>=E4,IF(TEXT(A$3:A$14580,"[s]")+0<=F4,B$3:B$14580)))

confirmed woth CTRL+SHIFT+ENTER

....or with ROUND like this

=AVERAGE(IF(ROUND(A$3:A$14580*86400,0)>=E4,IF(ROUND(A$3:A$14580*86400,0)<=F4,B$3:B$14580)))

If you want to stay with a non-array AVERAGEIFS use a helper column, e.g. in C3 use this formula copied down

=ROUND(A3*86400,0)

and then you can use AVERAGEIFS like this

=AVERAGEIFS(B:B,C:C,">="&E4,C:C,"<="&F4)

.....On top of that, none of those formulas handle 10000+ seconds (see row 1003 onwards) whereas my suggestions have no problem with that.

To check that all rows are covered change that last AVERAGEIFS to COUNTIFS, i.e.

=COUNTIFS(C:C,">="&E4,C:C,"<="&F4)

copy that down and sum the results and you get 14578, i.e. the number of rows of data - that proves that all the rows are being included in the COUNTIFS (and by extension the AVERAGEIFS)