dynamic - Excluding rows dynamically -

dynamic - Excluding rows dynamically -

let's assume have following:

1 10 2 20 3 30 4 20 5 10 6 30 7 20 8 9 10 =(average(a1:a7) 11 4 12 6

i able find way calculate average of a1-a7 cell a10 while excluding row range defined in a11 , a12. is, according above setup result should 20:

((10 + 20 + 30 + 20) / 4) = 20

because if rows 4,5 , 6 excluded what's left rows 1,2,3,7 averaged.

two other options:

=average(filter(a1:a7,isna(match(row(a1:a7),a11:a12,0))))

=arrayformula(averageif(match(row(a1:a7),a11:a12,0),na(),a1:a7))

dynamic google-spreadsheet average rows