Need Help with Formula to count how long the stock market is in a Drawdown of >20%

[strolf]

Long time lurker, first time poster. Looking for a way to count how long the stock market is in a Drawdown of >20%. Not sure if I need COUNT, ROW, MIN MAX...I'm really lost on this one. Let's say the stock market is 5% below it's all time high, and then goes 20% below it's high. I'd like to start counting from here...how many months it takes for the stock market to get back to its high, or 0.0%. I'd also like to get the max Drawdown % in column E. (I've expressed Drawdowns in positive form in my example). The "count" shouldn't show up unless the number in column D is > 20%, and then the Max will show up in E. I've given the "count" i'd like to get in Column D. Any help is appreciated, thanks in advance. Matt

[XLent]

I'm not quite sure I follow your result for the 3rd series - given C73 is 0%

Taking above into consideration, one option might be:

Please Login or Register  to view this content.

modify range to $1001 to encapsulate all your rows but don't make excessively large (for sake of performance)

note: if you want the % in E rather than text "Max Drawdown" swap out the REPT element and replace with something like $C2*($C2=MAX...)

[strolf]

Worked like a charm...thank you. Matt

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.