Populate Based On Criteria

[Kumara_faith]

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


So far the formula works but the formula sells after buying even though the selling price is lower than the buying price. Example is in cell E10, we bought at 22.6 after the price fell more than 2% but sold in cell F22 for 21.4 after price changes by more than 6%. However, this price is lower than the buy price.

Is there a way to modify this formula to add the criteria that it can only sell if the change in the price is more than 2% but also the sell price must be more than the buy price ?

Appreciate all the help.

I have a cross post here:

https://www.mrexcel.com/board/thread.../#post-5808181

[Glenn Kennedy]

Maybe in E5
=IF(AND($D5<=-0.02,IFERROR(MATCH(10^10,E$4:E4),0)<=IFERROR(MATCH(10^10,F$4:F4),0)),$C5,"")

and in F5
=IF(AND(N(LOOKUP(2,1/(E$4:E4<>""),E$4:E4))<C5,D5>=0.02,IFERROR(MATCH(10^10,E$4:E4),0)>=IFERROR(MATCH(10^10,F$4:F4),0)),C5,"")

I messed up your formulae in parts of G H and I...

[kvsrinivasamurthy]

Try this . A small change in second formula of Sell.
In F5 then copied down

=IF(AND($C5>$C$5,$D5>=0.02,IFERROR(MATCH(9.99E+307,F$4:F4),0)<=IFERROR(MATCH(9.99E+307,E$4:E4),0)),$C5,"")

[Kumara_faith]

Hi Glenn and murthy,

That worked. Thank you very much for your valuable time and patience. Have a great day.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.