Last 10 win / loss by team

[bebiba]

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

[AliGW]

Will you please attach a sample Excel workbook? Nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[modytrane]

I copied your data into excel file. Formulas added.
Does that work for you?

[bebiba]

Sure thing...

[AliGW]

Please let us know if the solution offered in post #3 worked for you.

[63falcondude]

If you only want to account for the last 10 games, I would include a helper column in column C:

C1 =COUNTIF(A$1:A1,A1)
Copied down column C

Then, with "Team A" in E1 and "Team B" in E2, try this in F1:
=COUNTIFS(A$1:A$19,E1,B$1:B$19,"Win",C$1:C$19,"<="&MAX(IF(A$1:A$19=E1,C$1:C$19)),C$1:C$19,">"&MAX(IF(A$1:A$19=E1,C$1:C$19))-10)&"-"&COUNTIFS(A$1:A$19,E1,B$1:B$19,"Loss",C$1:C$19,"<="&MAX(IF(A$1:A$19=E1,C$1:C$19)),C$1:C$19,">"&MAX(IF(A$1:A$19=E1,C$1:C$19))-10) Ctrl Shift Enter
Copied down column F.

[bebiba]

Thank you very much modytrane!

I think it's close, but it calculates the Win/Loss for all games for the team. For example, Team A now shows a 6-5 record, so it's calculating 11 games. I only want the record for the last 10 games that Team A played.
If I were to add 20 more games dispersed for team A and B, I would only want the last 10 that those teams played.

[bebiba]

Thank you so much falcondude.. You rock man!

[63falcondude]

You're welcome. Glad we could help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[bebiba]

It solved the question I asked, but I just made everything more complicated by splitting my data into multiple sheets (with a pivot table). Should I ask an additional question here, or mark this one as solved, because my question was indeed solved (and start a new thread with my now more complicated question)?

[AliGW]

No - carry on here, please, to explore the type of solution you require for the same problem.

[bebiba]

Again, thank you for your help. It solved the original problem with the first workbook. I figured I could use the example to solve a more complicated issue with another workbook, but I don't know if the formula will apply

You will see 2 sheets in the workbook. Sheet 1 is the data, Sheet 2 is the summary page that contains the pivot table where I want to apply the formula. The pivot table does a nice job of determining the Teams and the versions of that team.
Each team can have multiple "versions" of that team. What I want to do is calculate the last 10 win/loss record by team and version. I have tried to highlight the rows that correspond to the data that I manually calculated in sheet 2.

I hope you can help. Thanks again for the help with the first issue.

[63falcondude]

Oh boy... this is going to be ugly...

If you want to use a Pivot Table, you want the Team Names to be to the left of each version.

To do this, select your PivotTable > Design > Report Layout > Show in Tabular Form > Repeat All Item Labels. Also, you don't need a Grand Total here so you can get rid of that through Grand Totals > Off for Rows and Columns.

In D2 of Sheet1, put your helper column (Game #) formula in:
=COUNTIFS(A$2:A2,A2,B$2:B2,B2)

Now, in C2 of Sheet2, try this (ugly part):

=COUNTIFS(Sheet1!A$2:A$759,A2,Sheet1!B$2:B$759,B2,Sheet1!C$2:C$759,"Win",Sheet1!D$2:D$759,"<="&MAX(IF((Sheet1!A$2:A$759=A2)*(Sheet1!B$2:B$759=B2),Sheet1!D$2:D$759)),Sheet1!D$2:D$759,">"&MAX(IF((Sheet1!A$2:A$759=A2)*(Sheet1!B$2:B$759=B2),Sheet1!D$2:D$759))-10)&"-"&COUNTIFS(Sheet1!A$2:A$759,A2,Sheet1!B$2:B$759,B2,Sheet1!C$2:C$759,"Loss",Sheet1!D$2:D$759,"<="&MAX(IF((Sheet1!A$2:A$759=A2)*(Sheet1!B$2:B$759=B2),Sheet1!D$2:D$759)),Sheet1!D$2:D$759,">"&MAX(IF((Sheet1!A$2:A$759=A2)*(Sheet1!B$2:B$759=B2),Sheet1!D$2:D$759))-10) Ctrl Shift Enter

See Attachment.

[63falcondude]

Going off of post #13, if you plan on adding data, I would format the data in Sheet1 as a table and then reference to that table (in the PivotTable Data Source and formulas).

Then, the formula in Sheet2 C2 would look like this:

=COUNTIFS(Table1[Team Name],A2,Table1[Version],B2,Table1[Result],"Win",Table1[Game '#],"<="&MAX(IF((Table1[Team Name]=A2)*(Table1[Version]=B2),Table1[Game '#])),Table1[Game '#],">"&MAX(IF((Table1[Team Name]=A2)*(Table1[Version]=B2),Table1[Game '#]))-10)&"-"&COUNTIFS(Table1[Team Name],A2,Table1[Version],B2,Table1[Result],"Loss",Table1[Game '#],"<="&MAX(IF((Table1[Team Name]=A2)*(Table1[Version]=B2),Table1[Game '#])),Table1[Game '#],">"&MAX(IF((Table1[Team Name]=A2)*(Table1[Version]=B2),Table1[Game '#]))-10) Ctrl Shift Enter

See attachment for table usage.

[bebiba]

Wow! Thanks so much!

[63falcondude]

You're welcome. Happy to help.

Thanks for the rep!