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?

 

[Joe4]

WELCOME TO THE BOARD!

You need to fill in a few gaps of your data layout. How is that data laid out? How can we determine wins/losses? Do you have columns showing the result of each game?

Perhaps posting a section of your spreadsheet would be helpful. See "Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board" at the bottom of every page on this forum.

 

[Pittsburgher]

Yea, basically, I have a column that shows either a "Win" or a "Loss", along with a whole bunch of other data.

 

[Joe4]

I have a column that shows either a "Win" or a "Loss", along with a whole bunch of other data.

OK, it is still important for us to understand exactly how the data looks in order for us to try to devise a solution for you. Remember, we have never seen your spreadsheet, so we need for you to be as detailed as possible.

Please post some sample data.

 

[Pittsburgher]

G6:G168 contain text values either "Win" or "Loss". These values are typed in and are not based on an if statement. That's the only range of data I want analyzed.

 

[Joe4]

OK, here is a User Defined Function that I wrote to do that for you:

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)
            TempStreak = 0
        End If
    Next cell
    Streak = Application.WorksheetFunction.Max(Streak, TempStreak)
    
End Function

Simply post this in the VB editor in standard module and use like any other function.

So if you wanted to get the longest winning streak in range G6:G168, use this formula:
=STREAK(G6:G168,"Win")

 

[Pittsburgher]

Ah, I can't get that to work. It returns a value of "#NAME?"

 

[Joe4]

What is the name of the module you are pasting the code in?

What exactly is the formula you are using?

The error probably occurs in one of those two places.

 

[Pittsburgher]

The Module name is "Module 1" and I'm using the formula "=STREAK(G6:G168,"Win")"

 

[Joe4]

I assume that you don't have the quotes around your formula, else it would appear as text, i.e.
=STREAK(G6:G168,"Win")

The #NAME? usually indicates that Excel cannot find the specified function. It sounds like you posted it in the right place (Module1). Two more things to verify:

1. You placed the function is in Module1 of the workbook you are using the formula in, and not in some other workbook.

2. You didn't modify the function in any way, did you?