calculating longest winning/losing streak and totals

  • Thread starter Thread starter yowzers
  • Start date Start date
Y

yowzers

In column A, I have a list of dates and in column B, I have profits and
losses in dollar amounts correlating to that date. I want to be able to
calculate the longest winning/losing streaks in number of days. I also want
to be able to calculate the dollar amount total during that streak
 
Also, while I'm at it, in addition to the function above, I'd like to be able
to find the best/worst streak within a defined amount of time. For example,
I want to find how much I made in the best 10 day period throughout the year.
I'd like the formula to automatically find the most profitable 10 day period
and tell me the amount of profit.
 
In column A, I have a list of dates and in column B, I have profits and
losses in dollar amounts correlating to that date. I want to be able to
calculate the longest winning/losing streaks in number of days. I also
want
to be able to calculate the dollar amount total during that streak
Click to expand...

Assume data range = A2:B99 and dates are in ascending order.

Try these all entered with CTRL+SHIFT+ENTER:

Longest Winning Streak (E2):
=MAX(FREQUENCY(IF(B2:B99>0,A2:A99),IF(B2:B99<=0,A2:A99)))

Start Date (E3):
=INDEX(A2:A99,MATCH(E2,FREQUENCY(IF(B2:B99>0,A2:A99),IF(B2:B99<=0,A2:A99,)),0)-E2)

End Date (E4):
=INDEX(A2:A99,MATCH(E2,FREQUENCY(IF(B2:B99>0,A2:A99),IF(B2:B99<=0,A2:A99,)),0)-1)

Amount (E5):
=SUMIF(A2:A99,"<="&E4,B2:B99)-SUMIF(A2:A99,"<"&E3,B2:B99)

Also, while I'm at it, in addition to the function above, I'd like to be
able
to find the best/worst streak within a defined amount of time. For
example,
I want to find how much I made in the best 10 day period throughout the
year.
I'd like the formula to automatically find the most profitable 10 day
period
and tell me the amount of profit.
Click to expand...

Try these all entered with CTRL+SHIFT+ENTER:

Largest Amount in 10 Days (E7):
=MAX(SUMIF(A2:A99,"<"&A2:A99+10,B2:B99)-SUMIF(A2:A99,"<"&A2:A99,B2:B99))

Start Date (E8):
=MIN(IF(SUMIF(A2:A99,"<"&A2:A99+10,B2:B99)-SUMIF(A2:A99,"<"&A2:A99,B2:B99)=E7,A2:A99))

[You may need to change the number format of dates.]
 
AWESOME! Works like a charm.

Lori Miller said:
In column A, I have a list of dates and in column B, I have profits and
losses in dollar amounts correlating to that date. I want to be able to
calculate the longest winning/losing streaks in number of days. I also
want
to be able to calculate the dollar amount total during that streak
Click to expand...

Assume data range = A2:B99 and dates are in ascending order.

Try these all entered with CTRL+SHIFT+ENTER:

Longest Winning Streak (E2):
=MAX(FREQUENCY(IF(B2:B99>0,A2:A99),IF(B2:B99<=0,A2:A99)))

Start Date (E3):
=INDEX(A2:A99,MATCH(E2,FREQUENCY(IF(B2:B99>0,A2:A99),IF(B2:B99<=0,A2:A99,)),0)-E2)

End Date (E4):
=INDEX(A2:A99,MATCH(E2,FREQUENCY(IF(B2:B99>0,A2:A99),IF(B2:B99<=0,A2:A99,)),0)-1)

Amount (E5):
=SUMIF(A2:A99,"<="&E4,B2:B99)-SUMIF(A2:A99,"<"&E3,B2:B99)

Also, while I'm at it, in addition to the function above, I'd like to be
able
to find the best/worst streak within a defined amount of time. For
example,
I want to find how much I made in the best 10 day period throughout the
year.
I'd like the formula to automatically find the most profitable 10 day
period
and tell me the amount of profit.
Click to expand...

Try these all entered with CTRL+SHIFT+ENTER:

Largest Amount in 10 Days (E7):
=MAX(SUMIF(A2:A99,"<"&A2:A99+10,B2:B99)-SUMIF(A2:A99,"<"&A2:A99,B2:B99))

Start Date (E8):
=MIN(IF(SUMIF(A2:A99,"<"&A2:A99+10,B2:B99)-SUMIF(A2:A99,"<"&A2:A99,B2:B99)=E7,A2:A99))

[You may need to change the number format of dates.]


.
Click to expand...
 
ARGGG I messed up when describing what I needed to find. I need to find the
longest winning and losing streaks and totals by number of entries, not date
(I do not have an entry every day). I guess the dates in column A have no
relevance. So instead of finding the longest number of days between start
date and finish date of a streak, I need to know number of entries within
that streak. Also, the second party of the question, I need to know the
longest streak within a set number of entries, not dates. How do I tweek
this formula for this? Sorry....

