Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Winning Streak
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
"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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|