Find the next 10% drop

phil133

Board Regular
Joined
May 5, 2015
Messages
235
Hi. I'm going straight to the example because you will understand it much easier. Column B is daily stock prices.
A1=1/10/2009
Column A Column B Column C
1/10/2009 28 ""
2/10/2009 29 ""
3/10/2009 30 2
4/10/2009 31 ""
5/10/2009 26 ""
6/10/2009 27 2
7/10/2009 24 ""

I'm looking for a formula that will give me the date in which the price dropped 10% or more when I bought the stock (2 means that I bought the stock then). So the first 2 in column C is at price 30 and at date 3/10/2009. I want to get a 3 in column D when it reaches below 30*(1-10%). That happens at 5/10/2009. After that, I buy the stock again at 6/10/2009. It falls more than 10% on 7/10/2009. So I would like a 3 in column D.

Hope you understand what I'm trying to do!
Thanks for any help!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

FlameRetired

Active Member
Joined
Feb 19, 2016
Messages
326
Array-entered in D2 and filled down.
Code:
=MAX(IF(B2<=((0.9*$B$2:B2)*($C$2:C2=2)*(C2<>2)),3))
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Not sure how this will work with your live data.


Row\Col
A​
B​
C​
D​
E​
1​
Date
Price
Buy
Sell
2​
1/10/2009​
28​
""
0​
Array entered in D2 and filled down.
3​
2/10/2009​
29​
""
0​
=MAX(IF(B2<=((0.9*$B$2:B2)*($C$2:C2=2)*(C2<>2)),3))
4​
3/10/2009​
30​
2​
0​
5​
4/10/2009​
31​
""
0​
6​
5/10/2009​
26​
""
3​
7​
6/10/2009​
27​
2​
0​
8​
7/10/2009​
24​
""
3​
 

phil133

Board Regular
Joined
May 5, 2015
Messages
235
Thank you for your answer! D6 rightly is 3 but I get 3 again at D7. When I get the first 3, I want it to wait for the next 2 in column C to find the next 3. Can this be done?
 
Last edited:

FlameRetired

Active Member
Joined
Feb 19, 2016
Messages
326
Isn't that what my example does?

It sounds like you may need to try and copy / paste the formula again. I had the same difficulty before I included the element *(C2<>2) in the formula.
 

phil133

Board Regular
Joined
May 5, 2015
Messages
235

ADVERTISEMENT

Thank you for your answer. I tried the formula many times with ctrl and shift but the same thing. I paste a screenshot and would be grateful if you could take a look. View image: foto
 

FlameRetired

Active Member
Joined
Feb 19, 2016
Messages
326
The range in your formula starts one row below where it needs to in order to work.

Try changing the formula in your screenshot to

=MAX(IF(B4<=((0.9*$B$4:B4)*($C$4:C4=2)*(C4<>2)),3))

and array-entering it in D4 and fill down.
 

FlameRetired

Active Member
Joined
Feb 19, 2016
Messages
326
I am not able to copy from a screenshot.

Can you post your new data as you did in your first post?
 

phil133

Board Regular
Joined
May 5, 2015
Messages
235
01/10/2009 28 0 0
02/10/2009 29 0
05/10/2009 30 2 0
06/10/2009 31 0
07/10/2009 26 3
08/10/2009 27 3
09/10/2009 24 3
12/10/2009 23.01 3
13/10/2009 22.99 3
14/10/2009 22.86 3
15/10/2009 21.72 3
16/10/2009 21.43 3
19/10/2009 21.49 3
20/10/2009 20.9 3
21/10/2009 22.22 3
22/10/2009 20.69 3
23/10/2009 22.27 3
26/10/2009 24.31 3
 

FlameRetired

Active Member
Joined
Feb 19, 2016
Messages
326
Hmm. Yes I see now, and every work-around I try is not working. My apologies.

Have not found an effective way to do continuous look back for already existing 3s. Will keep trying.

Are you able to accommodate helper columns?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

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
Top