Forum Discussion

Rdfallon's avatar
Rdfallon
Copper Contributor
May 10, 2019

Index Formula

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)....
excel
Formulas and Functions
Rdfallon's avatar
Rdfallon
Copper Contributor
May 11, 2019

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

Twifoo's avatar
Twifoo
Silver Contributor
May 12, 2019
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's avatar
      Twifoo
      Silver Contributor
      May 20, 2019
      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")
      • Rdfallon's avatar
        Rdfallon
        Copper Contributor
        May 20, 2019

        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