# Find the next 10% drop

#### phil133

##### Active Member
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!

### 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
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

##### Active Member
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
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

##### Active Member

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

##### Active Member

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

#### FlameRetired

##### Active Member
I am not able to copy from a screenshot.

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

#### phil133

##### Active Member
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
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?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,994
Messages
5,856,699
Members
431,828
Latest member
kARTIK12345

### 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.

### Which adblocker are you using?

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

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