Display streak of wins or losses

[dotcanada]

Good day.

I am trying to make a display of the teams' current winning or losing streak. So, when a certain team wins 2 games in a row, I want the cell to display "W2" or if a team loses 3 games, then "L3". Also, let us say that if a team has a 3-game wining streak (W3) and then loses their next game, then there should be a L1 displayed.

I've tried various examples that I searched for over the net, however, as you can see in the L2BB image I posted, I have 2 columns of Wins and Losses (depending if a certain team is at home or visiting) and it wasn't working at all.

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
Schedule

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

 

[Sufiyan97]

Do you want something like

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

Cell Formulas
Range	Formula
G3:G30	G3	=IF(C3>I3,"W",IF(D3="x","OTL",IF(C3<I3,"L","")))
H3:H30	H3	=IF(G3="OTL","OTL",IF(COUNTIFS($B$3:B3,B3,$G$3:G3,G3)>1,G3&COUNTIFS($B$3:B3,B3,$G$3:G3,G3),G3))
M3:M30	M3	=IF(C3<I3,"W",IF(J3="x","OTL",IF(C3>I3,"L","")))
N3:N30	N3	=IF(M3="OTL","OTL",IF(COUNTIFS($O$3:O3,O3,$M$3:M3,M3)>1,M3&COUNTIFS($O$3:O3,O3,$M$3:M3,M3),M3))

 

[dotcanada]

Hi Sufiyan. Thanks so much for your help and time in getting me into the right path.

What I would like to do now is display the teams' latest streak into another table next to their respective teams. Each team is listed in a different sheet, that's why they're listed as Teams!A4, etc.

2023-24 NHL Season (2).xlsx
	D	E
3	Team	Streak
4	Boston Bruins
5	Buffalo Sabres
6	Detroit Red Wings
7	Florida Panthers
8	Montréal Canadiens
9	Ottawa Senators
10	Tampa Bay Lightning
11	Toronto Maple Leafs
Calculations

Cell Formulas
Range	Formula
D4:D5	D4	=Teams!A4
D6	D6	=Teams!A12
D7	D7	=Teams!A14
D8	D8	=Teams!A17
D9	D9	=Teams!A22
D10:D11	D10	=Teams!A28

 

[Sufiyan97]

Can you fill in Streak column manually just to show what you want to retrieve.

 

[dotcanada]

Absolutely. Sorry 'bout that.

The table I posted in my initial posting is only a small sample of what I actually have (it has 1314 rows). It is the current NHL season that will have blank cells because those games haven't been played yet.

So when a certain team wins 2 games in a row, I want the cell to display "W2" or if a team loses 4 games, then "L4". Also, if a team has a 3-game wining streak (W3) and then loses their next game, then there should be a L1 displayed.

The values I have under "Streak" are just examples of what I'm wanting to see.

2023-24 NHL Season (2).xlsx
	D	E
3	Team	Streak
4	Boston Bruins	L1
5	Buffalo Sabres	W2
6	Detroit Red Wings	W1
7	Florida Panthers	L1
8	Montréal Canadiens	W1
9	Ottawa Senators	L2
10	Tampa Bay Lightning	L2
11	Toronto Maple Leafs	W3
Calculations

Cell Formulas
Range	Formula
D4:D5	D4	=Teams!A4
D6	D6	=Teams!A12
D7	D7	=Teams!A14
D8	D8	=Teams!A17
D9	D9	=Teams!A22
D10:D11	D10	=Teams!A28

 

[Sufiyan97]

See if this works

Book1
	C	D	E	F
1		Team	Streak
2		Tampa Bay Lightning	W
3		Toronto Maple Leafs	W2
4		Boston Bruins	W2
5		Buffalo Sabres	L
6		Ottawa Senators	W
7		Detroit Red Wings	W
8		Montréal Canadiens	W
9
10
Calculations

Cell Formulas
Range	Formula
E2:E8	E2	=INDEX(Sheet2!$H$3:$H$30,AGGREGATE(14,6,(ROW(Sheet2!$H$3:$H$30)-ROW(Sheet2!$H$3)+1)/(Sheet2!$B$3:$B$30=D2),COUNTIF($D$2:D2,D2)))

 

[dotcanada]

Well, we're getting warmer.

