Formula for calculating win/loss streaks....

[Pittsburgher]

I have data from a tems current win/loss record. I want to calculate what the longest win streak is and the longest losing streak as well. What formula do I use?

 

[Pittsburgher]

Ah ha... I needed to add a Module to the workbook I was using. I had it added to a different workbook. It's working now, thanks for the info!

 

[Solola]

Win/Loss Streak

I have two similar questions - ones that cannot be answered by the preceding response.

Question 1 - My column of data also contains "Win" or "Loss," but occasionally there may be a blank cell (that person did not play that day, thus did not win nor lose). If I a person has 3 wins in a row, then a no play, then 2 more wins, I want that person's win streak to be 5, not 3.

Can this code be modified to take that into account?

Also, question number 2 - how can I calculate that current winning streak? For example, each time the game is played, I add a new row at the bottom of the data. I only want to calculate the number of times the person has currently been on a winning streak. Is this possible?

 

[Joe4]

For question 1, try this:

Function Streak(myRange As Range, myValue As String) As Long

    Dim cell As Range
    Dim TempStreak As Long
    
    For Each cell In myRange
        If cell = myValue Then
            TempStreak = TempStreak + 1
        Else
            Streak = Application.WorksheetFunction.Max(Streak, TempStreak)
            If Len(cell) > 0 Then TempStreak = 0
        End If
    Next cell
    Streak = Application.WorksheetFunction.Max(Streak, TempStreak)
    
End Function

For question 2, try this new function of CurrentStreak:

Function CurrentStreak(myRange As Range, myValue As String) As Long

    Dim cell As Range
    Dim TempStreak As Long
    
    For Each cell In myRange
        If cell = myValue Then
            TempStreak = TempStreak + 1
        Else
            CurrentStreak = Application.WorksheetFunction.Max(CurrentStreak, TempStreak)
            If Len(cell) > 0 Then TempStreak = 0
        End If
    Next cell
    CurrentStreak = TempStreak
    
End Function

 

[cmt_wright]

Hi, this is a really useful function. Thanks

How could the function be amended to be used with basic stats functions, like average winning streak, or, standard deviation of winning streak?
Thanks

 

[kekz]

bump!

hi guys!

can someone help me to modify Joe4's code to solve problem:

i have two columns (A and B) filled with 0 and 1. i need to calculate longest streak from the third column based on product of values from columns A and B:

for example:

A B C(=A*B)

1 1 1
0 1 0
0 0 0
1 1 1
0 0 0
... ... ...

columns A and B are continuously filled with new values.

thanks in advance!