1

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:

input & output illustration

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.

2
  • 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.
    – cp.engr
    Jul 15, 2019 at 11:55

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.