Greetings All and a very Happy New Year to you and yours everywhere!
Could use some help please:
I am trying to count the number of days the between the start and end of a drawdown. i.e., how long did the drawdown last (in calendar days), before the state of drawdown ceased to exist?
I have tried several permutations (between me and Google) and it is still not correct, (and is likely not he best way to proceed anyway). There is probably a better way I imagine, (however I do wish to work this out without VBA, thanks).
Anyway, Here is what I have done so far:
In COL AH is a formula that counts drawdowns from another column (COL AF). When the source column shows a drawdown (<0), col AH starts counting until there is no longer a drawdown. So AH counts streaks. A 0 (zero) means there is no drawdown. Any other number, (1 and up) means this column is counting a continued streak of drawdowns.
COL AH
=IF($AF3=0,0,$AH2+1)
COL AI Looks at AH and copies dates for (each day of) the duration of the drawdown(s) from a different date column (COL M). Importantly, it does not include the date the losing streak ends, only right up until it ends.
COL AI
=IF(AH3=0,"",
IF(AND(AH3=0,AH2>=1),"",M3))
Since I could not find any better way to do this so far, COL AJ returns a date (the actual date that the drawdown ended) by asking if the COL AH has (two) zeros in a row, and leaving it blank if not.
COL AJ
=IF(AND(AH3=0,AH2>0),M3,"")
I now have a start date (and it's continuation) in COL AI and the date the drawdown (actually) ceased in COL AJ.
What I am after please is a way to count the number of days from the start, till the (actual) end of the streak (of drawdowns). I.e., how may days in the drawdown (till no more drawdowns)?
Thank you all so much for your thoughts on this - it is very much appreciated!
Could use some help please:
I am trying to count the number of days the between the start and end of a drawdown. i.e., how long did the drawdown last (in calendar days), before the state of drawdown ceased to exist?
I have tried several permutations (between me and Google) and it is still not correct, (and is likely not he best way to proceed anyway). There is probably a better way I imagine, (however I do wish to work this out without VBA, thanks).
Anyway, Here is what I have done so far:
In COL AH is a formula that counts drawdowns from another column (COL AF). When the source column shows a drawdown (<0), col AH starts counting until there is no longer a drawdown. So AH counts streaks. A 0 (zero) means there is no drawdown. Any other number, (1 and up) means this column is counting a continued streak of drawdowns.
COL AH
=IF($AF3=0,0,$AH2+1)
COL AI Looks at AH and copies dates for (each day of) the duration of the drawdown(s) from a different date column (COL M). Importantly, it does not include the date the losing streak ends, only right up until it ends.
COL AI
=IF(AH3=0,"",
IF(AND(AH3=0,AH2>=1),"",M3))
Since I could not find any better way to do this so far, COL AJ returns a date (the actual date that the drawdown ended) by asking if the COL AH has (two) zeros in a row, and leaving it blank if not.
COL AJ
=IF(AND(AH3=0,AH2>0),M3,"")
I now have a start date (and it's continuation) in COL AI and the date the drawdown (actually) ceased in COL AJ.
What I am after please is a way to count the number of days from the start, till the (actual) end of the streak (of drawdowns). I.e., how may days in the drawdown (till no more drawdowns)?
Thank you all so much for your thoughts on this - it is very much appreciated!
| CT SAT.xlsx | |||||
|---|---|---|---|---|---|
| AH | AI | AJ | |||
| 1 | DDCamps | DD Date | DDDays | ||
| 2 | 0.00 | 0.00 | 0.00 | ||
| 3 | 0.00 | ||||
| 4 | 1.00 | 12/6/21 | |||
| 5 | 2.00 | 12/7/21 | |||
| 6 | 3.00 | 12/7/21 | |||
| 7 | 0.00 | 12/8/21 | |||
| 8 | 0.00 | ||||
| 9 | 0.00 | ||||
| 10 | 1.00 | 12/13/21 | |||
| 11 | 2.00 | 12/13/21 | |||
| 12 | 0.00 | 12/13/21 | |||
| 13 | 0.00 | ||||
| 14 | 1.00 | 12/14/21 | |||
| 15 | 2.00 | 12/14/21 | |||
| 16 | 3.00 | 12/15/21 | |||
| 17 | 0.00 | 12/15/21 | |||
| 18 | 1.00 | 12/15/21 | |||
| 19 | 2.00 | 12/15/21 | |||
| 20 | 3.00 | 12/15/21 | |||
| 21 | 4.00 | 12/15/21 | |||
| 22 | 5.00 | 12/16/21 | |||
| 23 | 6.00 | 12/16/21 | |||
| 24 | 7.00 | 12/16/21 | |||
| 25 | 8.00 | 12/19/21 | |||
| 26 | 9.00 | 12/19/21 | |||
| 27 | 10.00 | 12/20/21 | |||
| 28 | 11.00 | 12/20/21 | |||
| 29 | 0.00 | 12/20/21 | |||
| 30 | 0.00 | ||||
| 31 | 1.00 | 12/20/21 | |||
| 32 | 0.00 | 12/20/21 | |||
| 33 | 1.00 | 12/20/21 | |||
| 34 | 2.00 | 12/21/21 | |||
| 35 | 3.00 | 12/21/21 | |||
| 36 | 4.00 | 12/21/21 | |||
| 37 | 5.00 | 12/21/21 | |||
| 38 | 6.00 | 12/21/21 | |||
| 39 | 7.00 | 12/21/21 | |||
| 40 | 8.00 | 12/22/21 | |||
| 41 | 9.00 | 12/22/21 | |||
| 42 | 0.00 | 12/22/21 | |||
| 43 | 1.00 | 12/22/21 | |||
| 44 | 0.00 | 12/22/21 | |||
| 45 | 1.00 | 12/22/21 | |||
| 46 | 2.00 | 12/23/21 | |||
| 47 | 3.00 | 12/23/21 | |||
| 48 | 4.00 | 12/23/21 | |||
| 49 | 0.00 | 12/28/21 | |||
| 50 | 1.00 | 12/27/21 | |||
| 51 | 2.00 | 12/27/21 | |||
| 52 | 3.00 | 12/28/21 | |||
| 53 | 4.00 | 12/28/21 | |||
| 54 | 5.00 | 12/28/21 | |||
| 55 | 6.00 | 12/28/21 | |||
| 56 | 7.00 | 12/28/21 | |||
| 57 | 8.00 | 12/29/21 | |||
| 58 | 9.00 | 12/29/21 | |||
| 59 | 10.00 | 12/29/21 | |||
| 60 | 11.00 | 12/29/21 | |||
| 61 | 12.00 | 12/29/21 | |||
SAT | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| AH3:AH61 | AH3 | =IF($AF3=0,0,$AH2+1) |
| AI3:AI61 | AI3 | =IF(AH3=0,"", IF(AND(AH3=0,AH2>=1),"",M3)) |
| AJ3:AJ61 | AJ3 | =IF(AND(AH3=0,AH2>0),M3,"") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| AF:AF,AG1:AK61 | Cell Value | <0 | text | NO |
