Google-sheets – Moving average up until every listed week

google sheetsgoogle-sheets-arrayformula

I'm trying to automatically calculate a new average every week, so I can see the evolution of the average over the past weeks.

I have one column with the week numbers. The second column contains an amount of applicants per week. In the third column, I wish to get the average of the number of applicants this week and all the weeks prior. So if I have three rows, the first average will only use the the first row's number of applicants, the second row's average will be row 1 + row 2 divided by 2, the third row's average will be the amount of applicants of row 1 + 2 + 3 divided by 3.

There's probably a simple way to achieve this, but I cannot figure it out, and I don't know how to phrase it to find the answer in Google.

I can simply drag the formula down and that works, but I'm using ArrayFormulas everywhere and I'd like this average to be generated automatically as well.

Best Answer

One way to do this is to calculate a cumulative sum of column B and divide it by the number of rows where numbers are present. Try this:

=arrayformula( 
  iferror( 1 / ( 
    isnumber(B2:B) 
    * 
    countif( if(isnumber(B2:B), row(B2:B)), "<=" & row(B2:B) ) 
    / 
    dsum( 
      if( row(B:B) <= transpose(row(B2:B)), B:B ), 
      sequence(rows(A2:B)), 
      transpose(iferror(column(A2:B) / 0)) 
    ) 
  ) ) 
)

Related Solutions

Google Sheets – Formula for Every X Rows with Incrementing Cell Value

Please try, 1 in A2 and in A3 copied down:

=if(mod(row(),7)=2,A2+1,A2)

Google-sheets – Using a script to process form input every time a form is submitted

If the data you want to use is in, say, column E (5th column), then instead of

sheet.getRange(row,3).setValue(1);

you would have

sheet.getRange(row,3).setValue(sheet.getRange(row,5).getValue() + 4);

Or, if the computation involves several values in that row, say in the first ten columns, then get the row all in once:

var values =  sheet.getRange(row, 1, 1, 10).getValues(); 
var output = values[0][4] + 2*values[0][7] - 3*values[0][9];
sheet.getRange(row, 3).setValue(output);

In the latter version, JavaScript array indexing is used, where indices begin at 0, unlike in Sheets. So, values[0][4] means the zeroth row, fourth column in the fetched range; in practice, column E of the row that was fetched.

Also, the approach with getLastRow() may skip some entry if two arrive simultaneously. If you want to guard against that, get all the data and process any rows that aren't yet. Like this:

var values =  sheet.getDataRange().getValues(); 
for (var i=0; i<values.length; i++) {
  if (values[0][2] === "") { 
    var output = values[0][4] + 2*values[0][7] - 3*values[0][9];
    sheet.getRange(i+1, 3).setValue(output);
  }
}

Best Answer

Related Solutions

Google Sheets – Formula for Every X Rows with Incrementing Cell Value

Google-sheets – Using a script to process form input every time a form is submitted

Related Topic