Winning a Loosing Streak

wladoD

New Member
Joined
Sep 18, 2017
Messages
5
Hi Guys,

I do have a question about winning and loosing streak in my hockey league.

I have data where player can play home or away.
What would be the most efficient way to count winning and loosing streak?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
If you have a column to record Win/Lose then you can use something like this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Result</td><td style=";">Streak</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Win</td><td style=";">Win 1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Win</td><td style=";">Win 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Lose</td><td style=";">Lose 1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Lose</td><td style=";">Lose 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Lose</td><td style=";">Lose 3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Win</td><td style=";">Win 1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Win</td><td style=";">Win 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Win</td><td style=";">Win 3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Lose</td><td style=";">Lose 1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Win</td><td style=";">Win 1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Win</td><td style=";">Win 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Lose</td><td style=";">Lose 1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Lose</td><td style=";">Lose 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Lose</td><td style=";">Lose 3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Lose</td><td style=";">Lose 4</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">{=$A2&" "&(<font color="Blue">ROW(<font color="Red">$B2</font>)-MAX(<font color="Red">IF(<font color="Green">$A$1:$A1<>$A2,ROW(<font color="Purple">$A$1:$A1</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

WBD
 

wladoD

New Member
Joined
Sep 18, 2017
Messages
5
 

wladoD

New Member
Joined
Sep 18, 2017
Messages
5
My data is bit more complicated.

Player can play away and home matches.

Outcomes for results are WRT - WOT - WPP these are all Wins
LRT , LOT, LPP these are all Loses
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401

ADVERTISEMENT

