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