Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Find longest consecutive winning streaks
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 11
Default Find longest consecutive winning streaks
My data set is a series of monthly returns (in percentages) for an
investment. How can I go about finding which time period has the longest
consecutive winning months and what the return over that time period was.
Then find the second and third longest winning streaks which cannot be
contained in the longest one.

thanks in advance... trey

Jan-04 1.37%
Feb-04 0.99%
Mar-04 0.12%
Apr-04 -0.45%
May-04 -0.18%
Jun-04 0.30%
Jul-04 -0.21%
Aug-04 0.38%
Sep-04 0.74%
Oct-04 0.37%
Nov-04 1.50%
Dec-04 1.40%
Jan-05 0.39%
Feb-05 1.44%
Mar-05 -0.09%
Apr-05 -0.74%
May-05 0.88%
Jun-05 1.31%
Jul-05 1.53%
Aug-05 0.82%
Sep-05 1.63%
Oct-05 -1.50%
Nov-05 1.59%
Dec-05 1.93%
Jan-06 2.76%
Feb-06 0.47%
Mar-06 0.93%
Apr-06 1.63%
May-06 -1.65%
Jun-06 -0.68%
Jul-06 -0.03%
Aug-06 0.84%
Sep-06 0.28%
Oct-06 1.24%
Nov-06 1.52%
Dec-06 1.35%
Jan-07 1.44%
Feb-07 0.83%
Mar-07 1.58%
Apr-07 1.73%
May-07 1.91%
Jun-07 0.90%
Jul-07 1.01%
Aug-07 -1.38%
Sep-07 2.05%
Oct-07 2.83%

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 11,058
Default Find longest consecutive winning streaks
In C1 enter 1
In C2 enter:
=IF(B20,C1+1,0) and copy down. We see:

Jan-07 1.37% 1
Feb-07 0.99% 2
Mar-07 0.12% 3
Apr-07 -0.45% 0
May-07 -0.18% 0
Jun-07 0.30% 1
Jul-07 -0.21% 0
Aug-07 0.38% 1
Sep-07 0.74% 2
Oct-07 0.37% 3
Nov-07 1.50% 4
Dec-07 1.40% 5
Jan-07 0.39% 6
Feb-07 1.44% 7
Mar-07 -0.09% 0
Apr-07 -0.74% 0
May-07 0.88% 1
Jun-07 1.31% 2
Jul-07 1.53% 3
Aug-07 0.82% 4
Sep-07 1.63% 5
Oct-07 -1.50% 0
Nov-07 1.59% 1
Dec-07 1.93% 2
Jan-07 2.76% 3
Feb-07 0.47% 4
Mar-07 0.93% 5
Apr-07 1.63% 6
May-07 -1.65% 0
Jun-07 -0.68% 0
Jul-07 -0.03% 0
Aug-07 0.84% 1
Sep-07 0.28% 2
Oct-07 1.24% 3
Nov-07 1.52% 4
Dec-07 1.35% 5
Jan-07 1.44% 6
Feb-07 0.83% 7
Mar-07 1.58% 8
Apr-07 1.73% 9
May-07 1.91% 10
Jun-07 0.90% 11
Jul-07 1.01% 12
Aug-07 -1.38% 0
Sep-07 2.05% 1
Oct-07 2.83% 2

Look for the max in column C (12) to find the end of the longest streak,
then look for the next lower value followed by a zero to find the end of the
next longest streak, etc.

--
Gary''s Student - gsnu200761


"trey1982" wrote:

My data set is a series of monthly returns (in percentages) for an
investment. How can I go about finding which time period has the longest
consecutive winning months and what the return over that time period was.
Then find the second and third longest winning streaks which cannot be
contained in the longest one.

thanks in advance... trey

