# Sliding window average of last N non-empty non-blank cells in Google Sheets

Similar to this question:

Average of last 7 non-empty non-blank cells in Google Sheets

I'd like a formula to compute a sliding window average of the last `N` non-blank cells in a column. e.g. For `N = 7`, with the data in column `H`, beginning in row 3: Cells from the input column with no data do not generate a corresponding value in the output column, and they are omitted from the averaging.

The main difference is that the linked question gives you the average of the very last `N` (non-blank) values in the column. Thus as you append new values, the computation updates. It's not a sliding window, and not suitable for filling down.

• What have you tried? What errors?
– user55949
Jul 15, 2019 at 7:39
• @JanDoggen, I tried lots of things, until I finally came up with the answer I posted below. Jul 15, 2019 at 11:55

## 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))),"")`