May 10 2019 06:37 AM
I am working on a stock market index analysis. I need help in developing the formulas for columns “K” and column “N”.
I can bet the index (column “F”) will either go up (Long) or go down (Short). I can also set a Target (columns “I” &“L”) and a Stop Loss (columns “J & “M”) price on every bet. I can change the Target in cell J4 & M4 and change the Stop Loss in cells J5 & M5.
I am trying to determine which bets would be successful or not (columns “K” & “N’).
To make this Win or Lose determination I need to determine which number came first in the Close column (column “F”) the Target number (column “I”) or Stop Loss number (column “J”). If the Target number came first it is a “WIN” if the Stop Loss number came first it is a “LOSE”. The same determination needs to be made for columns “L” & “M”.
The spread sheet model is attached for your reference. I appreciate any assistance you can provide.
May 11 2019 07:34 AM
Thank you for your response.
I currently manually visually add and subtract the numbers in column G to determine when the sum = or greater/less than the number in J4 or J5 and then do it again for M4 and M5. My issue is that I cannot find a formula that will sum the numbers in column G to reach the Target or Stop Loss number and then drop one cell and repeat the calculation. Example is G10 + G11 =M4 which is a Short Result WIN(N9) because the sum of the numbers in column G were = or less than M4 before M5. Where as the Long Result is a LOSE because the sum of F9:F40 =J5 before the sum number in J4.
As I look at the worksheet cells J5 and M4 should probably be Negative numbers.. Hope this helps. Thanks
May 12 2019 10:32 PM
May 18 2019 11:06 AM
Sorry for the delay. I have attached a new sample worksheet with my manual calculations for your review. Thanks again for the help.
May 20 2019 03:07 AM
May 20 2019 06:08 AM
Thanks for the reply, the formula works great in column D but what I need is the formula for column "C" so that I don't have to calculate it manually. Column "C" sums the numbers in Column "B" until the sum either reaches a positive $10 or a negative $10. Thanks