Jan-04 1.37%
Feb-04 0.99%
Mar-04 0.12%
Apr-04 -0.45%
May-04 -0.18%
Jun-04 0.30%
Jul-04 -0.21%
Aug-04 0.38%
Sep-04 0.74%
Oct-04 0.37%
Nov-04 1.50%
Dec-04 1.40%
Jan-05 0.39%
Feb-05 1.44%
Mar-05 -0.09%
Apr-05 -0.74%
May-05 0.88%
Jun-05 1.31%
Jul-05 1.53%
Aug-05 0.82%
Sep-05 1.63%
Oct-05 -1.50%
Nov-05 1.59%
Dec-05 1.93%
Jan-06 2.76%
Feb-06 0.47%
Mar-06 0.93%
Apr-06 1.63%
May-06 -1.65%
Jun-06 -0.68%
Jul-06 -0.03%
Aug-06 0.84%
Sep-06 0.28%
Oct-06 1.24%
Nov-06 1.52%
Dec-06 1.35%
Jan-07 1.44%
Feb-07 0.83%
Mar-07 1.58%
Apr-07 1.73%
May-07 1.91%
Jun-07 0.90%
Jul-07 1.01%
Aug-07 -1.38%
Sep-07 2.05%
Oct-07 2.83%

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 11
Default Find longest consecutive winning streaks
How can I create a formula that looks for the next lower value followed by a
zero to find the end of the next longest streak?

thanks...trey


"Gary''s Student" wrote:

In C1 enter 1
In C2 enter:
=IF(B20,C1+1,0) and copy down. We see:

Jan-07 1.37% 1
Feb-07 0.99% 2
Mar-07 0.12% 3
Apr-07 -0.45% 0
May-07 -0.18% 0
Jun-07 0.30% 1
Jul-07 -0.21% 0
Aug-07 0.38% 1
Sep-07 0.74% 2
Oct-07 0.37% 3
Nov-07 1.50% 4
Dec-07 1.40% 5
Jan-07 0.39% 6
Feb-07 1.44% 7
Mar-07 -0.09% 0
Apr-07 -0.74% 0
May-07 0.88% 1
Jun-07 1.31% 2
Jul-07 1.53% 3
Aug-07 0.82% 4
Sep-07 1.63% 5
Oct-07 -1.50% 0
Nov-07 1.59% 1
Dec-07 1.93% 2
Jan-07 2.76% 3
Feb-07 0.47% 4
Mar-07 0.93% 5
Apr-07 1.63% 6
May-07 -1.65% 0
Jun-07 -0.68% 0
Jul-07 -0.03% 0
Aug-07 0.84% 1
Sep-07 0.28% 2
Oct-07 1.24% 3
Nov-07 1.52% 4
Dec-07 1.35% 5
Jan-07 1.44% 6
Feb-07 0.83% 7
Mar-07 1.58% 8
Apr-07 1.73% 9
May-07 1.91% 10
Jun-07 0.90% 11
Jul-07 1.01% 12
Aug-07 -1.38% 0
Sep-07 2.05% 1
Oct-07 2.83% 2

Look for the max in column C (12) to find the end of the longest streak,
then look for the next lower value followed by a zero to find the end of the
next longest streak, etc.

--
Gary''s Student - gsnu200761


"trey1982" wrote:

My data set is a series of monthly returns (in percentages) for an
investment. How can I go about finding which time period has the longest
consecutive winning months and what the return over that time period was.
Then find the second and third longest winning streaks which cannot be
contained in the longest one.

thanks in advance... trey

