Display streak of wins or losses

dotcanada

Board Regular
Joined
Dec 13, 2015
Messages
52
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLM
1HomeVisitor
2DateTeamScoreOTRWROWW/L/OTLScoreOTRWROWW/L/OTLTeam
3Tuesday, October 10, 2023Tampa Bay Lightning5xxW3LNashville Predators
4Pittsburgh Penguins2L4xxWChicago Blackhawks
5Vegas Golden Knights4xxW1LSeattle Kraken
6Wednesday, October 11, 2023Carolina Hurricanes5xxW3LOttawa Senators
7Toronto Maple Leafs6W5xOTLMontréal Canadiens
8Boston Bruins3xxW1LChicago Blackhawks
9Calgary Flames5xxW3LWinnipeg Jets
10Los Angeles Kings2L5xxWColorado Avalanche
11Vancouver Canucks8xxW1LEdmonton Oilers
12Thursday, October 12, 2023Buffalo Sabres1L5xxWNew York Rangers
13Columbus Blue Jackets2L4xxWPhiladelphia Flyers
14New Jersey Devils4xxW3LDetroit Red Wings
15Dallas Stars2W1xOTLSt. Louis Blues
16Minnesota Wild2xxW0LFlorida Panthers
17Nashville Predators3xxW0LSeattle Kraken
18San Jose Sharks1L4xxWVegas Golden Knights
19Friday, October 13, 2023New Jersey Devils3xOTL4WArizona Coyotes
20Washington Capitals0L4xxWPittsburgh Penguins
21Saturday, October 14, 2023Ottawa Senators5xxW2LPhiladelphia Flyers
22Winnipeg Jets6xxW4LFlorida Panthers
23Boston Bruins3xxW2LNashville Predators
24Columbus Blue Jackets5xxW3LNew York Rangers
25Detroit Red Wings6xxW4LTampa Bay Lightning
26Montréal Canadiens3xxW2LChicago Blackhawks
27Pittsburgh Penguins5xxW2LCalgary Flames
28Toronto Maple Leafs7xxW4LMinnesota Wild
29New York Islanders3xxW2LBuffalo Sabres
30St. Louis Blues2W1xOTLSeattle Kraken
Schedule
Cell Formulas
RangeFormula
G3:G30G3=IF(C3>H3,"W",IF(D3="x","OTL",IF(C3<H3,"L","")))
L3:L30L3=IF(C3<H3,"W",IF(I3="x","OTL",IF(C3>H3,"L","")))
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Do you want something like


Book1
ABCDEFGHIJKLMNOP
1HomeVisitor
2DateTeamScoreOTRWROWW/L/OTLW/L/OTL CountScoreOTRWROWW/L/OTLW/L/OTL CountTeam
3Tuesday, October 10, 2023Tampa Bay Lightning5xxWW3LLNashville Predators
4Pittsburgh Penguins2LL4xxWWChicago Blackhawks
5Vegas Golden Knights4xxWW1LLSeattle Kraken
6Wednesday, October 11, 2023Carolina Hurricanes5xxWW3LLOttawa Senators
7Toronto Maple Leafs6WW5xOTLOTLMontréal Canadiens
8Boston Bruins3xxWW1LLChicago Blackhawks
9Calgary Flames5xxWW3LLWinnipeg Jets
10Los Angeles Kings2LL5xxWWColorado Avalanche
11Vancouver Canucks8xxWW1LLEdmonton Oilers
12Thursday, October 12, 2023Buffalo Sabres1LL5xxWWNew York Rangers
13Columbus Blue Jackets2LL4xxWWPhiladelphia Flyers
14New Jersey Devils4xxWW3LLDetroit Red Wings
15Dallas Stars2WW1xOTLOTLSt. Louis Blues
16Minnesota Wild2xxWW0LLFlorida Panthers
17Nashville Predators3xxWW0LL2Seattle Kraken
18San Jose Sharks1LL4xxWWVegas Golden Knights
19Friday, October 13, 2023New Jersey Devils3xOTLOTL4WWArizona Coyotes
20Washington Capitals0LL4xxWWPittsburgh Penguins
21Saturday, October 14, 2023Ottawa Senators5xxWW2LLPhiladelphia Flyers
22Winnipeg Jets6xxWW4LL2Florida Panthers
23Boston Bruins3xxWW22LL2Nashville Predators
24Columbus Blue Jackets5xxWW3LLNew York Rangers
25Detroit Red Wings6xxWW4LLTampa Bay Lightning
26Montréal Canadiens3xxWW2LL2Chicago Blackhawks
27Pittsburgh Penguins5xxWW2LLCalgary Flames
28Toronto Maple Leafs7xxWW24LLMinnesota Wild
29New York Islanders3xxWW2LLBuffalo Sabres
30St. Louis Blues2WW1xOTLOTLSeattle Kraken
31
32
Sheet2
Cell Formulas
RangeFormula
G3:G30G3=IF(C3>I3,"W",IF(D3="x","OTL",IF(C3<I3,"L","")))
H3:H30H3=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:M30M3=IF(C3<I3,"W",IF(J3="x","OTL",IF(C3>I3,"L","")))
N3:N30N3=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))
 
