Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Formula for Win/Lose Streak

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Formula for Win/Lose Streak

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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Formula for Win/Lose Streak

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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Formula for Win/Lose Streak

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting current streak Renan Germano Excel Worksheet Functions 6 January 15th 10 07:55 PM
win or lose formula needed Pete Excel Discussion (Misc queries) 10 November 12th 09 05:43 AM
longet Win streak ParTeeGolfer Excel Worksheet Functions 6 January 9th 05 01:12 PM
Winning Streak ParTeeGolfer Excel Worksheet Functions 4 January 1st 05 12:08 AM
Current consecutive streak scott[_8_] Excel Programming 2 November 24th 03 06:06 PM


All times are GMT +1. The time now is 01:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"