Jan-04 1.37%
Feb-04 0.99%
Mar-04 0.12%
Apr-04 -0.45%
May-04 -0.18%
Jun-04 0.30%
Jul-04 -0.21%
Aug-04 0.38%
Sep-04 0.74%
Oct-04 0.37%
Nov-04 1.50%
Dec-04 1.40%
Jan-05 0.39%
Feb-05 1.44%
Mar-05 -0.09%
Apr-05 -0.74%
May-05 0.88%
Jun-05 1.31%
Jul-05 1.53%
Aug-05 0.82%
Sep-05 1.63%
Oct-05 -1.50%
Nov-05 1.59%
Dec-05 1.93%
Jan-06 2.76%
Feb-06 0.47%
Mar-06 0.93%
Apr-06 1.63%
May-06 -1.65%
Jun-06 -0.68%
Jul-06 -0.03%
Aug-06 0.84%
Sep-06 0.28%
Oct-06 1.24%
Nov-06 1.52%
Dec-06 1.35%
Jan-07 1.44%
Feb-07 0.83%
Mar-07 1.58%
Apr-07 1.73%
May-07 1.91%
Jun-07 0.90%
Jul-07 1.01%
Aug-07 -1.38%
Sep-07 2.05%
Oct-07 2.83%

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 10,124
Default Find longest consecutive winning streaks
From a post of mine about a month ago. Modify to suit

Try this where text in col a and numbers in col 2-11
Sub findlongestzeros()
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
mc = 0
For j = 2 To 11
If Cells(i, j) = 0 And Cells(i, j + 1) = 0 Then mc = mc + 1
Next j
'MsgBox mc
If mc mss Then
mss = mc
mr = i
End If
Next i
MsgBox "Max is Row " & mr
'MsgBox mss
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"trey1982" wrote in message
...
My data set is a series of monthly returns (in percentages) for an
investment. How can I go about finding which time period has the longest
consecutive winning months and what the return over that time period was.
Then find the second and third longest winning streaks which cannot be
contained in the longest one.

thanks in advance... trey

Jan-04 1.37%
Feb-04 0.99%
Mar-04 0.12%
Apr-04 -0.45%
May-04 -0.18%
Jun-04 0.30%
Jul-04 -0.21%
Aug-04 0.38%
Sep-04 0.74%
Oct-04 0.37%
Nov-04 1.50%
Dec-04 1.40%
Jan-05 0.39%
Feb-05 1.44%
Mar-05 -0.09%
Apr-05 -0.74%
May-05 0.88%
Jun-05 1.31%
Jul-05 1.53%
Aug-05 0.82%
Sep-05 1.63%
Oct-05 -1.50%
Nov-05 1.59%
Dec-05 1.93%
Jan-06 2.76%
Feb-06 0.47%
Mar-06 0.93%
Apr-06 1.63%
May-06 -1.65%
Jun-06 -0.68%
Jul-06 -0.03%
Aug-06 0.84%
Sep-06 0.28%
Oct-06 1.24%
Nov-06 1.52%
Dec-06 1.35%
Jan-07 1.44%
Feb-07 0.83%
Mar-07 1.58%
Apr-07 1.73%
May-07 1.91%
Jun-07 0.90%
Jul-07 1.01%
Aug-07 -1.38%
Sep-07 2.05%
Oct-07 2.83%


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 11,058
Default Find longest consecutive winning streaks
We know a streak has ended if we have a positive value followed by a zero.
So in D1 enter:
=IF(AND((C10),(C2=0)),C1,"") and copy down. We now see:

Jan-07 1.37% 1
Feb-07 0.99% 2
Mar-07 0.12% 3 3
Apr-07 -0.45% 0
May-07 -0.18% 0
Jun-07 0.30% 1 1
Jul-07 -0.21% 0
Aug-07 0.38% 1
Sep-07 0.74% 2
Oct-07 0.37% 3
Nov-07 1.50% 4
Dec-07 1.40% 5
Jan-07 0.39% 6
Feb-07 1.44% 7 7
Mar-07 -0.09% 0
Apr-07 -0.74% 0
May-07 0.88% 1
Jun-07 1.31% 2
Jul-07 1.53% 3
Aug-07 0.82% 4
Sep-07 1.63% 5 5
Oct-07 -1.50% 0
Nov-07 1.59% 1
Dec-07 1.93% 2
Jan-07 2.76% 3
Feb-07 0.47% 4
Mar-07 0.93% 5
Apr-07 1.63% 6 6
May-07 -1.65% 0
Jun-07 -0.68% 0
Jul-07 -0.03% 0
Aug-07 0.84% 1
Sep-07 0.28% 2
Oct-07 1.24% 3
Nov-07 1.52% 4
Dec-07 1.35% 5
Jan-07 1.44% 6
Feb-07 0.83% 7
Mar-07 1.58% 8
Apr-07 1.73% 9
May-07 1.91% 10
Jun-07 0.90% 11
Jul-07 1.01% 12 12
Aug-07 -1.38% 0
Sep-07 2.05% 1
Oct-07 2.83% 2 2

