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

arcu5

New Member
Joined
Jan 29, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
.
Index.JPG
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/
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Can you clarify your issue please? your formula seems to be working so what's missing?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,884
Messages
6,181,558
Members
453,053
Latest member
Kiranm13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top