Calculating Average for a section

Question

I am creating a Task list, and I have few stages. I have % done. in the picture below I want Cell J8 to calculate the average the to Stage 1 % Done which is between cell J9:J15, however, I want be able to insert a new row below or above row 15 and i want the cell J8 to calculate the average for those too. Hopefully this makes sense,

Thank you!

I tried to look for appscript code but i couldnt find any,

Answer 1

Using Apps Script to compute the average

In your use case, I've opted to create a script to calculate the average on column J. For reference, here's the script I wrote:

/*Script is configured to only run when a specific sheet and the J column is changed.
  This ensures that the script won't randomly trigger if another sheet and/or column is edited. */

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  if (sh.getName() == 'sheet_name' && e.range.columnStart == 10) { //replace sheet_name with the name of your sheet
    var indexVal = sh.getRange(9, 1, sh.getLastRow() - 8).getValues().flat().indexOf('Stage 2') + 8; // gets the last row of the value to average
    var aveRange = sh.getRange(`J9:J${indexVal}`).getValues().filter(x => x != ''); // gets the all of the values to average.
    var aveValue = aveRange.reduce((a, b) => parseFloat(a) + parseFloat(b)) / aveRange.length;
    sh.getRange('J8').setValue((aveValue * 100).toFixed(2) + '%'); //adds the computed average to J8
  } else return; 
}

This script gets the last row of the values to average by checking which row Stage 2 is on then computes the average of all of the values from J9 up to the row before Stage 2. The computed average is then insterted onto J8. The script only triggers when a specific sheet and the J column was edited. Be advised, however, that simple triggers won't run when an entire row is deleted.

Also, note that, in order for a simple trigger to work, you would have to bound this script to the spreadsheet.

References:

• getRange()

• Custom Menus

• Simple triggers