So now the 12 is still the highest, 7 is the next highest, etc. This method
will also display ties.
--
Gary''s Student - gsnu200761


"trey1982" wrote:

How can I create a formula that looks for the next lower value followed by a
zero to find the end of the next longest streak?

thanks...trey


"Gary''s Student" wrote:

In C1 enter 1
In C2 enter:
=IF(B20,C1+1,0) and copy down. We see:

Jan-07 1.37% 1
Feb-07 0.99% 2
Mar-07 0.12% 3
Apr-07 -0.45% 0
May-07 -0.18% 0
Jun-07 0.30% 1
Jul-07 -0.21% 0
Aug-07 0.38% 1
Sep-07 0.74% 2
Oct-07 0.37% 3
Nov-07 1.50% 4
Dec-07 1.40% 5
Jan-07 0.39% 6
Feb-07 1.44% 7
Mar-07 -0.09% 0
Apr-07 -0.74% 0
May-07 0.88% 1
Jun-07 1.31% 2
Jul-07 1.53% 3
Aug-07 0.82% 4
Sep-07 1.63% 5
Oct-07 -1.50% 0
Nov-07 1.59% 1
Dec-07 1.93% 2
Jan-07 2.76% 3
Feb-07 0.47% 4
Mar-07 0.93% 5
Apr-07 1.63% 6
May-07 -1.65% 0
Jun-07 -0.68% 0
Jul-07 -0.03% 0
Aug-07 0.84% 1
Sep-07 0.28% 2
Oct-07 1.24% 3
Nov-07 1.52% 4
Dec-07 1.35% 5
Jan-07 1.44% 6
Feb-07 0.83% 7
Mar-07 1.58% 8
Apr-07 1.73% 9
May-07 1.91% 10
Jun-07 0.90% 11
Jul-07 1.01% 12
Aug-07 -1.38% 0
Sep-07 2.05% 1
Oct-07 2.83% 2

Look for the max in column C (12) to find the end of the longest streak,
then look for the next lower value followed by a zero to find the end of the
next longest streak, etc.

--
Gary''s Student - gsnu200761


"trey1982" wrote:

My data set is a series of monthly returns (in percentages) for an
investment. How can I go about finding which time period has the longest
consecutive winning months and what the return over that time period was.
Then find the second and third longest winning streaks which cannot be
contained in the longest one.

thanks in advance... trey

Jan-04 1.37%
Feb-04 0.99%
Mar-04 0.12%
Apr-04 -0.45%
May-04 -0.18%
Jun-04 0.30%
Jul-04 -0.21%
Aug-04 0.38%
Sep-04 0.74%
Oct-04 0.37%
Nov-04 1.50%
Dec-04 1.40%
Jan-05 0.39%
Feb-05 1.44%
Mar-05 -0.09%
Apr-05 -0.74%
May-05 0.88%
Jun-05 1.31%
Jul-05 1.53%
Aug-05 0.82%
Sep-05 1.63%
Oct-05 -1.50%
Nov-05 1.59%
Dec-05 1.93%
Jan-06 2.76%
Feb-06 0.47%
Mar-06 0.93%
Apr-06 1.63%
May-06 -1.65%
Jun-06 -0.68%
Jul-06 -0.03%
Aug-06 0.84%
Sep-06 0.28%
Oct-06 1.24%
Nov-06 1.52%
Dec-06 1.35%
Jan-07 1.44%
Feb-07 0.83%
Mar-07 1.58%
Apr-07 1.73%
May-07 1.91%
Jun-07 0.90%
Jul-07 1.01%
Aug-07 -1.38%
Sep-07 2.05%
Oct-07 2.83%

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default Find longest consecutive winning streaks
In C1 enter 1

