Find the next 10% drop

[phil133]

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!

 

[FlameRetired]

Array-entered in D2 and filled down.

=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]

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?

 

[FlameRetired]

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]

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]

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.

 

[phil133]

Thanks for your effort but the problem still persists View image: foto1

 

[FlameRetired]

I am not able to copy from a screenshot.

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

 

[phil133]

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]

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?