Jan 27 2021 01:33 AM
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.
Jan 27 2021 09:12 AM
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?
Jan 27 2021 11:20 AM
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?
Jan 27 2021 11:42 AM
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.
Jan 27 2021 12:23 PM
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.
Jan 28 2021 12:39 AM
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.
Jan 29 2021 02:09 PM
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))