That works on the posted sample but if the first entry was negative...

Try this instead:

=--(B10)


--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in message
...
In C1 enter 1
In C2 enter:
=IF(B20,C1+1,0) and copy down. We see:

Jan-07 1.37% 1
Feb-07 0.99% 2
Mar-07 0.12% 3
Apr-07 -0.45% 0
May-07 -0.18% 0
Jun-07 0.30% 1
Jul-07 -0.21% 0
Aug-07 0.38% 1
Sep-07 0.74% 2
Oct-07 0.37% 3
Nov-07 1.50% 4
Dec-07 1.40% 5
Jan-07 0.39% 6
Feb-07 1.44% 7
Mar-07 -0.09% 0
Apr-07 -0.74% 0
May-07 0.88% 1
Jun-07 1.31% 2
Jul-07 1.53% 3
Aug-07 0.82% 4
Sep-07 1.63% 5
Oct-07 -1.50% 0
Nov-07 1.59% 1
Dec-07 1.93% 2
Jan-07 2.76% 3
Feb-07 0.47% 4
Mar-07 0.93% 5
Apr-07 1.63% 6
May-07 -1.65% 0
Jun-07 -0.68% 0
Jul-07 -0.03% 0
Aug-07 0.84% 1
Sep-07 0.28% 2
Oct-07 1.24% 3
Nov-07 1.52% 4
Dec-07 1.35% 5
Jan-07 1.44% 6
Feb-07 0.83% 7
Mar-07 1.58% 8
Apr-07 1.73% 9
May-07 1.91% 10
Jun-07 0.90% 11
Jul-07 1.01% 12
Aug-07 -1.38% 0
Sep-07 2.05% 1
Oct-07 2.83% 2

Look for the max in column C (12) to find the end of the longest streak,
then look for the next lower value followed by a zero to find the end of
the
next longest streak, etc.

--
Gary''s Student - gsnu200761


"trey1982" wrote:

My data set is a series of monthly returns (in percentages) for an
investment. How can I go about finding which time period has the longest
consecutive winning months and what the return over that time period was.
Then find the second and third longest winning streaks which cannot be
contained in the longest one.

thanks in advance... trey

Jan-04 1.37%
Feb-04 0.99%
Mar-04 0.12%
Apr-04 -0.45%
May-04 -0.18%
Jun-04 0.30%
Jul-04 -0.21%
Aug-04 0.38%
Sep-04 0.74%
Oct-04 0.37%
Nov-04 1.50%
Dec-04 1.40%
Jan-05 0.39%
Feb-05 1.44%
Mar-05 -0.09%
Apr-05 -0.74%
May-05 0.88%
Jun-05 1.31%
Jul-05 1.53%
Aug-05 0.82%
Sep-05 1.63%
Oct-05 -1.50%
Nov-05 1.59%
Dec-05 1.93%
Jan-06 2.76%
Feb-06 0.47%
Mar-06 0.93%
Apr-06 1.63%
May-06 -1.65%
Jun-06 -0.68%
Jul-06 -0.03%
Aug-06 0.84%
Sep-06 0.28%
Oct-06 1.24%
Nov-06 1.52%
Dec-06 1.35%
Jan-07 1.44%
Feb-07 0.83%
Mar-07 1.58%
Apr-07 1.73%
May-07 1.91%
Jun-07 0.90%
Jul-07 1.01%
Aug-07 -1.38%
Sep-07 2.05%
Oct-07 2.83%



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 15,768
Default Find longest consecutive winning streaks
Using a single array formula** entered in, say, E1:

=LARGE(FREQUENCY(IF(B$1:B$460,ROW(B$1:B$46)),IF(B $1:B$46<=0,ROW(B$1:B$46))),ROWS(E$1:E1))

