Forum Discussion
How to Calculate Trade Running?
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?
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.
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.
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.
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))