Stop calculation once a condition is met

hi everyone,

So I've been working hard on this spreadsheet to make it track stocks prices live. it gets a live prices from my broker and multiple formulas are ran using these prices. One of them is a "Stop-Loss" Flag function, that constantly checks if current market price (that is set as a "stop-loss" level) has been reached or not (i.e A1 is the "live" price cell;

Please Login or Register  to view this content.

).
My problems are:

1. the price keeps fluctuate after the condition has been met, and if the price then goes back above 1.5, the condition is now FALSE again and if I come back an hour later I can't tell if the stop-loss level had been reached (I know what you're thinking, I can just check the stock chart, but I'm actually using multiple option contracts prices and the example given here is only to simplify things). So my question is, how can I tell excel "once the condition (A1<1.5) is TRUE, populate the cell with text ("Stop-Loss") and then STOP checking A1, so that the text is always there).

2. Also, if possible, I'd like excel to register the price of that first cross below 1.5 (so if the price that triggered the stop-loss was 1.48 I want it to save it somewhere so I can come back later and know what price I got out at..

3. Last but not least, right now I have to manually match the strike price of the option (E6) to the corresponding "live" price of that strike (S4:S13) so excel knows what prices to work with. I want a formula that will look like this:

look for the value in E6 in range S4:S13, once you've found it, compare the Stop Loss trigger price (O7) to the live price of that strike

. I know it sounds really complicated but really it as simple as it get, I just don't know how to do it.. so in the spreadsheet example, since the strike price in E6=15, excel needs to compare the trigger value (O7) against the prices of the 15 strike price (T6 and U6) and ignore all other prices..


I hope my explanation was not too clumsy and that my goal is clear. see attached spreadhseet to see what it looks like right now

thanks in advance

Dupl.

You should continue discussion vba is needed in your situation.

http://www.excelforum.com/excel-prog...condition.html

Thread Closed.