With the level of complication, I think you'll need VBA and not a formula:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #8EA9DB;background-color: #000000;;">ID</td><td style="font-weight: bold;text-align: center;color: #8EA9DB;background-color: #000000;;">ROUND</td><td style="font-weight: bold;text-align: center;color: #8EA9DB;background-color: #000000;;">TEAM 1</td><td style="font-weight: bold;text-align: center;color: #8EA9DB;background-color: #000000;;">PLAYER 1</td><td style="font-weight: bold;text-align: center;color: #8EA9DB;background-color: #000000;;"></td><td style="font-weight: bold;text-align: center;color: #8EA9DB;background-color: #000000;;">H</td><td style="font-weight: bold;text-align: center;color: #8EA9DB;background-color: #000000;;">A</td><td style="font-weight: bold;text-align: center;color: #8EA9DB;background-color: #000000;;">R</td><td style="font-weight: bold;text-align: center;color: #8EA9DB;background-color: #000000;;"></td><td style="font-weight: bold;text-align: center;color: #8EA9DB;background-color: #000000;;">PLAYER 2</td><td style="font-weight: bold;text-align: center;color: #8EA9DB;background-color: #000000;;">TEAM 2</td><td style="font-weight: bold;text-align: center;color: #8EA9DB;background-color: #000000;;">DATE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">3</td><td style=";">1. Round</td><td style=";">Edmonton Oilers</td><td style="color: #4472C4;;">Tosino23</td><td style="color: #4472C4;background-color: #A9D08E;;">WRT</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style=";">RT</td><td style="color: #4472C4;background-color: #A9D08E;;">LRT</td><td style="color: #4472C4;;">wladoD</td><td style=";">Edmonton Oilers</td><td style="text-align: right;;">22-Jul-17</td><td style="text-align: right;;"></td><td style=";">Tosino23</td><td style="text-align: right;;">-2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">44</td><td style=";">Quaterfinal</td><td style=";">Anaheim Ducks</td><td style="color: #4472C4;;">alphafxdb</td><td style="color: #4472C4;background-color: #A9D08E;;">LRT</td><td style="text-align: right;;">4</td><td style="text-align: right;;">6</td><td style=";">RT</td><td style="color: #4472C4;background-color: #A9D08E;;">WRT</td><td style="color: #4472C4;;">dawo043</td><td style=";">St. Louis Blues</td><td style="text-align: right;;">23-Jul-17</td><td style="text-align: right;;"></td><td style=";">wladoD</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">45</td><td style=";">Quaterfinal</td><td style=";">Edmonton Oilers</td><td style="color: #4472C4;;">Tosino23</td><td style="color: #4472C4;background-color: #A9D08E;;">LRT</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style=";">RT</td><td style="color: #4472C4;background-color: #A9D08E;;">WRT</td><td style="color: #4472C4;;">wladoD</td><td style=";">Chicago Blackhawks</td><td style="text-align: right;;">24-Jul-17</td><td style="text-align: right;;"></td><td style=";">alphafxdb</td><td style="text-align: right;;">-3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">46</td><td style=";">Quaterfinal</td><td style=";">St. Louis Blues</td><td style="color: #4472C4;;">dawo043</td><td style="color: #4472C4;background-color: #A9D08E;;">WRT</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style=";">RT</td><td style="color: #4472C4;background-color: #A9D08E;;">LRT</td><td style="color: #4472C4;;">alphafxdb</td><td style=";">Anaheim Ducks</td><td style="text-align: right;;">25-Jul-17</td><td style="text-align: right;;"></td><td style=";">dawo043</td><td style="text-align: right;;">-3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">47</td><td style=";">Quaterfinal</td><td style=";">Chicago Blackhawks</td><td style="color: #4472C4;;">wladoD</td><td style="color: #4472C4;background-color: #A9D08E;;">WRT</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style=";">RT</td><td style="color: #4472C4;background-color: #A9D08E;;">LRT</td><td style="color: #4472C4;;">Tosino23</td><td style=";">Edmonton Oilers</td><td style="text-align: right;;">26-Jul-17</td><td style="text-align: right;;"></td><td style=";">Biculinho</td><td style="text-align: right;;">-1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">48</td><td style=";">Quaterfinal</td><td style=";">Anaheim Ducks</td><td style="color: #4472C4;;">alphafxdb</td><td style="color: #4472C4;background-color: #A9D08E;;">LPP</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style=";">PP</td><td style="color: #4472C4;background-color: #A9D08E;;">WPP</td><td style="color: #4472C4;;">dawo043</td><td style=";">St. Louis Blues</td><td style="text-align: right;;">27-Jul-17</td><td style="text-align: right;;"></td><td style=";">FlatOut3232</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">49</td><td style=";">Semifinal</td><td style=";">Washington Capitals</td><td style="color: #4472C4;;">Biculinho</td><td style="color: #4472C4;background-color: #A9D08E;;">LRT</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style=";">RT</td><td style="color: #4472C4;background-color: #A9D08E;;">WRT</td><td style="color: #4472C4;;">wladoD</td><td style=";">Chicago Blackhawks</td><td style="text-align: right;;">28-Jul-17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">50</td><td style=";">Semifinal</td><td style=";">Nashville Predators</td><td style="color: #4472C4;;">FlatOut3232</td><td style="color: #4472C4;background-color: #A9D08E;;">WRT</td><td style="text-align: right;;">6</td><td style="text-align: right;;">2</td><td style=";">RT</td><td style="color: #4472C4;background-color: #A9D08E;;">LRT</td><td style="color: #4472C4;;">dawo043</td><td style=";">St. Louis Blues</td><td style="text-align: right;;">29-Jul-17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">51</td><td style=";">Semifinal</td><td style=";">Chicago Blackhawks</td><td style="color: #4472C4;;">wladoD</td><td style="color: #4472C4;background-color: #A9D08E;;">WRT</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style=";">RT</td><td style="color: #4472C4;background-color: #A9D08E;;">LRT</td><td style="color: #4472C4;;">Biculinho</td><td style=";">Washington Capitals</td><td style="text-align: right;;">30-Jul-17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">52</td><td style=";">Semifinal</td><td style=";">St. Louis Blues</td><td style="color: #4472C4;;">dawo043</td><td style="color: #4472C4;background-color: #A9D08E;;">LRT</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style=";">RT</td><td style="color: #4472C4;background-color: #A9D08E;;">WRT</td><td style="color: #4472C4;;">FlatOut3232</td><td style=";">Nashville Predators</td><td style="text-align: right;;">31-Jul-17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">53</td><td style=";">Semifinal</td><td style=";">Washington Capitals</td><td style="color: #4472C4;;">Biculinho</td><td style="color: #4472C4;background-color: #A9D08E;;">WRT</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style=";">RT</td><td style="color: #4472C4;background-color: #A9D08E;;">LRT</td><td style="color: #4472C4;;">wladoD</td><td style=";">Chicago Blackhawks</td><td style="text-align: right;;">01-Aug-17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">54</td><td style=";">Semifinal</td><td style=";">Nashville Predators</td><td style="color: #4472C4;;">FlatOut3232</td><td style="color: #4472C4;background-color: #A9D08E;;">WRT</td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style=";">RT</td><td style="color: #4472C4;background-color: #A9D08E;;">LRT</td><td style="color: #4472C4;;">dawo043</td><td style=";">St. Louis Blues</td><td style="text-align: right;;">02-Aug-17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">55</td><td style=";">Semifinal</td><td style=";">Chicago Blackhawks</td><td style="color: #4472C4;;">wladoD</td><td style="color: #4472C4;background-color: #A9D08E;;">WRT</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style=";">RT</td><td style="color: #4472C4;background-color: #A9D08E;;">LRT</td><td style="color: #4472C4;;">Biculinho</td><td style=";">Washington Capitals</td><td style="text-align: right;;">03-Aug-17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />

