How to display record of last 10 games

[dotcanada]

Good day.

I have been banging my head against the wall trying to figure this out. I've search past posts on this topic but still can't figure it out.
I've attached a sample of my spreadsheet of the schedule. What I would like to do is display the record of each teams' the last 10 games ((W-L-OTL) for each team).

Many thanks in advance.

2023-24 NHL Season (2).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Home						Visitor
2	Date	Team	Score	OT	RW	ROW	W/L/OTL	Score	OT	RW	ROW	W/L/OTL	Team
3	Tuesday, October 10, 2023	Tampa Bay Lightning	5		x	x	W	3				L	Nashville Predators
4		Pittsburgh Penguins	2				L	4		x	x	W	Chicago Blackhawks
5		Vegas Golden Knights	4		x	x	W	1				L	Seattle Kraken
6	Wednesday, October 11, 2023	Carolina Hurricanes	5		x	x	W	3				L	Ottawa Senators
7		Toronto Maple Leafs	6				W	5	x			OTL	Montréal Canadiens
8		Boston Bruins	3		x	x	W	1				L	Chicago Blackhawks
9		Calgary Flames	5		x	x	W	3				L	Winnipeg Jets
10		Los Angeles Kings	2				L	5		x	x	W	Colorado Avalanche
11		Vancouver Canucks	8		x	x	W	1				L	Edmonton Oilers
12	Thursday, October 12, 2023	Buffalo Sabres	1				L	5		x	x	W	New York Rangers
13		Columbus Blue Jackets	2				L	4		x	x	W	Philadelphia Flyers
14		New Jersey Devils	4		x	x	W	3				L	Detroit Red Wings
15		Dallas Stars	2				W	1	x			OTL	St. Louis Blues
16		Minnesota Wild	2		x	x	W	0				L	Florida Panthers
17		Nashville Predators	3		x	x	W	0				L	Seattle Kraken
18		San Jose Sharks	1				L	4		x	x	W	Vegas Golden Knights
19	Friday, October 13, 2023	New Jersey Devils	3	x			OTL	4				W	Arizona Coyotes
20		Washington Capitals	0				L	4		x	x	W	Pittsburgh Penguins
21	Saturday, October 14, 2023	Ottawa Senators	5		x	x	W	2				L	Philadelphia Flyers
22		Winnipeg Jets	6		x	x	W	4				L	Florida Panthers
23		Boston Bruins	3		x	x	W	2				L	Nashville Predators
24		Columbus Blue Jackets	5		x	x	W	3				L	New York Rangers
25		Detroit Red Wings	6		x	x	W	4				L	Tampa Bay Lightning
26		Montréal Canadiens	3		x	x	W	2				L	Chicago Blackhawks
27		Pittsburgh Penguins	5		x	x	W	2				L	Calgary Flames
28		Toronto Maple Leafs	7		x	x	W	4				L	Minnesota Wild
29		New York Islanders	3		x	x	W	2				L	Buffalo Sabres
30		St. Louis Blues	2				W	1	x			OTL	Seattle Kraken
31		Edmonton Oilers	3				L	4		x	x	W	Vancouver Canucks
32		San Jose Sharks	1	x			OTL	2				W	Colorado Avalanche
33		Vegas Golden Knights	4		x	x	W	1				L	Anaheim Ducks
34		Los Angeles Kings	5	x			OTL	6				W	Carolina Hurricanes
35	Sunday, October 15, 2023	Ottawa Senators	5		x	x	W	2				L	Tampa Bay Lightning
36		Anaheim Ducks	6		x	x	W	3				L	Carolina Hurricanes
37	Monday, October 16, 2023	Columbus Blue Jackets	0				L	4		x	x	W	Detroit Red Wings
38		New Jersey Devils	3				L	4		x	x	W	Florida Panthers
39		New York Rangers	2		x	x	W	1				L	Arizona Coyotes
40		Toronto Maple Leafs	1				L	4		x	x	W	Chicago Blackhawks
41		Washington Capitals	3				W	2	x			OTL	Calgary Flames
Schedule

Cell Formulas
Range	Formula
G3:G41	G3	=IF(C3>H3,"W",IF(D3="x","OTL",IF(C3<H3,"L","")))
L3:L41	L3	=IF(C3<H3,"W",IF(I3="x","OTL",IF(C3>H3,"L","")))

Also asked here How to display record of last 10 games plus streak

 

[Eric W]

Try:

Book1
	A	B	C	D	E
