calculating longest winning/losing streak and totals
Awesome, thanks again!
"Lori Miller" wrote:
To shorten formulas, choose Insert Name Define:
T: =$A$2:$A$99
D: =$B$2:$B$99
i: =ROW(T)
W: =FREQUENCY(IF(D0,T),IF(TMIN(T),(D<=0)*T))
L: =FREQUENCY(IF(D<0,T),IF(TMIN(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
.
|