Code:
Public Sub FindStreaks()

Dim lastPlayer As Long
Dim thisPlayer As Long
Dim lastRow As Long
Dim thisRow As Long
Dim lastResult As String
Dim thisResult As String
Dim currentStreak As Long

' Find the last row of data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

' Find the last row of player names
lastPlayer = Cells(Rows.Count, "N").End(xlUp).Row

' Process each player
For thisPlayer = 2 To lastPlayer
    ' Blank out the last result and streak
    lastResult = ""
    currentStreak = 0
    
    ' Process the data rows from bottom to top
    For thisRow = lastRow To 2 Step -1
        ' Determine whether the current player won, lost or didn't play
        thisResult = ""
        If Cells(thisRow, "D").Value = Cells(thisPlayer, "N").Value Then
            If Left$(Cells(thisRow, "E").Value, 1) = "W" Then
                thisResult = "W"
            Else
                thisResult = "L"
            End If
        ElseIf Cells(thisRow, "J").Value = Cells(thisPlayer, "N").Value Then
            If Left$(Cells(thisRow, "I").Value, 1) = "W" Then
                thisResult = "W"
            Else
                thisResult = "L"
            End If
        End If
        
        ' If this player played then either accumulate or terminate the streak
        If thisResult <> "" Then
            If lastResult = "" Then
                lastResult = thisResult
                currentStreak = 1
            Else
                If thisResult = lastResult Then
                    currentStreak = currentStreak + 1
                Else
                    Exit For
                End If
            End If
        End If
    Next thisRow
    
    ' Choose one of the lines below
    Cells(thisPlayer, "O").Value = CStr(currentStreak) & " " & lastResult
    Cells(thisPlayer, "O").Value = currentStreak * IIf(lastResult = "L", -1, 1)
Next thisPlayer

End Sub

WBD
 

wladoD

New Member
Joined
Sep 18, 2017
Messages
5
Can you please advice where to add VBA code and how will it work?
Where will I see the result?
 

wladoD

New Member
Joined
Sep 18, 2017
Messages
5
Team1
WRT
LRT
LRT
LRT
WRT
WRT
LRT
LRT
LRT
LRT

<colgroup><col span="2"></colgroup><tbody>
</tbody>

I am able to dynamically extract the last matches played. would it be possible to use regular formula?
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
For the VBA, press [Alt]+[F11] to bring up the editor, select Insert->Module and paste the code above. Close the editor and go back to the sheet. Press [Alt]+[F8] to select and run the macro.

WBD
 

Watch MrExcel Video

Forum statistics

Threads
1,109,275
Messages
5,527,728
Members
409,785
Latest member
AdamPriest

This Week's Hot Topics

Top