45	Team	Last 10 record (W-L-OTL)		Last 10 record (W-L-OTL)	(Helper)
46	Anaheim Ducks	1-1-0		1-1-0	1
47	Boston Bruins	2-0-0		2-0-0	1
48	Buffalo Sabres	0-2-0		0-2-0	1
49	Calgary Flames	1-1-1		1-1-1	1
50	Carolina Hurricanes	2-1-0		2-1-0	1
51	Columbus Blue Jackets	1-2-0		1-2-0	1
52	Dallas Stars	1-0-0		1-0-0	1
53	Detroit Red Wings	2-1-0		2-1-0	1
54	Edmonton Oilers	0-2-0		0-2-0	1
55	Los Angeles Kings	0-1-1		0-1-1	1
56	Minnesota Wild	1-1-0		1-1-0	1
57	Montréal Canadiens	1-0-1		1-0-1	1
58	Nashville Predators	1-2-0		1-2-0	1
59	New Jersey Devils	1-1-1		1-1-1	1
60	New York Islanders	1-0-0		1-0-0	1
61	New York Rangers	2-1-0		2-1-0	1
62	Ottawa Senators	2-1-0		2-1-0	1
63	Pittsburgh Penguins	2-1-0		2-1-0	1
64	San Jose Sharks	0-1-1		0-1-1	1
65	St. Louis Blues	1-0-1		1-0-1	1
66	Tampa Bay Lightning	1-2-0		1-2-0	1
67	Toronto Maple Leafs	2-1-0		2-1-0	1
68	Vancouver Canucks	2-0-0		2-0-0	1
69	Vegas Golden Knights	3-0-0		3-0-0	1
70	Washington Capitals	1-1-0		1-1-0	1
71	Winnipeg Jets	1-1-0		1-1-0	1
Sheet9

Cell Formulas
Range	Formula
A46:A71	A46	=SORT(UNIQUE(B3:B41))
D46:D71	D46	=COUNTIFS(INDEX(B:B,E46):B$41,A46,INDEX(G:G,E46):G$41,"W")+COUNTIFS(INDEX(M:M,E46):M$41,A46,INDEX(L:L,E46):L$41,"W")&"-"&COUNTIFS(INDEX(B:B,E46):B$41,A46,INDEX(G:G,E46):G$41,"L")+COUNTIFS(INDEX(M:M,E46):M$41,A46,INDEX(L:L,E46):L$41,"L")&"-"&COUNTIFS(INDEX(B:B,E46):B$41,A46,INDEX(G:G,E46):G$41,"OTL")+COUNTIFS(INDEX(M:M,E46):M$41,A46,INDEX(L:L,E46):L$41,"OTL")
E46:E71	E46	=IFERROR(AGGREGATE(14,6,ROW($A$3:$A$41)/SIGN(($B$3:$B$41=A46)+($M$3:$M$41=A46)),10),1)
B46:B71	B46	=LET(s,IF($B$2:$B$41=A46,$G$2:$G$41,IF($M$2:$M$41=A46,$L$2:$L$41,"")),f,FILTER(s,s<>""),r,ROWS(f),seq,SEQUENCE(r),ff,FILTER(f,seq>r-10),SUM(IF(ff="W",1))&"-"&SUM(IF(ff="L",1))&"-"&SUM(IF(ff="OTL",1)))
Dynamic array formulas.

Also, PLEASE update your profile to show what version of Excel you're using. I created a formula (B46) using array formulas. Then I noticed on the other forum you posted on that you only have Excel 2016, so I had to go back and come up with another version (D6, with a helper cell in E6).

Let us know how this works.

 

[dotcanada]

Firstly, MANY thanks for your help with this and do appreciate and respect your time. And my apologies for the archaic version of office I'm using (2016). I do plan to get 2024 when it's made available later in the year. I am just revisiting my spreadsheet and using the only version I have available at this time.

Also, just some FYI (sorry, I should had mentioned this initially), the schedule I provided was just a sample of the first few rows of the sheet (the number of rows I actually have is 1314 on this sheet).
This schedule is for this current NHL season. Therefore, there are a lot of blank cells for the upcoming dates. So, I changed OPTION in the AGGREGATE from 6 to 4. This gave me a value (under the "Helper" heading) of 1 (it gave me a much larger number before). Another change I made was that I noticed you put B$41, G$41, M$41, and L$41, I assume to indicate the end of the table. I replaced these values with the values of the actual end of my schedule (in my case B$1314, G$1314, M$1314, and L$1314). For testing purposes, I'm using the Boston Bruins (I have a separate sheet with the list of teams on it).

Once these changes were in place, in the "Last 10 Record" column, unfortunately it's giving me the team's total record of the season up until today.

Would it be okay to paste the entire schedule? I used the D46 formula almost to the letter (with the exception of the replacements mentioned above).

Thanks again for your time. Cheers.

 

[Eric W]

Thank you for updating your profile.

Yes, of course you should change the $41 to the end of your table in both the D and E formulas. The $3 in the E formula is the start of the table.

However, the option in AGGREATE must be 6, not 4! The purpose of that formula is to find the 10th largest row that has the given team in it. The AGGREGATE is constructed to give an error for every row that doesn't have the given team in it, so the LARGE (14) option only looks at the rows with the given team. Then it picks the 10th highest. If there are fewer than 10 rows, then that's where the IFERROR comes into the picture, and tells the D formula to start looking at row 1.

However, if you want to exclude games that are scheduled but not played yet, change the E formula to:

=IFERROR(AGGREGATE(14,6,ROW($A$3:$A$41)/SIGN(($B$3:$B$41=A46)+($M$3:$M$41=A46))/($G$3:$G$41<>""),10),1)

Try that to see if it works. You can't post a 1300 row table here, but there are options.

 

[dotcanada]

Eureka!!!

Many, many thanks!!!

 

[Eric W]

Happy to help!