Simple formula needed

[thurmbo]

Hi, I believe I need an "IF" formula to figure out the % of target achieved for stock trading.

If a LONG trade: I purchase 100 shares at $49 and my target is $52, then I sell at $51.50, what is the % of target achieved?

If a SHORT trade: I Short 100 shares at $49 and my target is $46, then I close the trade at $47, what is the % target achieved?

Thanks you for your help!

 

[jbvinny]

cell references would help.

 

[thurmbo]

cell references would help.

Sorry (and thanks), I hope this helps:

Having troubles posting a picture, but what I'm trying to do can be viewed here.

Many thanks!

 

[jbvinny]

=IF($A1="Long",$F1/$B1,($F1/$B1)-1)

Assuming "Long" and "Short" are your only options in for Column A. Don't forget that any spaces before or after "Long" or "Short" in column A would through this off so it might be worth using a drop down for consistency.

I apologize if this isnt exactly right but it should be close to what you need.

 

[thurmbo]

=IF($A1="Long",$F1/$B1,($F1/$B1)-1)

Assuming "Long" and "Short" are your only options in for Column A. Don't forget that any spaces before or after "Long" or "Short" in column A would through this off so it might be worth using a drop down for consistency.

I apologize if this isnt exactly right but it should be close to what you need.

jbvinny, Thanks a bunch for the formula! It so happens that it works perfectly for a LONG trade, but not for a SHORT trade, (dependent on Column A).

I am trying to play around with your formula, but so far not getting it. Would you have another suggestion?

Appreciated,
Greg

 

[jbvinny]

How about?

=IF($A3="Long",$F3/$B3,1-(($F3/$B3)-1))

I am assuming by it not working that you meant the result was not what was desired. Try this on a few different scenerios...should work.

 

[jbvinny]

Nevermind that will only work if you sold the short for more than target...I will look at it again.

 

[jbvinny]

=IF($A2="Long",$F2/$B2,$B2/$F2)

I think this actually works.

 

[thurmbo]

=IF($A1="Long",$F1/$B1,($F1/$B1)-1)

Assuming "Long" and "Short" are your only options in for Column A. Don't forget that any spaces before or after "Long" or "Short" in column A would through this off so it might be worth using a drop down for consistency.

I apologize if this isnt exactly right but it should be close to what you need.

Nevermind that will only work if you sold the short for more than target...I will look at it again.

Correct, and the outcome will also be determined whether it was a Winning or Losing trade.

Here is an actual screenshot of the exact column/row of my trading spreadsheet. Hopefully I get this to you before you reply again

Greg

 

[jbvinny]

=IF($A2="Long",$F2/$B2,$B2/$F2)

I think this actually works.

Did the above not work?