Copy down until you get either 0s or #NUM! errors.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in message
...
We know a streak has ended if we have a positive value followed by a zero.
So in D1 enter:
=IF(AND((C10),(C2=0)),C1,"") and copy down. We now see:

Jan-07 1.37% 1
Feb-07 0.99% 2
Mar-07 0.12% 3 3
Apr-07 -0.45% 0
May-07 -0.18% 0
Jun-07 0.30% 1 1
Jul-07 -0.21% 0
Aug-07 0.38% 1
Sep-07 0.74% 2
Oct-07 0.37% 3
Nov-07 1.50% 4
Dec-07 1.40% 5
Jan-07 0.39% 6
Feb-07 1.44% 7 7
Mar-07 -0.09% 0
Apr-07 -0.74% 0
May-07 0.88% 1
Jun-07 1.31% 2
Jul-07 1.53% 3
Aug-07 0.82% 4
Sep-07 1.63% 5 5
Oct-07 -1.50% 0
Nov-07 1.59% 1
Dec-07 1.93% 2
Jan-07 2.76% 3
Feb-07 0.47% 4
Mar-07 0.93% 5
Apr-07 1.63% 6 6
May-07 -1.65% 0
Jun-07 -0.68% 0
Jul-07 -0.03% 0
Aug-07 0.84% 1
Sep-07 0.28% 2
Oct-07 1.24% 3
Nov-07 1.52% 4
Dec-07 1.35% 5
Jan-07 1.44% 6
Feb-07 0.83% 7
Mar-07 1.58% 8
Apr-07 1.73% 9
May-07 1.91% 10
Jun-07 0.90% 11
Jul-07 1.01% 12 12
Aug-07 -1.38% 0
Sep-07 2.05% 1
Oct-07 2.83% 2 2

So now the 12 is still the highest, 7 is the next highest, etc. This
method
will also display ties.
--
Gary''s Student - gsnu200761


"trey1982" wrote:

How can I create a formula that looks for the next lower value followed
by a
zero to find the end of the next longest streak?

thanks...trey


"Gary''s Student" wrote:

In C1 enter 1
In C2 enter:
=IF(B20,C1+1,0) and copy down. We see:

Jan-07 1.37% 1
Feb-07 0.99% 2
Mar-07 0.12% 3
Apr-07 -0.45% 0
May-07 -0.18% 0
Jun-07 0.30% 1
Jul-07 -0.21% 0
Aug-07 0.38% 1
Sep-07 0.74% 2
Oct-07 0.37% 3
Nov-07 1.50% 4
Dec-07 1.40% 5
Jan-07 0.39% 6
Feb-07 1.44% 7
Mar-07 -0.09% 0
Apr-07 -0.74% 0
May-07 0.88% 1
Jun-07 1.31% 2
Jul-07 1.53% 3
Aug-07 0.82% 4
Sep-07 1.63% 5
Oct-07 -1.50% 0
Nov-07 1.59% 1
Dec-07 1.93% 2
Jan-07 2.76% 3
Feb-07 0.47% 4
Mar-07 0.93% 5
Apr-07 1.63% 6
May-07 -1.65% 0
Jun-07 -0.68% 0
Jul-07 -0.03% 0
Aug-07 0.84% 1
Sep-07 0.28% 2
Oct-07 1.24% 3
Nov-07 1.52% 4
Dec-07 1.35% 5
Jan-07 1.44% 6
Feb-07 0.83% 7
Mar-07 1.58% 8
Apr-07 1.73% 9
May-07 1.91% 10
Jun-07 0.90% 11
Jul-07 1.01% 12
Aug-07 -1.38% 0
Sep-07 2.05% 1
Oct-07 2.83% 2

Look for the max in column C (12) to find the end of the longest
streak,
then look for the next lower value followed by a zero to find the end
of the
next longest streak, etc.

--
Gary''s Student - gsnu200761


"trey1982" wrote:

