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","")))

 

[dotcanada]

So good news and bad news. The good news is that one problem was is that originally when I used your formulae (Eric's), I was using it on a table that was formatted that included Sufiyan's table in post #2. I got rid of those extra columns and reverted it back to my original table (in post #1). I tried it with that and (the bad news), it still giving be results that are incorrect.

For simplicity's sake, instead of using "Teams!A4" to refer to a team on another sheet, I'm using the direct cell on the sheet we're working on (ex. D4). Here are the results I'm getting:

2023-24 NHL Season (2).xlsx
	D
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

2023-24 NHL Season (2).xlsx
	AG	AH	AI
4	L1	L	796
5	L2	L	764
6	W1	W	780
7	W2	W	788
8	L2	L	791
9	W2	W	767
10	W0	W	800
11	L0	L	794
Calculations

Cell Formulas
Range	Formula
AG4:AG11	AG4	=RIGHT(AH4)&COUNTIFS(INDEX(Schedule!B:B,AI4+1):Schedule!$B$1314,D4,INDEX(Schedule!G:G,AI4+1):Schedule!$G$1314,"*"&RIGHT(AH4))+COUNTIFS(INDEX(Schedule!M:M,AI4+1):Schedule!$M$1314,D4,INDEX(Schedule!L:L,AI4+1):Schedule!$L$1314,"*"&RIGHT(AH))
AH4:AH11	AH4	=INDIRECT("Schedule!"&TEXT(MAX(IFERROR(AGGREGATE(14,6,(ROW(Schedule!$B$3:$B$1314)*100+7)/(Schedule!$B$3:$B$1314=D4)/(Schedule!$G$3:$G$1314<>""),1),0),IFERROR(AGGREGATE(14,6,(ROW(Schedule!$B$3:$B$1314)*100+12)/(Schedule!$M$3:$M$1314=D4)/(Schedule!$G$3:$G$1314<>""),1),0)),"R00C00"),0)
AI4:AI11	AI4	=MAX(IFERROR(AGGREGATE(14,6,ROW(Schedule!$B$3:$B$1314)/(Schedule!$B$3:$B$1314=D4)/(Schedule!$G$3:$G$1314<>"")/(RIGHT(AH4)<>RIGHT(Schedule!$G$3:$G$1314)),1),0),IFERROR(AGGREGATE(14,6,ROW(Schedule!$B$3:$B$1314)/(Schedule!$M$3:$M$1314=D4)/(Schedule!$L$3:$L$1314<>"")/(RIGHT(AH4)<>RIGHT(Schedule!$L$3:$L$1314)),1),0))

So in the results above, in cell AG9, I should be getting a value of W3. I have to figure out why. But in cells AG10 and AG11, it's giving me a return of W0 and L0 and I don't know why.

 

[dotcanada]

Argh, I'm such an idiot. So I figured out the main problem. At the end of your formula that I have in AG4, I didn't realize I had only the column and not the row (I had (AH) instead of (AH4).
Sorry about that.

Now, as you mentioned in your initial post (Eric), for the purposes of this streak, I have "OTL" counting as an "L". What if I wanted "OTL"s to count as themselves, then reset if the next game was a regular "L"? So for example, a team has OTL1 as a streak. Then they get a regular loss ("L") in their next game which would reset the streak to "L1".

Thanks so much for your help. You area a Yoda amongst padawan excel users like myself.

 

[Eric W]

Good job figuring out the issues! If you want OTL to be its own streak, you basically just have to remove RIGHT() from around the ranges, and the "*" from the COUNTIFS. So the Helper1 doesn't change, but the Helper2 and the main formulas do. Like:

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	OTL1		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<>"")/(G4<>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<>"")/(G4<>Schedule!$L$3:$L$30),1),0))
E4:E12	E4	=G4&COUNTIFS(INDEX(Schedule!B:B,H4+1):Schedule!$B$30,D4,INDEX(Schedule!G:G,H4+1):Schedule!$G$30,G4)+COUNTIFS(INDEX(Schedule!M:M,H4+1):Schedule!$M$30,D4,INDEX(Schedule!L:L,H4+1):Schedule!$L$30,G4)

 

[dotcanada]

Perfect!

Thanks so much for all your time and help. Cheers.

 

[dotcanada]

Hi again Eric.

Just out of curiosity, in H4, you have in two areas, "Schedule!$G$4:$G$30" and "Schedule!$L$4:$L$30". Why is it "$G$4" and "$L$4" and not "$G$3" and "$L$3"?

 

[Eric W]

Hi again Eric.

Just out of curiosity, in H4, you have in two areas, "Schedule!$G$4:$G$30" and "Schedule!$L$4:$L$30". Why is it "$G$4" and "$L$4" and not "$G$3" and "$L$3"?

Ugh! Good eye! Yes, that's a mistake. They all should be $x$3:$x$30. That happens in both the G and H formulas. Based on where they are in the formulas, the formulas should still mostly work ok. But they could also cause some very subtle errors that would be hard to find.

 

[dotcanada]

Ah ok. I probably would have really noticed when the new season starts next fall.

So far, everything is working fantastically. Thanks again. Cheers.