| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting current winning streak | Excel Worksheet Functions | |||
| Find longest consecutive winning streaks | Excel Worksheet Functions | |||
| Calculating totals | Excel Discussion (Misc queries) | |||
| Help calculating totals | Excel Discussion (Misc queries) | |||
| Winning Streak | Excel Worksheet Functions | |||
Hybrid Mode
