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

Cell Formulas
RangeFormula
D4:D5D4=Teams!A4
D6D6=Teams!A12
D7D7=Teams!A14
D8D8=Teams!A17
D9D9=Teams!A22
D10:D11D10=Teams!A28

2023-24 NHL Season (2).xlsx
AGAHAI
4L1L796
5L2L764
6W1W780
7W2W788
8L2L791
9W2W767
10W0W800
11L0L794
Calculations
Cell Formulas
RangeFormula
AG4:AG11AG4=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:AH11AH4=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:AI11AI4=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.
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
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
DEFGH
3TeamStreakHelper1Helper2
4Boston BruinsW2W0
5Buffalo SabresL2L0
6Detroit Red WingsW1W14
7Florida PanthersL2L0
8Montréal CanadiensW1W7
9Ottawa SenatorsW1W6
10Tampa Bay LightningL1L3
11Toronto Maple LeafsW2W0
12New Jersey DevilsOTL1OTL14
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<>"")/(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:E12E4=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)
 
Upvote 0
Solution
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"?
 
Upvote 0
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.
 
Upvote 0
Ah ok. I probably would have really noticed when the new season starts next fall.

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

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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