Hello
I have a spreadsheet where I would like to know the win-loss record for the last 10 games by team. I have seen some really cool examples where people have suggested using formulas such as
=CHOOSE(COUNTIF(OFFSET(A1,LOOKUP(REPT("Z",255),B:B,ROW(A:A))-10,0,10,1),"Win")+1,"0-10","1-9","2-8","3-7","4-6","5-5", "6-4", "7-3", "8-2", "9-1", "10-0") to get the win loss for the last 10 games, but it doesn't take into acct the different teams
I started using the -find formula combined with lookup such as
=LOOKUP(, -FIND("Team A",A:A ),ROW(B:B)) to get the row number of the last filled in value for Team A, but I get stuck trying to use offset as in
=OFFSET(A1, LOOKUP(,-FIND("Team A",A:A ),ROW(B:B))-10, 0) as this gives me the result of the game 10 games before the last game that Team A played regardless of whether that game was played by Team A or Team B.
Can anyone assist or provide some guidance?
As an example, my sheet looks like:
team A Win Team A Win Team A Loss Team B Loss Team A Loss Team A Loss Team A Win Team A Win Team B Win Team B Win Team B Loss Team B Loss Team B Loss Team A Loss Team B Loss Team A Loss Team A Win Team B Loss Team A Win
The results for Team A should be: 6-4 and for Team B: 2-6
Bookmarks