Average a column without including the average cell in the same column, in a dynamic sheet?

0
votes

I have a sheet which pulls a dynamic set of rows from another sheet via an importrange and a dropdown. I need to place an average in the first empty cell in the same column, this average include all the numbers in the column above it starting at row 5 (without including the cell containing the average itself, as this creates a circular reference error). The number of rows will always change though.

I am fairly comfy with Google Script but still new to it and ideally would like to .setFormula in the Lastrow+1 based on column index, but I can't seem to get the AVERAGE formula to count the entire column except the cell which contains the average formula.

1
google-apps-scriptgoogle-sheets
Do you have a demo spreadsheet for us to make it easier to visualize your question? – Riël
docs.google.com/spreadsheets/d/… The document has HIPAA info in it so here is a mock replication – KyleM
Thank you! Added an answer below. – Riël

1 Answers

1
votes

This script should help you, this works for one column but I think it will help you changing it to work for multiple columns if needed:

function onEdit(e) {
  //check if first row is changed
  if (e.range.getRow()==1) {
    var s = SpreadsheetApp.getActiveSheet();
    //remove all data because filter does not want to overwrite existing values
    s.getRange("A2:B").clear();
    //put back the filter function
    s.getRange("A5").setFormula("=FILTER(Sheet2!A1:B, Sheet2!A1:A = A1)");
    //get number of rows
    var lastrow = s.getLastRow();
    //add the average function below
    s.getRange("B"+(lastrow+1)).setFormula("=AVERAGE(B2:B"+(lastrow)+")");
 }
}