Unfortunately, if I used my end of my sheet in my "Schedule" of $H$1314 and $B$1314 (instead of $H$30 and $B$30), the result I get is 0. I think this is because they're blank cells due to games that haven't been played yet. Is there a way to exclude those?
As you can see, Florida hasn't played yet so it's giving me an error (#NUM!) so that would have to be blank until they play their first game.

Would there also be a way to instead of a cell displaying "L" or "W", could we display "L1" or "W1"?

Thanks again for your time with this.

2023-24 NHL Season (2).xlsx
	D	E
3	Team	Streak
4	Boston Bruins	W2
5	Buffalo Sabres	L
6	Detroit Red Wings	W
7	Florida Panthers	#NUM!
8	Montréal Canadiens	W
9	Ottawa Senators	W
10	Tampa Bay Lightning	W
11	Toronto Maple Leafs	W2
Calculations

Cell Formulas
Range	Formula
D4:D5	D4	=Teams!A4
E4:E11	E4	=INDEX(Schedule!$H$3:$H$30,AGGREGATE(14,6,(ROW(Schedule!$H$3:$H$30)-ROW(Schedule!$H$3)+1)/(Schedule!$B$3:$B$30=D4),COUNTIF($D$4:D4,D4)))
D6	D6	=Teams!A12
D7	D7	=Teams!A14
D8	D8	=Teams!A17
D9	D9	=Teams!A22
D10:D11	D10	=Teams!A28

 

[Eric W]

Using the table from post 1, try:

Book1
	D	E	F	G	H
3	Team	Streak		Helper1	Helper2
4	Boston Bruins	W2		W	0
5	Buffalo Sabres	L2		L	0
6	Detroit Red Wings	W1		W	14
7	Florida Panthers	L2		L	0
8	Montréal Canadiens	W1		W	7
9	Ottawa Senators	W1		W	6
10	Tampa Bay Lightning	L1		L	3
11	Toronto Maple Leafs	W2		W	0
12	New Jersey Devils	L1		OTL	14
Calculations

Cell Formulas
Range	Formula
G4:G12	G4	=INDIRECT("Schedule!"&TEXT(MAX(IFERROR(AGGREGATE(14,6,(ROW(Schedule!$B$3:$B$30)*100+7)/(Schedule!$B$3:$B$30=D4)/(Schedule!$G$4:$G$30<>""),1),0),IFERROR(AGGREGATE(14,6,(ROW(Schedule!$B$3:$B$30)*100+12)/(Schedule!$M$3:$M$30=D4)/(Schedule!$G$4:$G$30<>""),1),0)),"R00C00"),0)
H4:H12	H4	=MAX(IFERROR(AGGREGATE(14,6,ROW(Schedule!$B$3:$B$30)/(Schedule!$B$3:$B$30=D4)/(Schedule!$G$4:$G$30<>"")/(RIGHT(G4)<>RIGHT(Schedule!$G$3:$G$30)),1),0),IFERROR(AGGREGATE(14,6,ROW(Schedule!$B$3:$B$30)/(Schedule!$M$3:$M$30=D4)/(Schedule!$L$4:$L$30<>"")/(RIGHT(G4)<>RIGHT(Schedule!$L$3:$L$30)),1),0))
E4:E12	E4	=RIGHT(G4)&COUNTIFS(INDEX(Schedule!B:B,H4+1):Schedule!$B$30,D4,INDEX(Schedule!G:G,H4+1):Schedule!$G$30,"*"&RIGHT(G4))+COUNTIFS(INDEX(Schedule!M:M,H4+1):Schedule!$M$30,D4,INDEX(Schedule!L:L,H4+1):Schedule!$L$30,"*"&RIGHT(G4))

These formulas are pretty complicated since the team name can show up in column B or M. I needed 2 helper columns. The G formula finds the last status of the team. The H column is the highest row with the team that has a different result than column G. Then the E formula does a COUNTIF in that range of the team and last result. OTL is considered an L for the purposes of this streak.

 

[dotcanada]

Hi Eric.
I take it these formulae you provided are based off the original table in my first post and not Sufiyan's in the second post?

UPDATE: Nevermind. I figured that it is. And thanks so much for your help.

 

[dotcanada]

So close. There's one cell that's not giving me a value that I'm looking for.

In cell AG5, it's giving me a return of L4 when it should be L2. I think what is happening is it's only counting the streaks in "Schedule" column B (I could be wrong). I'm also using the end of my actual data on the "Schedule" sheet (which is row 1314). The first two tables are from the same "Calculations" sheet.

2023-24 NHL Season (2).xlsx
	D
4	Boston Bruins
5	Buffalo Sabres
6	Detroit Red Wings
Calculations

Cell Formulas
Range	Formula
D4:D5	D4	=Teams!A4
D6	D6	=Teams!A12

2023-24 NHL Season (2).xlsx
	AG	AH	AI
3	Streak	Helper 1	Helper 2
4	L1	L	796
5	L4	L	693
6	W1	W	780
Calculations

Cell Formulas
Range	Formula
AG4:AG5	AG4	=RIGHT(AH4)&COUNTIFS(INDEX(Schedule!B:B,AI4+1):Schedule!$B$1314,Teams!A4,INDEX(Schedule!G:G,AI4+1):Schedule!$G$1314,"*"&RIGHT(AH4))+COUNTIFS(INDEX(Schedule!M:M,AI4+1):Schedule!$M$1314,Teams!A4,INDEX(Schedule!L:L,AI4+1):Schedule!$L$1314,"*"&RIGHT(AH4))
AH4:AH5	AH4	=INDIRECT("Schedule!"&TEXT(MAX(IFERROR(AGGREGATE(14,6,(ROW(Schedule!$B$3:$B$1314)*100+7)/(Schedule!$B$3:$B$1314=Teams!A4)/(Schedule!$G$4:$G$1314<>""),1),0),IFERROR(AGGREGATE(14,6,(ROW(Schedule!$B$3:$B$1314)*100+12)/(Schedule!$L$3:$L$1314=Teams!A4)/(Schedule!$G$4:$G$1314<>""),1),0)),"R00C00"),0)
AI4:AI5	AI4	=MAX(IFERROR(AGGREGATE(14,6,ROW(Schedule!$B$3:$B$1314)/(Schedule!$B$3:$B$1314=Teams!A4)/(Schedule!$G$4:$G$1314<>"")/(RIGHT(AH4)<>RIGHT(Schedule!$G$3:$G$1314)),1),0),IFERROR(AGGREGATE(14,6,ROW(Schedule!$B$3:$B$1314)/(Schedule!$L$3:$L$314=Teams!A4)/(Schedule!$K$4:$K$1314<>"")/(RIGHT(AH4)<>RIGHT(Schedule!$K$3:$K$1314)),1),0))
AG6	AG6	=RIGHT(AH6)&COUNTIFS(INDEX(Schedule!B:B,AI6+1):Schedule!$B$1314,Teams!A12,INDEX(Schedule!G:G,AI6+1):Schedule!$G$1314,"*"&RIGHT(AH6))+COUNTIFS(INDEX(Schedule!M:M,AI6+1):Schedule!$M$1314,Teams!A12,INDEX(Schedule!L:L,AI6+1):Schedule!$L$1314,"*"&RIGHT(AH6))
AH6	AH6	=INDIRECT("Schedule!"&TEXT(MAX(IFERROR(AGGREGATE(14,6,(ROW(Schedule!$B$3:$B$1314)*100+7)/(Schedule!$B$3:$B$1314=Teams!A12)/(Schedule!$G$4:$G$1314<>""),1),0),IFERROR(AGGREGATE(14,6,(ROW(Schedule!$B$3:$B$1314)*100+12)/(Schedule!$L$3:$L$1314=Teams!A12)/(Schedule!$G$4:$G$1314<>""),1),0)),"R00C00"),0)
AI6	AI6	=MAX(IFERROR(AGGREGATE(14,6,ROW(Schedule!$B$3:$B$1314)/(Schedule!$B$3:$B$1314=Teams!A12)/(Schedule!$G$4:$G$1314<>"")/(RIGHT(AH6)<>RIGHT(Schedule!$G$3:$G$1314)),1),0),IFERROR(AGGREGATE(14,6,ROW(Schedule!$B$3:$B$1314)/(Schedule!$L$3:$L$314=Teams!A12)/(Schedule!$K$4:$K$1314<>"")/(RIGHT(AH6)<>RIGHT(Schedule!$K$3:$K$1314)),1),0))

The following table is where I'm getting the team names from:

2023-24 NHL Season (2).xlsx
	A
2	Anaheim Ducks
3	Arizona Coyotes
4	Boston Bruins
5	Buffalo Sabres
6	Calgary Flames
7	Carolina Hurricanes
8	Chicago Blackhawks
9	Colorado Avalanche
10	Columbus Blue Jackets
11	Dallas Stars
12	Detroit Red Wings
13	Edmonton Oilers
Teams