calculating longest winning/losing streak and totals
yowzers
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
yowzers
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.
Lori Miller
> 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
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)
For the longest losing streak change the signs i.e. switch < with >.
________________________
> 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.
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.]
yowzers
"Lori Miller" wrote:
> .
>
yowzers
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" wrote:
> .
>
Lori Miller
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.)
yowzers
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!!!
"Lori Miller" wrote:
> .
>
Lori Miller
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
yowzers
"Lori Miller" wrote:
> .
>