0

I would like to calculate the average of the previous N-elements in a column and return an array. The output should be something like this for the last 3-elements on column B, column C shows the corresponding formula. average prevous N-element

In my real problem, I need to return the result using an arrayformula for the entire column. Column D shows the formula for computing the average for non-empty rows, but I am not able to return the result considering the subset of the last three.

The following question: average the previous 5 cells that have values addressed a similar problem, but the result is not returned as an array. If I adapt the formula in Column D, with the solution from the question, I don't the expected result:

=ARRAYFORMULA(if(NOT(ISBLANK(A:A)),
     Average(Filter(Sort(A:A,If(A:A="",0,Row(A:A)),false),
         Row(A:A)<3)),))

I don't get the expected result, the result is 5.5 for all non-empty rows.

David Leal
  • 6,373
  • 4
  • 29
  • 56
  • Does this answer your question? [How to see the +/- change in rolling average between two cells](https://stackoverflow.com/questions/59119576/how-to-see-the-change-in-rolling-average-between-two-cells) – Rafa Guillermo May 24 '21 at 07:46

1 Answers1

2

Your post sample shows calculable data starting in Row 1 (as opposed to having any headers in Row 1). So the formula I've written will reference the entire Column A:A.

Delete everything from Column B and place the following formula in B1:

=ArrayFormula(IF(A:A="",,(SUMIF(ROW(A:A),">="&ROW(A:A)-2,A:A)-SUMIF(ROW(A:A),">"&ROW(A:A),A:A)) / IF(ROW(A:A)<3,ROW(A:A),3)))

IF a cell in A:A is blank, the formula will return null for the corresponding cell in B:B.

Otherwise, the first SUMIF will sum all values in A:A that are in cells beginning two rows back. From this will be subtracted a second SUMIF of all values in A:A that are in cell numbers greater than the current cell number. This leaves you with the total for "the last three rows" held in memory for each cell. This total is then divided by the return of the IF clause (i.e., the row number if the current row number is less than 3, or 3 for all others).

Again, this is based on the particulars of your post. If your real-world application doesn't start in Row 1 and run the full Column A:A, use this version:

=ArrayFormula(IF(A2:A="",,(SUMIF(ROW(A2:A),">="&ROW(A2:A)-2,A2:A)-SUMIF(ROW(A2:A),">"&ROW(A2:A),A2:A)) / IF(SEQUENCE(ROWS(A2:A),1)<3,SEQUENCE(ROWS(A2:A),1),3)))

Just swap the first cell reference per range (i.e., here, A2) with the actual first cell of your calculable range.

Erik Tyler
  • 9,079
  • 2
  • 7
  • 11
  • if works in both cases. I tested that it also works using `NOT(ISBLANK(A:A))` to keep it consistent with other formulas I have in my sheet: `=ArrayFormula(IF(NOT(ISBLANK(A2:A)),(SUMIF(IF(ROW(A2:A),ROW(A2:A)),">="&ROW(A2:A)-2,A2:A)-SUMIF(IF(ROW(A2:A),ROW(A2:A)),">"&ROW(A2:A),A2:A)) / IF(SEQUENCE(ROWS(A2:A),1)<3,SEQUENCE(ROWS(A2:A),1),3),))`. Waiting for other responses, if not this the correct one. – David Leal May 23 '21 at 18:16
  • I am reviewing your formula, and I am wondering, why you need this: `IF(ROW(A2:A),ROW(A2:A))` it doesn't have the false clause so it will return always `A2:A)`. Thanks – David Leal May 24 '21 at 23:20
  • 1
    It didn't need the IF clause. It must have been part of some intermediate step in constructing the formula, one which I forgot to remove in the final. I've edited the formulas in my post with those removed. Good catch. – Erik Tyler May 24 '21 at 23:30