Formula to identify changes of less than 20% and more than 20% over time?

[arcu5]

.

I have the following data from the S&P 500 index ranging from 1990-present. I need to do the following;

• identify all periods of market downturns and market upturns
• market upturn is defined as a period during which there is no downside move in the index greater than 20% and a downturn is defined as a period where there is a downside move greater than 20%.
• Ive started by identifying the peaks and troughs using the following formula =IF(AND((B2>B1),(B2>B3)),"peak",""), but Im not sure how to proceed/

 

[LouisT]

Can you clarify your issue please? your formula seems to be working so what's missing?

 

[arcu5]

Can you clarify your issue please? your formula seems to be working so what's missing?

Oh sorry I'm just not sure what to do after I have identified all the peaks and troughs. I know i have to check if there any periods where there is no downside move greater than 20%. But I'm not sure how to put a formula that will check the entire range for instances where there are no downside moves greater than that.