Conversation Re: Index Formula in Excel
https://techcommunity.microsoft.com/t5/excel/index-formula/m-p/560505#M30813
Please clarify how you determine which number came first.Sat, 11 May 2019 05:38:30 GMTTwifoo2019-05-11T05:38:30ZIndex Formula
https://techcommunity.microsoft.com/t5/excel/index-formula/m-p/558353#M30790
<P>I am working on a stock market index analysis. I need help in developing the formulas for columns “K” and column “N”.</P><P> </P><P>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.</P><P> </P><P>I am trying to determine which bets would be successful or not (columns “K” & “N’).</P><P> </P><P>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”.</P><P> </P><P>The spread sheet model is attached for your reference. I appreciate any assistance you can provide.</P>Fri, 10 May 2019 13:37:43 GMThttps://techcommunity.microsoft.com/t5/excel/index-formula/m-p/558353#M30790Rdfallon2019-05-10T13:37:43ZRe: Index Formula
https://techcommunity.microsoft.com/t5/excel/index-formula/m-p/560505#M30813
Please clarify how you determine which number came first.Sat, 11 May 2019 05:38:30 GMThttps://techcommunity.microsoft.com/t5/excel/index-formula/m-p/560505#M30813Twifoo2019-05-11T05:38:30ZRe: Index Formula
https://techcommunity.microsoft.com/t5/excel/index-formula/m-p/560798#M30824
<P><LI-USER uid="280482"></LI-USER> </P><P>Thank you for your response.</P><P>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.</P><P>As I look at the worksheet cells J5 and M4 should probably be Negative numbers.. Hope this helps. Thanks</P>Sat, 11 May 2019 14:34:30 GMThttps://techcommunity.microsoft.com/t5/excel/index-formula/m-p/560798#M30824Rdfallon2019-05-11T14:34:30ZRe: Index Formula
https://techcommunity.microsoft.com/t5/excel/index-formula/m-p/562294#M30898
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.Mon, 13 May 2019 05:32:27 GMThttps://techcommunity.microsoft.com/t5/excel/index-formula/m-p/562294#M30898Twifoo2019-05-13T05:32:27ZRe: Index Formula
https://techcommunity.microsoft.com/t5/excel/index-formula/m-p/612567#M31261
<P><LI-USER uid="280482"></LI-USER> </P><P>Sorry for the delay. I have attached a new sample worksheet with my manual calculations for your review. Thanks again for the help.</P>Sat, 18 May 2019 18:06:57 GMThttps://techcommunity.microsoft.com/t5/excel/index-formula/m-p/612567#M31261Rdfallon2019-05-18T18:06:57ZRe: Index Formula
https://techcommunity.microsoft.com/t5/excel/index-formula/m-p/614958#M31309
Given the sum results in Column C, the formula in D8, copied down rows, would be a simple IF, like this:<BR />=IF(C8>0,<BR />"WIN",<BR />"LOSE")Mon, 20 May 2019 10:07:40 GMThttps://techcommunity.microsoft.com/t5/excel/index-formula/m-p/614958#M31309Twifoo2019-05-20T10:07:40ZRe: Index Formula
https://techcommunity.microsoft.com/t5/excel/index-formula/m-p/615424#M31320
<P><LI-USER uid="280482"></LI-USER> </P><P>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</P>Mon, 20 May 2019 13:08:31 GMThttps://techcommunity.microsoft.com/t5/excel/index-formula/m-p/615424#M31320Rdfallon2019-05-20T13:08:31Z