Using the column below, what is the best way to track a winning streak
automatically. Keep in mind the column will get longer with data, weather it
be with a "W" or an "L". It Seems to me the best way would be to start at the
bottom of the list and count the "W"'s till I come to an "L" however I am
stumped on how to accomplish this




Win/loss
W
L
W
W
W
W
W
W
W
W
W
W
W
W
W
W
W
W
W
W
W
W

On Fri, 31 Dec 2004 07:37:04 -0800, ParTeeGolfer
wrote:

Using the column below, what is the best way to track a winning streak
automatically. Keep in mind the column will get longer with data, weather it
be with a "W" or an "L". It Seems to me the best way would be to start at the
bottom of the list and count the "W"'s till I come to an "L" however I am
stumped on how to accomplish this

If I understand you correctly, you want to keep track of how long your current
winning streak has been on going.

If that is the case, it is relatively trivial to do in VBA with a User Defined
Function (UDF).

Assume your list of W and L are in a range named Win_Loss.
Assume the only valid entries in this range are "W", "L" or a blank cell.
Assume that the W's and L's are contiguous.

<alt<F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

Enter the UDF

=winstrk(Win_loss)

in some cell. Win_loss can be a named range, or a range reference such as
A2:A100.

The most recent number of wins will be displayed.

==========================
Function WinStrk(WL As Range) As Long
Dim i As Long
Dim c As Range

For i = WL.Count To 1 Step -1
If WL(i, 1) = "L" Then Exit Function
If WL(i, 1) = "W" Then WinStrk = WinStrk + 1
Next i

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






--ron

In addition, using Ron's UDF, you could keep a running total of the wins
in an adjacent column.

In B2 enter =IF(A2="W",winstrk($A$2:A2),"")

Drag/copy down column B

Gord Dibben Excel MVP


On Fri, 31 Dec 2004 12:43:41 -0500, Ron Rosenfeld
wrote:

On Fri, 31 Dec 2004 07:37:04 -0800, ParTeeGolfer
wrote:

Using the column below, what is the best way to track a winning streak
automatically. Keep in mind the column will get longer with data, weather it
be with a "W" or an "L". It Seems to me the best way would be to start at the
bottom of the list and count the "W"'s till I come to an "L" however I am
stumped on how to accomplish this

If I understand you correctly, you want to keep track of how long your current
winning streak has been on going.

If that is the case, it is relatively trivial to do in VBA with a User Defined
Function (UDF).

Assume your list of W and L are in a range named Win_Loss.
Assume the only valid entries in this range are "W", "L" or a blank cell.
Assume that the W's and L's are contiguous.

<alt<F11 opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

Enter the UDF

=winstrk(Win_loss)

in some cell. Win_loss can be a named range, or a range reference such as
A2:A100.

The most recent number of wins will be displayed.

==========================
Function WinStrk(WL As Range) As Long
Dim i As Long
Dim c As Range

For i = WL.Count To 1 Step -1
If WL(i, 1) = "L" Then Exit Function
If WL(i, 1) = "W" Then WinStrk = WinStrk + 1
Next i

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






--ron

On Fri, 31 Dec 2004 12:41:11 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

In addition, using Ron's UDF, you could keep a running total of the wins
in an adjacent column.

In B2 enter =IF(A2="W",winstrk($A$2:A2),"")

Drag/copy down column B

Gord Dibben Excel MVP

Good thought, Gord.

And if you don't mind having 0's instead of a blank, or if you format the cells
so that 0's are displayed as a blank, you could also use:

=winstrk($A$2:A2)

and drag/copy it down.
--ron

"ParTeeGolfer" wrote...
Using the column below, what is the best way to track a winning streak
automatically. Keep in mind the column will get longer with data, weather
it be with a "W" or an "L". It Seems to me the best way would be to start
at the bottom of the list and count the "W"'s till I come to an "L"
however I am stumped on how to accomplish this

You're right. The best way to do this is to start with the last W and find
the nearest preceding L. If your W/L were in column A, try

=LOOKUP(2,1/(Games="W"),ROW(Games))-LOOKUP(2,1/(OFFSET(Games,0,0,
LOOKUP(2,1/(Games="W"),ROW(Games))-2,1)="L"),ROW(Games))