My data set is a series of monthly returns (in percentages) for an
investment. How can I go about finding which time period has the
longest
consecutive winning months and what the return over that time period
was.
Then find the second and third longest winning streaks which cannot
be
contained in the longest one.

thanks in advance... trey

Jan-04 1.37%
Feb-04 0.99%
Mar-04 0.12%
Apr-04 -0.45%
May-04 -0.18%
Jun-04 0.30%
Jul-04 -0.21%
Aug-04 0.38%
Sep-04 0.74%
Oct-04 0.37%
Nov-04 1.50%
Dec-04 1.40%
Jan-05 0.39%
Feb-05 1.44%
Mar-05 -0.09%
Apr-05 -0.74%
May-05 0.88%
Jun-05 1.31%
Jul-05 1.53%
Aug-05 0.82%
Sep-05 1.63%
Oct-05 -1.50%
Nov-05 1.59%
Dec-05 1.93%
Jan-06 2.76%
Feb-06 0.47%
Mar-06 0.93%
Apr-06 1.63%
May-06 -1.65%
Jun-06 -0.68%
Jul-06 -0.03%
Aug-06 0.84%
Sep-06 0.28%
Oct-06 1.24%
Nov-06 1.52%
Dec-06 1.35%
Jan-07 1.44%
Feb-07 0.83%
Mar-07 1.58%
Apr-07 1.73%
May-07 1.91%
Jun-07 0.90%
Jul-07 1.01%
Aug-07 -1.38%
Sep-07 2.05%
Oct-07 2.83%



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 11
Default Find longest consecutive winning streaks
to the final part of my question. how does one determine the return for each
of these "winning" periods. [(1+x)*(1+x2)*(1+x3) - 1] i understand. i can
even figure out a way on how to get the first "winning period" return, but am
stumped on how to figure out a way to the return for each of these "winning"
periods.

....trey

"Gary''s Student" wrote:

We know a streak has ended if we have a positive value followed by a zero.
So in D1 enter:
=IF(AND((C10),(C2=0)),C1,"") and copy down. We now see:

Jan-07 1.37% 1
Feb-07 0.99% 2
Mar-07 0.12% 3 3
Apr-07 -0.45% 0
May-07 -0.18% 0
Jun-07 0.30% 1 1
Jul-07 -0.21% 0
Aug-07 0.38% 1
Sep-07 0.74% 2
Oct-07 0.37% 3
Nov-07 1.50% 4
Dec-07 1.40% 5
Jan-07 0.39% 6
Feb-07 1.44% 7 7
Mar-07 -0.09% 0
Apr-07 -0.74% 0
May-07 0.88% 1
Jun-07 1.31% 2
Jul-07 1.53% 3
Aug-07 0.82% 4
Sep-07 1.63% 5 5
Oct-07 -1.50% 0
Nov-07 1.59% 1
Dec-07 1.93% 2
Jan-07 2.76% 3
Feb-07 0.47% 4
Mar-07 0.93% 5
Apr-07 1.63% 6 6
May-07 -1.65% 0
Jun-07 -0.68% 0
Jul-07 -0.03% 0
Aug-07 0.84% 1
Sep-07 0.28% 2
Oct-07 1.24% 3
Nov-07 1.52% 4
Dec-07 1.35% 5
Jan-07 1.44% 6
Feb-07 0.83% 7
Mar-07 1.58% 8
Apr-07 1.73% 9
May-07 1.91% 10
Jun-07 0.90% 11
Jul-07 1.01% 12 12
Aug-07 -1.38% 0
Sep-07 2.05% 1
Oct-07 2.83% 2 2

So now the 12 is still the highest, 7 is the next highest, etc. This method
will also display ties.
--
Gary''s Student - gsnu200761


"trey1982" wrote:

How can I create a formula that looks for the next lower value followed by a
zero to find the end of the next longest streak?

thanks...trey


"Gary''s Student" wrote:

In C1 enter 1
In C2 enter:
=IF(B20,C1+1,0) and copy down. We see:

