Average the last N values in a Sheets column using Arrayformula

Question

Experts, I need your help...

I'm looking to create a formula (not necessarily an arrayformula), that will automatically calculate an average. This sounds easy, but surprisingly, it isn't.

Let me explain...

I have a list of values in column G of a google sheet. Once each week, a script adds a new row at the bottom of the sheet and a new value in column G of the new row. Each week, after the script runs, I need to get the average of the last N cells and paste the result in columns N, O,and P. (The average of 2 values pastes to column N, the average of 6 values pastes to column O, and the average of 13 values pastes to column P.)

Calculating an average of values in a column is simple. Calculating the average of the last N values in a column is simple. Calculating an average automatically for the last N values in a column is difficult. I don't open the sheet every day, and even if I did, I usually don't have the time (or desire) to update every time. I want this to be automatic. (If what I want to do is impossible with a formula, a script solution is acceptable.)

Here's how I need it to work. The source values are in column G. (Row 1 is always the column label.) The values to be averaged start in cell G2 and continue to the end of column G.

I need to average G2 and G3, and paste the result in cell N3. Then, average G3 and G4, and paste the result in cell N4. Then, average G4 and G5, and paste the result in cell N5. And so on, to the bottom of column G.

Then, I need to duplicate this, but with 6 values. I need to average G2:G7, then paste the result in cell O7. Then, average G3:G8, then paste the result in cell O8. Then, average G4:G9, then paste the result in cell O9. Again, all the way to the bottom of column G.

Finally, I need to do all of this again, only this time with 13 values.

(If someone can tell me how, I can share a sheet.)

Would anyone care to give me some insight?

Answer 1

Please, see Test sheet cell D2.

Formula returns average of N, where N is cell D1 (can be changed in cell or directly in formula)

=ARRAYFORMULA(
 (SUMIF(SEQUENCE(COUNT($C$2:$C),1,1,1),"<="&SEQUENCE(COUNT($C$2:$C),1,1,1)+D$1-1,$C$2:$C)-
  SUMIF(SEQUENCE(COUNT($C$2:$C),1,1,1),"<"&SEQUENCE(COUNT($C$2:$C),1,1,1),$C$2:$C))/D$1)

Idea is to generate sequence of numbers from 1 to M where M is number of cells in your data.

Then using SUMIF() (unfortunately SUMIFS doesn't work with ARRAYFORMULA) add all values with index lower than sum of starting point and specified number in D1 and subtract sum of values with index lower than starting point.