longet Win streak

[Guest]

Ok, I am tring to figure out how to automaticlly keep track of a column with
wins and losses in it containing "L" or "W" and detrmine the longest winning
streak

EXAMPLE:

W
L
W
W
W
W
L
W
W
L

By looking at the column I can tell that the longest winning streak is 4.
How can I create a formula to keep track of this automatically?

Any Suggestions?

Please Help!

 

[Ron Rosenfeld]

Ok, I am tring to figure out how to automaticlly keep track of a column with
wins and losses in it containing "L" or "W" and detrmine the longest winning
streak

EXAMPLE:

W
L
W
W
W
W
L
W
W
L

By looking at the column I can tell that the longest winning streak is 4.
How can I create a formula to keep track of this automatically?

Any Suggestions?

Please Help!

A formula solution is pretty complicated. You can adapt Harlan's solution in
the thread at
http://groups-beta.google.com/group....functions/browse_frm/thread/eafe0c55d264800d

A UDF might be simpler. To enter this, <alt><F11> opens the VB editor. Ensure
your project is highlighted in the project explorer, then Insert/Module and
paste the code below into the window that opens.

To use this UDF, enter the formula =MAXWINSTREAK(A1:A10) (or some other range)
into some cell. Read the answer 4 from your example.

=========================
Function MaxWinStreak(rg As Range) As Integer
Const Wins As String = "W"
Dim c As Range
Dim TempWins As Integer

For Each c In rg
If c.Text = Wins Then
TempWins = TempWins + 1
Else
If TempWins > MaxWinStreak Then MaxWinStreak = TempWins
TempWins = 0
End If
Next c

End Function
========================


--ron

 

[William]

Hi

Perhaps you could use a function similar to the one below - copy the code
into a general module of the relevant workbook. I assume your list is in
column A. Then in any cell enter =longeststreak() to return the figure.

Function longeststreak()
Application.Volatile
Dim r As Range, c As Range, l As Long, i As Long
i = 0
l = 0
With ActiveSheet
Set r = .Range(.Range("A1"), _
..Range("A" & Rows.Count).End(xlUp))
For Each c In r
If c = "W" Then
i = i + 1
Else
i = 0
End If
If i > l Then l = i
Next c
End With
longeststreak = l
End Function

--
XL2002
Regards

William

(e-mail address removed)

| Ok, I am tring to figure out how to automaticlly keep track of a column
with
| wins and losses in it containing "L" or "W" and detrmine the longest
winning
| streak
|
| EXAMPLE:
|
| W
| L
| W
| W
| W
| W
| L
| W
| W
| L
|
| By looking at the column I can tell that the longest winning streak is
4.
| How can I create a formula to keep track of this automatically?
|
| Any Suggestions?
|
| Please Help!

 

[Guest]

Ron,

This helped out and did exactly what I needed.
Thanks for the help!!

 

[Harlan Grove]

Function MaxWinStreak(rg As Range) As Integer
Const Wins As String = "W"
Dim c As Range
Dim TempWins As Integer

For Each c In rg
If c.Text = Wins Then
TempWins = TempWins + 1
Else
If TempWins > MaxWinStreak Then MaxWinStreak = TempWins
TempWins = 0
End If
Next c

End Function

If every cell is a win, this function returns zero. If you're going to use
this approach, you have to add another

If TempWins > MaxWinStreak Then MaxWinStreak = TempWins

statement after the For loop.

 

[Ron Rosenfeld]

If every cell is a win, this function returns zero. If you're going to use
this approach, you have to add another

If TempWins > MaxWinStreak Then MaxWinStreak = TempWins

statement after the For loop.

Good catch. Thanks.
--ron

 

[Ron Rosenfeld]

Ron,

This helped out and did exactly what I needed.
Thanks for the help!!

You're welcome. But see Harlan's note and correct the UDF as follows to take
care of the situation where there are all W's:

===========================
Function MaxWinStreak(rg As Range) As Integer
Const Wins As String = "W"
Dim c As Range
Dim TempWins As Integer

For Each c In rg
If c.Text = Wins Then
TempWins = TempWins + 1
Else
If TempWins > MaxWinStreak Then MaxWinStreak = TempWins
TempWins = 0
End If
Next c

If TempWins > MaxWinStreak Then MaxWinStreak = TempWins

End Function
==========================


--ron