The row itself: http://i.imgur.com/5VbTDqs.png

I'm trying to have a formula that will figure out which was my longest win and lose streak (where any value above 0 is considered a win, and any value under 0 is considered a loss), but I can't seem to figure it out whatsoever.

I've tried this formula: =MAX(FREQUENCY(IF(I14:I="0",ROW(I14:I)),IF(I14:I <"0",ROW(I14:I))))

But, it doesn't seem to be working whatsoever, I just get an array error.

Any help would be appreciated.

Hi,

Am Sun, 20 Dec 2015 08:04:58 -0800 (PST) schrieb :

The row itself: http://i.imgur.com/5VbTDqs.png

I'm trying to have a formula that will figure out which was my longest win and lose streak (where any value above 0 is considered a win, and any value under 0 is considered a loss), but I can't seem to figure it out whatsoever.

I've tried this formula: =MAX(FREQUENCY(IF(I14:I="0",ROW(I14:I)),IF(I14:I <"0",ROW(I14:I))))

try it with an UDF:

Function LongestSerie(myRng As Range, Result As String) As Integer
Dim rngC As Range
Dim varRes() As Variant
Dim Counter As Integer, i As Integer

For Each rngC In myRng
Select Case Result
Case "w"
If rngC 0 Then
Counter = Counter + 1
Else
ReDim Preserve varRes(i)
varRes(i) = Counter
Counter = 0
i = i + 1
End If
Case "l"
If rngC < 0 Then
Counter = Counter + 1
Else
ReDim Preserve varRes(i)
varRes(i) = Counter
Counter = 0
i = i + 1
End If
Case "d"
If rngC = 0 Then
Counter = Counter + 1
Else
ReDim Preserve varRes(i)
varRes(i) = Counter
Counter = 0
i = i + 1
End If
End Select
Next
LongestSerie = Application.Max(varRes)
End Function

Call this function in the sheet with e.g.
=LongestSerie(I14:I23;"w") for won games
=LongestSerie(I14:I23;"l") for lost games
=LongestSerie(I14:I23;"d") for draws


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi,

Am Sun, 20 Dec 2015 20:36:44 +0100 schrieb Claus Busch:

try it with an UDF:

Function LongestSerie(myRng As Range, Result As String) As Integer

there is an errror in the code of the first answer.
Try it this way:

Function LongestSerie(myRng As Range, Result As String) As Integer
Dim rngC As Range
Dim varRes() As Variant
Dim Counter As Integer, i As Integer

If myRng.Columns.Count = 1 Then
Set myRng = myRng.Resize(rowsize:=myRng.Rows.Count + 1)
ElseIf myRng.rowss.Count = 1 Then
Set myRng = myRng.Resize(columnsize:=myRng.Columns.Count + 1)
End If

For Each rngC In myRng
Select Case LCase(Result)
Case "w"
If rngC 0 Then
Counter = Counter + 1
ElseIf rngC <= 0 And Counter 0 Then
ReDim Preserve varRes(i)
varRes(i) = Counter
Counter = 0
i = i + 1
End If
Case "l"
If rngC < 0 Then
Counter = Counter + 1
ElseIf rngC = 0 And Counter 0 Then
ReDim Preserve varRes(i)
varRes(i) = Counter
Counter = 0
i = i + 1
End If
Case "d"
If Len(rngC) 0 And rngC = 0 Then
Counter = Counter + 1
ElseIf (rngC < 0 And Counter 0) Or (Len(rngC) = 0 And
Counter 0) Then
ReDim Preserve varRes(i)
varRes(i) = Counter
Counter = 0
i = i + 1
End If
End Select
Next
LongestSerie = Application.Max(varRes)
End Function

And call the function in the sheet with
=IFERROR(LongestSerie($I$14:$I$23,"w"),0)
for won games.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I do it in auxiliary columns, as I want to know not only the maximum, but also the current streak.

Check this file:

https://goo.gl/rUKR7T

Columns W and X show current win/lose streak, columns Y and Z show maximum win/lose streak.