Winning Streak

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...

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
 
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
Click to expand...

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 said:
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
Click to expand...

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))
 
Back
Top