# Running average of the last [n] values in a column

I'm looking to create a formula in Google Sheets that will automatically calculate the running average for values in G2:G, based on the most recent `n` rows. I want to calculate this across three columns, with each column using a different `n` value.

# Background

1. I have a list of values in G2:G of my spreadsheet.
2. Once a week, a script adds a new row of data to Column G, below the last populated value.
3. In columns N, O, and P, I calculate the running average for the current and previous rows up to a total of `n` rows, where `n` is equal to:
• `2` in column N,
• `6` in column O,
• `13` in column P
4. In each column, the calculations start in the `nth` row so there are enough prior rows to keep the calculation results for that column consistent.
5. Currently, each week after the script has added a new value, I calculate the new average for each of the three `n` values then paste those results at the bottom of their respective Columns N-P.
6. I'm looking for a formula to automatically perform the calculation for me and update the values dynamically when new values are added by the script.

# Sample Data

1. Column G contains the values populated by the script.
2. Columns N-P have the running average results.
G H-M N O P
1 Values `n=2` `n=6` `n=13`
2 59
3 62 60.5
4 73 67.5
5 69 71
6 69 69
7 47 58 63.17
8 43 45 60.5
9 31 37 55.33
10 30 30.5 48.17
11 19 24.5 39.83
12 64 41.5 39
13 50 57 39.5
14 82 66 46 53.69
15 46 64 48.5 52.69
16 25 35.5 47.67 49.85
17 18 21.5 47.5 45.62
18 71 44.5 48.67 45.77

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.

# Formula

Place in cell N2. It will calculate results for the three columns N-P.

``````=LET(range,G2:G, array,{2, 6, 13},
BYCOL(array, LAMBDA(n,
MAP(SEQUENCE(COUNTA(range)),
LAMBDA(i,
IF(i<n,,AVERAGE(
CHOOSEROWS(range,
SEQUENCE(n, 1,i,-1)))))))))
``````

# Explanation

1. LET is used to store values in named variables.

1. `range` stores the range reference.
2. `array` stores an array of `n` values.
2. BYCOL passes each value from `array` one-by-one into a LAMBDA function that stores the current value in `n`.

3. For each `n` the LAMBDA's formula runs a MAP function:

1. MAP's array argument is a SEQUENCE of numbers incrementing by 1, up to the number of values in `range`, which is determined using COUNTA.
2. The numbers in the SEQUENCE each represent a row index for a populated value from `range`
3. MAP passes each value from that SEQUENCE, one-by-one, into another LAMBDA function that stores the current row index value in `i`
4. The LAMBDA's formula checks if `i` is less than `n` and, if true, a blank value is returned because it indicates that there are insufficient rows to average `n` values, and, if false, the formula continues.

5. The remaining formula returns the AVERAGE of the most recent `n` rows.

6. CHOOSEROWS returns the actual rows to average from `range` using another SEQUENCE as its rows argument.

7. The SEQUENCE returns an array that is `n` rows high, `1` column wide, starts with `i`, and increments `-1`. For example,

```row index i=20, n=6
----------------------
=SEQUENCE(n,1,i,-1)
=SEQUENCE(6,1,20,-1)
={20,19,18,17,16,15```

This array represents each row INDEX to be returned from `range`.

8. Lastly AVERAGE is applied to the rows returned by CHOOSEROWS and the LAMBDA's formula is applied to the next `i` until all `i`'s have been processed for that `n`.

9. The original BYCOL function will then supply the next `n`, until all `i`'s have been processed for all `n`'s.