2

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
0

2 Answers 2

0

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.

0

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

AVERAGE  BYCOL  CHOOSEROWS  COUNTA  IF  LAMBDA  LET  MAP  SEQUENCE

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.

Your Answer

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

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