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.
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?
