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

Question

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.

Answer 1

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) Hs with Is.

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