Populate Based on Criteria

[kumara_faith]

Hi,

I have the following table:

Stock Market Buy Sell Model.xlsx
	K	L	M	N	O
2	Date	Price	% Difference	Buy	Sell
3	1/1/2021	100	-3%	100
4	2/1/2021	105	5%		105
5	3/1/2021	95	-10%	95
6	4/1/2021	115	21%		115
7	5/1/2021	85	-26%	85
8	6/1/2021	120	41%		120
9	7/1/2021	119	-1%
10	8/1/2021	118	-1%
11	9/1/2021	117	-1%
12	10/1/2021	115	-2%	115
13	11/1/2021	85	-26%
14	12/1/2021	120	41%		120
15	13/1/2021	105	-13%	105
16	14/1/2021	95	-10%
17	15/1/2021	115	21%		115
Sheet1

Cell Formulas
Range	Formula
M4:M17	M4	=(L4/L3)-1

I am trying to build a formula in column N and column O as follows:

1) If column M is down by 2% or more, then we will buy 1 unit of the product for the price in column L
2) If column M is up by 2% or more, then we will sell 1 unit of the product for the price in column L
3) We can only buy and sell once. Which means if we have bought previously, we will not buy again even when the price is down by 2% or more until we have sold. The same applies for sell as well

The correct result is as per the table above. I tried using the following IF formula but it does not seems to work.

=IF(M3<=-2%,L3,"")

Appreciate all the help.

 

[Marcelo Branco]

Maybe...
Note that I got a different result on row 12 (-1.71% is not less than or equal to -2%)

Pasta1
	K	L	M	N	O
1
2	Date	Price	% Difference	Buy	Sell
3	01/01/2021	100	-3,00%	100
4	02/01/2021	105	5,00%		105
5	03/01/2021	95	-9,52%	95
6	04/01/2021	115	21,05%		115
7	05/01/2021	85	-26,09%	85
8	06/01/2021	120	41,18%		120
9	07/01/2021	119	-0,83%
10	08/01/2021	118	-0,84%
11	09/01/2021	117	-0,85%
12	10/01/2021	115	-1,71%
13	11/01/2021	85	-26,09%	85
14	12/01/2021	120	41,18%		120
15	13/01/2021	105	-12,50%	105
16	14/01/2021	95	-9,52%
17	15/01/2021	115	21,05%		115
Plan4

Cell Formulas
Range	Formula
N3:N17	N3	=IF(AND($M3<=-0.02,IFERROR(MATCH(9.99E+307,N$2:N2),0)<=IFERROR(MATCH(9.99E+307,O$2:O2),0)),$L3,"")
O3:O17	O3	=IF(AND($M3>=0.02,IFERROR(MATCH(9.99E+307,O$2:O2),0)<=IFERROR(MATCH(9.99E+307,N$2:N2),0)),$L3,"")
M4:M17	M4	=(L4/L3)-1

Hope this helps

M.

 

[kumara_faith]

Hi Marcelo,

That worked. Thank you and appreciate your valuable time and patience. Have a great day ahead.?

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.

 

[kumara_faith]

Hi Marcelo,

My apologies. I have a further query on the solution. I have the formula in the input sheet below:

Buy and Hold.xlsx
	B	C	D	E	F
4	Date	Price	% Difference	Buy	Sell
5	9/5/1996	23.2
6	10/5/1996	24	3.45%		24
7	13/5/1996	23.9	-0.42%
8	14/5/1996	23.6	-1.26%
9	15/5/1996	23.2	-1.69%
10	16/5/1996	22.6	-2.59%	22.6
11	17/5/1996	22.8	0.88%
12	20/5/1996	22.5	-1.32%
13	21/5/1996	22.6	0.44%
14	22/5/1996	22	-2.65%
15	23/5/1996	22.1	0.45%
16	24/5/1996	21.6	-2.26%
17	28/5/1996	21.2	-1.85%
18	29/5/1996	20.8	-1.89%
19	30/5/1996	20.5	-1.44%
20	31/5/1996	20.4	-0.49%
21	3/6/1996	20.1	-1.47%
22	4/6/1996	21.4	6.47%		21.4
Sheet1

Cell Formulas
Range	Formula
E5:E22	E5	=IF(AND($D5<=-0.02,IFERROR(MATCH(9.99E+307,E$4:E4),0)<=IFERROR(MATCH(9.99E+307,F$4:F4),0)),$C5,"")
F5:F22	F5	=IF(AND($D5>=0.02,IFERROR(MATCH(9.99E+307,F$4:F4),0)<=IFERROR(MATCH(9.99E+307,E$4:E4),0)),$C5,"")
D6:D22	D6	=(C6/C5)-1

1) Is there a way to modify to the formula to start at buy and not sell ? In the table above the formula populates the results for sell first.
2) In cell E10, the formula buys at price 22.6. Then in cell F22, the formula sells at price 21.4. Is there a way to modify the formula to sell not only if the changes in price is more than 2% (existing criteria) but also higher than the buy price ?

Appreciate all the help.

 

[kumara_faith]

Hi,

Appreciate any help.

 

[kumara_faith]

Hi,

I have cross post here:

Populate Based On Criteria [SOLVED]

Hi, I have the attached table. The formula in column E and F are based on the following criteria: 1) If the price drops by 2%, then buy 2) When the price rises by 2%, then sell 3) You can only buy once and can only buy again after you sell

www.excelforum.com