Counting a sequence

  • Thread starter Thread starter Neil Grantham
  • Start date Start date
N

Neil Grantham

I wonder if it is possible to count a best sequence of Wins

My spreadsheet has a column that I enter either Win, Loss or Draw.

I would like to show the best sequence of wins over a whole season, so
not necessarily the last sequence of wins, if that makes sense.

Ideally I'd also like to count the longest unbeaten sequence, given
that both a Win and a Draw are 'unbeaten'
I.E. I might have a sequence that is W W W D W W D L L W W
For the firs part of this email, I want to return 3 as the best Win
sequence, whilst the latter part would return an unbeaten sequence of
7

Possible?

Thanks
Neil
 
Neil,

Here is one method that employs two helper columns to
accomplish your goal:

Say your column with wins and losses was column A
for examples sake say A1:A100

In B1 put =IF(A1<>"L", 1,0)
In B2 put =IF(A2<>"L", B1+1,0) and drag down as far as
you data goes

In C1 put =IF(A1="W",1,0)
In C2 put =IF(A2="W",C1+1,0) and drag down

To get the longest unbeaten streak
=MAX(B1:B100)
To get the longest winning streak
=MAX(C1:C100)

Dan E
 
Hi Neil,

If you want to try using VBA, the code below will work
too. You will need to change the "Sheet1" and "C1"
references to your worksheet name, and the first cell of
the column that contains the W,D,L, information.

Hope this helps.

Regards,
James S

For the code below, my example looked like this:

A B C
1 W
2 L
3 W
4 D
5 W
Etc.

Sub WinLossStreak()
Dim wks As Worksheet
Dim rng As Range
Dim rngBeginCheckingHere As Range
Dim intWinStreak As Integer
Dim intBestStreak As Integer

Set wks = ThisWorkbook.Worksheets("Sheet1")
Set rngBeginCheckingHere = wks.Range("C1")

intWinStreak = 0
For Each rng In wks.Range(rngBeginCheckingHere, _
Cells(Rows.Count, rngBeginCheckingHere.Column).End
(xlUp))
If rng.Value = "W" Or rng.Value = "D" Then
intWinStreak = intWinStreak + 1
' Check the current win streak vs.
' the best win streak
If intBestStreak < intWinStreak Then
intBestStreak = intWinStreak
End If
Else
' Current record is a Loss, reset
' the win streak.
intWinStreak = 0
End If
Next rng
MsgBox intBestStreak
End Sub
 
Back
Top