Excel AVERAGEIF function in combination with MOD to average over every nth row not behaving as expected

Question

With Excel, I'm attempting to average every fourth row.

Given that all of the formula's components were computed correctly, the following formula should produce the desired outcome (if I do everything step by step).

=AVERAGEIF(MOD(ROW(D20:D35)-ROW(D20),4),"=0",D20:D35)    # does not work

=MOD(ROW(D20:D35)-ROW(D20),4)   # returns correct values

=AVERAGEIF(N19#,"=0",D20:D35) # N19# is the return of =MOD(ROW(D20:D35)-ROW(D20),4), and the result is the correct average, as it should be in the top function when everything is nested in one function.

I am not sure why the nested function is not working. Can anyone help?

Answer 1

Average Every Nth Row

• All three finish in under a second for 1M rows but it seems like INDEX is faster than FILTER and FILTER is faster than Ron Rosenfeld's modernized version.

=LET(Data,D20:D35,nth,4,
AVERAGE(INDEX(Data,SEQUENCE((ROWS(Data)-1)/nth+1,,,nth))))

=LET(Data,D20:D35,nth,4,
AVERAGE(FILTER(Data,MOD(SEQUENCE(ROWS(Data)),nth)=1)))

=LET(Data,D20:D35,nth,4,
AVERAGE(IFERROR(Data*1/(MOD(SEQUENCE(ROWS(Data)),nth)=1),"")))