-1

Have a sheet with a lot of sports data...

Row 1 has the titles of each column. Below that is data of individual games. Here's an idea of what it looks like, simplified. 

HomeTeam,  AwayTeam,  HomeGoals, AwayGoals, TotalGoals, Result  
Bruins,    Leafs,     3,         2,         5,          HomeWin  
Coyotes,   Jets,      3,         3,         6,          Tie  
Panthers,  Lightning, 2,         3,         5,          AwayWin  
Predators, Kings,     1,         4,         5,          AwayWin  
Bruins,    Blues,     2,         2,         4,          Tie


etc

So what I want to do is pull in the value of HomeGoals for the last x games from x team.

For example, let's say I have a 1000 rows of data, how do I best get the sum or average of HomeGoals in the Bruins (team) last 4 games as the home team - so the 4 most recent times they show up in column A?

Similarly, how would I count the amount of AwayWins (last column) in the Bruins last x home games? Like if I have the Bruins 10 most recent home games, I want to count the number of times AwayWin was the result.

The data is ordered from oldest to most recent (but I can flip if needed).

player0
  • 124,011
  • 12
  • 67
  • 124
Grandaddycrunk
  • 21
  • 3
  • AVERAGE(OFFSET(C2,COUNT(C:C),0,-4)), Something like this? It gets the average of the c2 column last 4 rows. (2+1+2+3)/4 =2 – Arundeep Chohan Jun 06 '19 at 03:13
  • The problem is I need another condition added to it. I don't just want the most recent 4 rows...I want the most recent 4 rows when x is the home team. Right now with what you posted it will just take the 4 bottom rows... I know I could do this with a vlookup and then just adjust the range I'd like to choose from. But I'm always adding more data to this so that would require constant manipulation. There has to be an easier and more automatic way to do this, which is what I'm looking for. – Grandaddycrunk Jun 06 '19 at 03:21
  • AverageIf can compare the hometeam to an x. – Arundeep Chohan Jun 06 '19 at 03:36
  • So use averageifs(), set as many criteria as you want. Or countifs() with sumifs()... – Solar Mike Jun 06 '19 at 03:41
  • @SolarMike How can I do averageifs() to filter down just the last ___ instances? Ie I want the average home goal (column C) value the last 5 times (from the bottom) the team shows up...how do I put that into the formula? – Grandaddycrunk Jun 06 '19 at 04:05
  • 1
    Well, you don't show a date... So if the data is ordered newest at the top downwards then take the first 5 (or 10 - you are not clear on this either) or if newest at the bottom then take the last 5 or 10... – Solar Mike Jun 06 '19 at 04:41

2 Answers2

0

cell J2:

=AVERAGE(IFERROR(QUERY({A2:F}, "select Col3 where Col1='Bruins' offset "&
 COUNTA(FILTER(A:A, A:A="Bruins"))-4)))

cell J4:

=AVERAGE(IFERROR(QUERY({A2:F}, "select Col3 where Col1='Bruins' and Col6='AwayWin' offset "&
 COUNTA(FILTER(A:A, A:A="Bruins", F:F="AwayWin"))-2)))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Awesome, thank you! Got that to work. Next question...in that formula, I would ideally like to say instead of Bruins, use a cell reference. So the part about where Col1='Bruins' as well as A:A="Bruins" - is there any way to make that refer a cell? Say in A1 I have Bruins...if I put this formula in B1, I'd like it to put the output for whatever team I have in cell A1 - so if I switch the team name in A1 to Panthers, the formula will automatically recalculate. The formula you send requires manually editing the team name in the formula, from what I can tell. – Grandaddycrunk Jun 06 '19 at 18:23
  • `Col1='Bruins'` change to `Col1='"&A1&"'` and `A:A="Bruins"` to `A:A=A1` – player0 Jun 06 '19 at 18:26
  • Thanks, I'll make sure to follow the instructions in the link you sent. I have one final question and then I'm all set to go. How do I do a count of text using that query? So in the code you sent I am able to get the averages. Let's say in that sheet you posted a pic of, I want to do a count of the amount of times "AwayWin" happens in the 4 most recent home games the Bruins played (from the bottom). Ie I would expect the count function to return 3 AwayWins (row 25, 21, 17, 16 last 4 Bruins home games, 3 AwayWins). Thanks again for all the help so far. – Grandaddycrunk Jun 08 '19 at 01:32
  • @Grandaddycrunk `=IF(COUNTA(QUERY({A2:F}, "select Col3 where Col1='Bruins' and Col6='AwayWin' offset "& COUNTA(FILTER(A:A, A:A="Bruins", F:F="AwayWin"))-4))>3, COUNTA(QUERY({A2:F}, "select Col3 where Col1='Bruins' and Col6='AwayWin' offset "& COUNTA(FILTER(A:A, A:A="Bruins", F:F="AwayWin"))-4)), COUNTA(IFERROR(QUERY({A2:F}, "select Col3 where Col1='Bruins' and Col6='AwayWin'"))))` - https://i.stack.imgur.com/8Y1nK.png – player0 Jun 08 '19 at 02:24
0

On SO it is supposed to be one Q per original post, so I have chosen:

pull in the value of HomeGoals for the last x games from x team

and assumed dates are in ColumnA, with HomeTeam in B1.

Please try the x for last games in I1 and the x for team in J1 and:

 =sum(query(SORT(A2:G,1,),"select Col4 where Col2='"&J1&"' limit "&I1))
pnuts
  • 58,317
  • 11
  • 87
  • 139