Lori Miller said:
In column A, I have a list of dates and in column B, I have profits and
losses in dollar amounts correlating to that date. I want to be able to
calculate the longest winning/losing streaks in number of days. I also
want
to be able to calculate the dollar amount total during that streak
Click to expand...

Assume data range = A2:B99 and dates are in ascending order.

Try these all entered with CTRL+SHIFT+ENTER:

Longest Winning Streak (E2):
=MAX(FREQUENCY(IF(B2:B99>0,A2:A99),IF(B2:B99<=0,A2:A99)))

Start Date (E3):
=INDEX(A2:A99,MATCH(E2,FREQUENCY(IF(B2:B99>0,A2:A99),IF(B2:B99<=0,A2:A99,)),0)-E2)

End Date (E4):
=INDEX(A2:A99,MATCH(E2,FREQUENCY(IF(B2:B99>0,A2:A99),IF(B2:B99<=0,A2:A99,)),0)-1)

Amount (E5):
=SUMIF(A2:A99,"<="&E4,B2:B99)-SUMIF(A2:A99,"<"&E3,B2:B99)

Also, while I'm at it, in addition to the function above, I'd like to be
able
to find the best/worst streak within a defined amount of time. For
example,
I want to find how much I made in the best 10 day period throughout the
year.
I'd like the formula to automatically find the most profitable 10 day
period
and tell me the amount of profit.
Click to expand...

Try these all entered with CTRL+SHIFT+ENTER:

Largest Amount in 10 Days (E7):
=MAX(SUMIF(A2:A99,"<"&A2:A99+10,B2:B99)-SUMIF(A2:A99,"<"&A2:A99,B2:B99))

Start Date (E8):
=MIN(IF(SUMIF(A2:A99,"<"&A2:A99+10,B2:B99)-SUMIF(A2:A99,"<"&A2:A99,B2:B99)=E7,A2:A99))

[You may need to change the number format of dates.]


.
Click to expand...
 
The first part should find the longest winning streak based on
consecutve entries, also the associated dates and dollar amount.
So hopefully this is ok?

For the second part, you can tweak the formulas to refer
to the date ten entries down instead of the date plus ten days.

Try selecting both formulas, then:

Edit > Replace:

Find What:
A2:A99+10

Replace With:
LOOKUP(ROW(A2:A99)+10,ROW(A2:A99),A2:A99)

(Note: Although not strictly needed, it's simpler to work with a
date column as you can make use of the SUMIF function.)
 
Lori, thanks for all your help. Couple of followups.

In the first part, the formula is giving me the first available longest
streak. However, if 10 is the longest streak, and I have three different
times where I've gone on a streak of 10, I'd like it to give me the largest
one.

Also, in the second part, the start date is fine when I find the start of a
win streak given a set amount of entries. However, when I convert the < to >
to find the start date for the losing streak, for some reason it gives me one
day before the start date. For example, if my 10 day losing streak starts on
1/5/09, it will say 1/4/09. Is there something I need to adjust?

Lastly, how can I find the end date for second part? Right now, I only have
the formula for the start date and I can look at my list and count ten
entries down. Is there a formula that will give this to me?

Thanks again!!!
 
To shorten formulas, choose Insert > Name > Define:

T: =$A$2:$A$99
D: =$B$2:$B$99
i: =ROW(T)
W: =FREQUENCY(IF(D>0,T),IF(T>MIN(T),(D<=0)*T))
L: =FREQUENCY(IF(D<0,T),IF(T>MIN(T),(D>=0)*T))
DW:=SUMIF(T,"<="&T,D)-SUMIF(T,"<="&LOOKUP(i-W,i,T),D)
DL:=SUMIF(T,"<="&T,D)-SUMIF(T,"<="&LOOKUP(i-L,i,T),D)

W and DW are the streaks of wins and dollar amounts.
You can enter "=W" in C2:C99 with Ctrl+Shift+Enter {CSE}
which should fill the range, and also "=DW" in D2:D99.
__________________
All formulas need entering with {CSE} except E4 and E9:

Longest Streak of Wins (E2): =MAX(W)
Dollar Amount (E3):=MAX(IF(W=E2,DW))
Start Date (E4): =INDEX(T,MATCH(E5,T,0)-E2+1)
End Date (E5): =MIN(IF(W=E2,IF(DW=E3,T)))

Largest 10 Days (E7):
=MAX(IF(i<=MAX(i)-10+1,SUMIF(T,"<"&LOOKUP(i+10,i,T),D)-SUMIF(T,"<"&T,D)))
Start Date (E8):
=MIN(IF(SUMIF(T,"<"&LOOKUP(i+10,i,T),D)-SUMIF(T,"<"&T,D)=E7,T))
End Date (E9):
=INDEX(T,MATCH(E8,T,0)+10-1)
__________________

For losses:
- Copy formulas to column F
- Edit > replace "W" with "L"
- Use =MIN(...) instead of =MAX(...) for largest 10 days

Can't afford any more time on this so HTH, Lori
 
Back
Top