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