Search code examples
if-statementgoogle-sheetsaveragearray-formulasgoogle-sheets-query

Get the last 5 results of column C or D if column A or B is equal to ___?

I know the title is a horrible description, sorry.

Basically I have a sheet with results from basketball games. So in column A I have the home team. In column B I have the away team. In column C the home team's points. In column D the away team's points. There's about 500 rows worth of data at the minute.

What I want to do is the following:

Say I want to get the average points scored by the New York Knicks in their last 5 games. The most recent games are at the bottom of the sheet, and the first/oldest ones at the top of the sheet.

So across the bottom/last 5 instances of "New York Knicks" in column A and B, I want the average of the results of C (if New York Knicks is in column A) and D (if in column B).

I know how to do this if I would want just the last 5 home games for instance (so in that instance I basically query the bottom 5 results of column C in the last 5 occurrences of column A being New York Knicks). I don't know how to do it when I am looking for when New York Knicks occurs in either column A or B, and then have to get the averages from column C or D.

Can anyone help?

Solution

  • this will transform your 4 columns into 2 columns:

    =ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(A2:B<>"", "♠"&A2:B&"♦"&C2:D, )),,999^99)),,999^99), "♠")), "♦"))

    0

    and average score of the last 5 games:

    =ARRAYFORMULA(AVERAGE(QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(A2:B<>"", "♠"&A2:B&"♦"&C2:D, )),,999^99)),,999^99), "♠")), "♦"), 
 "select Col2 
  where lower(Col1) contains 'new york knicks' 
  offset "&COUNTIF(A2:B, "new york knicks")-5)))

    0