Upvote 0
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.

Cell Formulas
RangeFormula
D4:D5D4=Teams!A4
D6D6=Teams!A12
D7D7=Teams!A14
D8D8=Teams!A17
D9D9=Teams!A22
D10:D11D10=Teams!A28
 
Upvote 0
Can you fill in Streak column manually just to show what you want to retrieve.
 
Upvote 0
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.

Cell Formulas
RangeFormula
D4:D5D4=Teams!A4
D6D6=Teams!A12
D7D7=Teams!A14
D8D8=Teams!A17
D9D9=Teams!A22
D10:D11D10=Teams!A28
 
Upvote 0
See if this works

Book1
CDEF
1TeamStreak
2Tampa Bay LightningW
3Toronto Maple LeafsW2
4Boston BruinsW2
5Buffalo SabresL
6Ottawa SenatorsW
7Detroit Red WingsW
8Montréal CanadiensW
9
10
Calculations
Cell Formulas
RangeFormula
E2:E8E2=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)))
 
Upvote 0
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.

Cell Formulas
RangeFormula
D4:D5D4=Teams!A4
E4:E11E4=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)))
D6D6=Teams!A12
D7D7=Teams!A14
D8D8=Teams!A17
D9D9=Teams!A22
D10:D11D10=Teams!A28
 
Upvote 0
Using the table from post 1, try:

Book1
DEFGH
3TeamStreakHelper1Helper2
4Boston BruinsW2W0
5Buffalo SabresL2L0
6Detroit Red WingsW1W14
7Florida PanthersL2L0
8Montréal CanadiensW1W7
9Ottawa SenatorsW1W6
10Tampa Bay LightningL1L3
11Toronto Maple LeafsW2W0
12New Jersey DevilsL1OTL14
Calculations
Cell Formulas
RangeFormula
G4:G12G4=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:H12H4=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:E12E4=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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
4Boston Bruins
5Buffalo Sabres
6Detroit Red Wings
Calculations
Cell Formulas
RangeFormula
D4:D5D4=Teams!A4
D6D6=Teams!A12


2023-24 NHL Season (2).xlsx
AGAHAI
3StreakHelper 1Helper 2
4L1L796
5L4L693
6W1W780
Calculations
Cell Formulas
RangeFormula
AG4:AG5AG4=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:AH5AH4=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:AI5AI4=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))
AG6AG6=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))
AH6AH6=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)
AI6AI6=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
2Anaheim Ducks
3Arizona Coyotes
4Boston Bruins
5Buffalo Sabres
6Calgary Flames
7Carolina Hurricanes
8Chicago Blackhawks
9Colorado Avalanche
10Columbus Blue Jackets
11Dallas Stars
12Detroit Red Wings
13Edmonton Oilers
Teams
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top