How to get average of input category in google sheet-CodePudding

The thing is I want to find average of value from a column with a condition of category, so whenever I put a category value into the cell I should get average of that category in another cell

CodePudding user response：

You could use a simple QUERY formula.

CodePudding user response：

Average

function average() {
  const ss = SpreadsheetApp.getActive()
  const sh = ss.getSheetByName("Enter Sheet Name");
  var avg = sh.getRange(2, 2, sh.getLastRow() - 1).getValues().flat().reduce((a,c) => { a.sum  = c;  a.cnt;return a;},{sum:0,cnt:0,av:function(){return this.sum/this.cnt}}).av();
  //Logger.log(avg);
  return avg;
}