How to Calculate Trade Running?

[Omer_Ad]

Hello,

I need help with filling the column "Trade Running" in sheet 1 in the attached Excel File.

When a Buy signal (in Column C) appears, then trade running becomes from 0 to 1. If the stop loss price corresponding with the buy signal (column E) is breached subsequently before there is a sell signal, then the trade running becomes '0'. Otherwise, the trade running signal '1' continues until a sell signal is reached. That is, at each buy signal the trade running column becomes 1 until the stop loss price is reached in column B or Sell signal is generated in column C.

Someone provided me with this formula:

=IF(LOOKUP("zzzzz",$C$1:C2)<>"Buy",0,IF(B2>=LOOKUP(10^35,$E$2:E2),1,0))

It works on this file, but doesn't work on my data. What is "Lookup(10^35..." implying?

If anyone can find the formula for trade running in a different way, I will really appreciate it.

[#2]

@Omer_Ad 

Actually LOOKUP("zzzzz",$C$1:C2) returns last non-empty value in current range (from C1 to current row) for the column C, and LOOKUP(10^35,$E$2:E2) returns largest (more exactly largest, but less than 10^35 value) in the current range in column E.

Formula works correctly, perhaps you have non-blank cells in your actual file which only looks like blank?

[#3]

@Sergei Baklan 

Thanks for your response. I really appreciate this.

I have 2423 rows in my data. Column C in this file is column P in my file. Column B in this file is column B in my file. And, column E in this file is column AJ in my file. So, when I change the formula for columns, it should look like:

=IF(LOOKUP("zzzzz",$P$1:P2)<>"Buy",0,IF(B2>=LOOKUP(10^35,$AJ$2:AJ2),1,0))

I checked and deleted everything in my target column. However, what happens is that, I get all 0s except 1s where there is a buy signal. In other words, the 1s ought to continue until the stop loss is reached or the sell signal is reached (whichever comes first), but 1s only appear next to the buy signals in my file...am I making a mistake when switching the formula from this file to my file?

[#4]

@Sergei Baklan 

For instance, when I add the formula in the file I have attached in this post, I don't get the results that I am getting in the file I attached in my first post.

[#5]

@Omer_Ad 

That since the cells in column C which are empty are not blank, they have some not-printable character. You may select all such empty cells and clean them by Del, or modify the formula as

=IF(LOOKUP(2,1/(LEN($C$1:C2)>0),$C$1:C2)<>"Buy",0,IF(B2>=LOOKUP(10^35,$E$2:E2),1,0))

Most probably you copy/paste data from web or like.

[#6]

@Sergei Baklan 

Thank You for your response.

However, this formula doesn't work because if the price goes below the stop loss price, column F becomes 0 and, after that, becomes 1 once again (when it should become 1 at the next buy signal). For instance, in the attached file, in row 289 there is a buy signal with a corresponding stop loss price of 5.0847...in row 295 the price goes below the stop loss price, and the trade is terminated. However, row 296 becomes 1 again in column F. This should become 1 in row 376 when there is a buy signal.

I will highly appreciate it if you can provide me a formula to help me navigate this problem.

[#7]

@Omer_Ad 

I added couple of named cells

Please note they are with RELATIVE references. With that formula could be

=IF(LOOKUP(2,1/(LEN($C$1:C2)>0),$C$1:C2)<>"Buy",0,IF(B2>=LOOKUP(10^35,$E$2:E2),IF((LEN(CellLeft)=0)*(CellUp=0),0,1),0))