Index Formula

Copper Contributor

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.

6 Replies
Please clarify how you determine which number came first.

@Twifoo 

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

It would be better if you fill in Columns K and N with your desired results. Then, attach again your sample file with such desired results. Thereby, I will be able to ascertain the logic of the formula you need.

@Twifoo 

Sorry for the delay. I have attached a new sample worksheet with my manual calculations for your review. Thanks again for the help.

Given the sum results in Column C, the formula in D8, copied down rows, would be a simple IF, like this:
=IF(C8>0,
"WIN",
"LOSE")

@Twifoo 

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