Winning Streak

P

ParTeeGolfer

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
 
R

Ron Rosenfeld

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
 
G

Gord Dibben

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
 
R

Ron Rosenfeld

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
 
H

Harlan Grove

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top