Stop Loss Backtesting Formula

Hi, I’m backtesting a trading strategy and currently focusing on optimising a stop loss percentage. I have 8.5 years worth of data and I want to be able to tweak a Stop Loss percentage number to see what would have given optimum results. For the trades that were successful, I want the formula to give me either the Exit price (if the trade was completed) or the Stop Loss price (if the lowest price during the trade fell below the Stop Loss and triggered it.


A = Exit price (if stop is not triggered)
B = Stop Loss

C = Lowest price for the duration fo the trade

D = Result : Either A or B depending on whether the Stop loss was triggered by C falling below B




I need a formula that says:

D = A

unless

C falls below B,

in which case

D = B



Example 1:
A (Exit Price) = 100
B (Stop Loss) = 90
C (Lowest) = 98
In this case, D = 100
(lowest price did not go under stop loss, therefore D = A (Exit price)


Example 2:
A (Exit Price) = 100
B (Stop Loss) = 90
C (Lowest) = 85
In this case, D = 90
(lowest price did go under stop loss and triggered it, therefore D = B (Stop loss)


Any ideas greatly appreciated!

Thanks so much! I've been laying awake at night trying to figure this out in my head! What a dumbo.

Thats perfect.

If anyone else is looking for the same answer, this is for Longs.

=IF(C1<B1,A1,B1)

For Shorts, this seems to work:

=IF(C1>B1,A1,B1)