persistant value in the cell

Hello,

I want to monitor the buying and selling activity of a stock using Excel.

1. If the current stock price falls below a specified buy price, I want a column (let's call it "Bought") to display "Y." This "Y" should persist unless someone manually removes it.

2. If the current stock price rises above a specified sell price, I want another column (let's call it "Sold") to display "Y." Like "Bought," this "Y" should persist unless manually deleted.

Stock formula.png

Column G reflects changes as the current stock price fluctuates.

What's working:
The attached formula functions correctly when the current stock price falls below the buy price. In this case, the "Bought" column (J) displays "Y."

What's not working:

1. If the stock price surpasses the buy price, the "Bought" column (J) shows null, even though a purchase was made.
2. Similarly, when the stock price exceeds the selling price, the "Sold" column (K) displays null due to the scenario where "Bought" is null.

Table displaying expected behavior

Here is a table illustrating the anticipated behavior of the "Bought" and "Sold" columns in response to changes in the stock price:

Stock Table.png

The "Bought" column is set to "Y" as soon as the current stock price falls below the buy price, and it remains as "Y."
The "Sold" column is marked as "Y" when the current stock price exceeds the sale price, and "Bought" is also marked as "Y."

I appreciate your guidance. Thank you in advance.

I think you will need a macro for that since a function will look of the current price in G5 and not in past values.