Google-sheets – Looking for a “weighted average if” functions in Google Sheets

google sheetsworksheet-function

I am familiar with the =AVERAGE.WEIGHTED function in Google Sheets, but I'm wondering if there's a way to add an IF in there, like =AVERAGEIF. I have a baseball spreadsheet, with the following columns:

Column A – Player Name, Column B – Team, Column C – Innings Played, Column D – Batting Average

Basically, I want a weighted average of batting averages of all players on certain team, using innings played as the weights. If I used =AVERAGE.WEIGHTED(D:D, C:C), I would get a nice weighted average of all batting averages with innings played as the weights, but I only want to include players who have a Team of Yankees. How could I go about this?

Thanks in advance!

Best Answer

I actually found one way to accomplish this! I added a Column E which is Innings Played multiplied by Batting Average (Column C * Column D). Then, I can use a =SUMIF to sum results in column E if Column B shows a certain team, and divide that by another =SUMIF that sums Column C if Column B shows that same team. =SUMIF(B:B, "Yankees", E:E)/SUMIF(B:B, "Yankees", C:C). Just wanted to post this answer in case anyone else is looking for the same thing!

Related Solutions

Google Sheets – How to Calculate Average Between Cells

In principle, this could be done without a script... but it's really ugly. Already looking up the nearest nonempty cells (up and down) is nontrivial (and slow), and it gets much worse when you start writing data into the column, thus changing what's empty or not (circular dependency). One could calculate in another column and then paste the values... but it's better to just use a script.

The script below adds a custom function "Interpolate" to the menu, when the document opens. Place the cursor to any cell within a gap, and use the menu command.

Although the function interpolate() looks long, the logic is very straightforward. Among the advantages of a script solution is robust handling of edge cases: there is no data above, there is no data below, the data isn't numeric, or the gap doesn't actually exist.

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", [{name: "Interpolate",  functionName: "interpolate"}]);
}

function interpolate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var height = sheet.getDataRange().getHeight();
  var cell = sheet.getActiveCell();
  if (cell.getValue() !== '') {
    return;
  }
  var row = cell.getRow();
  var column = cell.getColumn()
  var values = sheet.getRange(1, column, height, 1).getValues();
  for (var i=row-1; i>1; i--) {
    if (values[i-1][0] !== '') {
      break;
    }
  }
  for (var j=row+1; j<height; j++) {
    if (values[j-1][0] !== '') {
      break;
    }
  }
  var a = values[i-1][0];
  var b = values[j-1][0];
  if (typeof a == 'number' && typeof b == 'number') {
    var insert = [];
    for (var k=1; k<j-i; k++) {
      insert.push([a+(b-a)*k/(j-i)]);
    }
    sheet.getRange(i+1, column, j-i-1, 1).setValues(insert);
  }
}

Google Sheets – How to Average Across Sheets

Please try something like:

=query({Sheet1!A1:B10;Sheet2!A1:B10},"select Col1, Avg(Col2), count(Col1) where Col1 is not NULL group by Col1 label Col1 'name', count(Col1) 'number of times referenced?', Avg(Col2) 'average'")

Adjust ranges to suit those you did not mention.

QUERY

For your sample not necessary but you might want to insert order by Col1 asc before label.

Best Answer

Related Solutions

Google Sheets – How to Calculate Average Between Cells

Google Sheets – How to Average Across Sheets

Related Topic