Jan-07 1.37% 1
Feb-07 0.99% 2
Mar-07 0.12% 3
Apr-07 -0.45% 0
May-07 -0.18% 0
Jun-07 0.30% 1
Jul-07 -0.21% 0
Aug-07 0.38% 1
Sep-07 0.74% 2
Oct-07 0.37% 3
Nov-07 1.50% 4
Dec-07 1.40% 5
Jan-07 0.39% 6
Feb-07 1.44% 7
Mar-07 -0.09% 0
Apr-07 -0.74% 0
May-07 0.88% 1
Jun-07 1.31% 2
Jul-07 1.53% 3
Aug-07 0.82% 4
Sep-07 1.63% 5
Oct-07 -1.50% 0
Nov-07 1.59% 1
Dec-07 1.93% 2
Jan-07 2.76% 3
Feb-07 0.47% 4
Mar-07 0.93% 5
Apr-07 1.63% 6
May-07 -1.65% 0
Jun-07 -0.68% 0
Jul-07 -0.03% 0
Aug-07 0.84% 1
Sep-07 0.28% 2
Oct-07 1.24% 3
Nov-07 1.52% 4
Dec-07 1.35% 5
Jan-07 1.44% 6
Feb-07 0.83% 7
Mar-07 1.58% 8
Apr-07 1.73% 9
May-07 1.91% 10
Jun-07 0.90% 11
Jul-07 1.01% 12
Aug-07 -1.38% 0
Sep-07 2.05% 1
Oct-07 2.83% 2

Look for the max in column C (12) to find the end of the longest streak,
then look for the next lower value followed by a zero to find the end of the
next longest streak, etc.

--
Gary''s Student - gsnu200761


"trey1982" wrote:

My data set is a series of monthly returns (in percentages) for an
investment. How can I go about finding which time period has the longest
consecutive winning months and what the return over that time period was.
Then find the second and third longest winning streaks which cannot be
contained in the longest one.

thanks in advance... trey

Jan-04 1.37%
Feb-04 0.99%
Mar-04 0.12%
Apr-04 -0.45%
May-04 -0.18%
Jun-04 0.30%
Jul-04 -0.21%
Aug-04 0.38%
Sep-04 0.74%
Oct-04 0.37%
Nov-04 1.50%
Dec-04 1.40%
Jan-05 0.39%
Feb-05 1.44%
Mar-05 -0.09%
Apr-05 -0.74%
May-05 0.88%
Jun-05 1.31%
Jul-05 1.53%
Aug-05 0.82%
Sep-05 1.63%
Oct-05 -1.50%
Nov-05 1.59%
Dec-05 1.93%
Jan-06 2.76%
Feb-06 0.47%
Mar-06 0.93%
Apr-06 1.63%
May-06 -1.65%
Jun-06 -0.68%
Jul-06 -0.03%
Aug-06 0.84%
Sep-06 0.28%
Oct-06 1.24%
Nov-06 1.52%
Dec-06 1.35%
Jan-07 1.44%
Feb-07 0.83%
Mar-07 1.58%
Apr-07 1.73%
May-07 1.91%
Jun-07 0.90%
Jul-07 1.01%
Aug-07 -1.38%
Sep-07 2.05%
Oct-07 2.83%

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Find the Longest Run. dlbeiler Excel Worksheet Functions 7 October 11th 07 03:33 AM
Find and replace consecutive numbers Colin Hayes Excel Worksheet Functions 6 December 26th 06 03:45 PM
Longest string in a column Jeff Kantner Excel Worksheet Functions 6 December 1st 05 05:56 AM
find the max values for cells in consecutive groups of 600 john Excel Discussion (Misc queries) 2 October 4th 05 10:52 AM
Longest Entry in Columns Rebecca New Users to Excel 2 February 26th 05 03:54 AM


All times are GMT +1. The time now is 03:05 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"

 

Contact Us ExcelBanter forum home Privacy Statement