longet Win streak

[ParTeeGolfer]

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]

On Sat, 8 Jan 2005 17:41:02 -0800, ParTeeGolfer
<[email protected]> wrote:

>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/...fe0c55d264800d

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

[email protected]

"ParTeeGolfer" <[email protected]> wrote in message
news:[email protected]...
| 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!

[ParTeeGolfer]

Ron,

This helped out and did exactly what I needed.
Thanks for the help!!
"Ron Rosenfeld" wrote:

> On Sat, 8 Jan 2005 17:41:02 -0800, ParTeeGolfer
> <[email protected]> wrote:
>
> >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/...fe0c55d264800d
>
> 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
>

[Harlan Grove]

"Ron Rosenfeld" <[email protected]> wrote...
....
>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]

On Sat, 8 Jan 2005 23:29:49 -0800, "Harlan Grove" <[email protected]> wrote:

>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]

On Sat, 8 Jan 2005 19:29:02 -0800, ParTeeGolfer
<[email protected]> wrote:

>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