TL;DR
For sliding window size N=7
, with data starting in H3
,
=if(and(len(H3)>0,count(H$3:H3)>=7),average(indirect("H" & large(filter(row(H$3:H3), len(H$3:H3)), 7) & ":H" & row (H3))),"")
Explanation
Here it is repeated with indentation, for reference in the following explanation.
if
(
and
(
len(H3)>0,
count(H$3:H3)>=7
),
average
(
indirect
(
"H" &
large(filter(row(H$3:H3), len(H$3:H3)), 7) &
":H" &
row (H3)
)
),
""
)
- The
$
fixes rows, so that as you fill down, H$3:H3
ranges become H$3:H4
, H$3:H5
, ... H$3:H9
, H$3:H10
, etc.
filter(row(...), len(...))
returns an array that consists of the row numbers of the nonempty entries in the given range.
large(..., 7)
picks the 7th largest number from this array: this is the row number where you want to start averaging.
indirect("H" & ... & ":H" ...)
forms the range for averaging, e.g., H9:H15
if the output of preceding computation was 15.
- Unlike the linked answer, we append the
row
so that the range ends at the current row (for the sliding window), instead of going all the way to the bottom.
- Finally,
average
does the average. You could put other aggregate functions here, too.
- We replace the linked answer's
iferror()
with the surrounding if(and(len(H3)>0,count(H$3:H3)>=7), ..., "")
. This makes it only generate an output value if the cell has a value (len()>0
), and if there have been at least 7 non-empty data points (count(...)>=7
).
- Using
len()>0
instead of isblank()
makes it exclude cells with formulas that generate no output.
- Alternatively, omit the
and()
and count()
, and just don't paste the formula into the first N-1
cells in the output column. Then you'd have
=if(len(H3)>0,average(indirect("H" & large(filter(row(H$3:H3), len(H$3:H3)), 7) & ":H" & row (H3))),"")
(Or use that simplified formula, and begin output at the first input row, knowing that the first N-1
values are computed over the smaller ranges.)
Fill Over
If you copy the formula to a horizontally-adjacent column, and want it to look at input data from an also horizontally-adjacent column (I
), you'll have to replace the literal (quoted) H
s with I
s.
Alternatively, you can complicate the formula a bit to automate this, using a formula such as this or this. Then you'd have
=if(and(len(H3)>0,count(H$3:H3)>=7),average(indirect(regexextract(address(1, column(H$1)), "[A-Z]+") & large(filter(row(H$3:H3), len(H$3:H3)), 7) & ":" & regexextract(address(1, column(H$1)), "[A-Z]+") & row (H3))),"")