table formula issues

[MonsterMan]

I am trying to get this simple. Excel table to work. The idea is show if all trades that are made, whether, Winners or Losers. If the trade is a Long, the exit price need to be higher than the entry price to be a winner, if it is less, then the trade will be a loser.


Setup – Long
Entry – 10
Exit 12
Winner (will show ‘Winner’)
Setup – Long
Entry – 10
Exit 9
Loser (will show ‘Loser’)


The same goes for a short trade. The exit price needs to be lower than the entry price to be a winner. If it is higher than the entry price, then the trade is a loser.
Setup – Short
Entry – 10
Exit – 9
Winner (will show ‘Winner’)
Setup – Short
Entry 10
Exit – 11
Loser (will show ‘Loser’)


The formula I have so far is this, but does not seem to be working. Can anyone help me out please?


=IF[Entry]="",[Exit]="","",IF([Setup]="Long", IF[Exit]-[Entry]>0,"Winner","Loser", =IF[Entry]="",[Exit]="","",IF([Setup]="Short", IF[Exit]-[Entry]<0,"Winner","Loser")

I would attach the spread sheet, but I can't seem to find a button at attach. So, I have uploaded a screenshot - https://imgur.com/kKBfLMZ

Thank MM

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe...

=IF([@Setup]="Long",IF([@Exit]>[@Entry],"Winner","Looser"),IF([@Entry]>[@Exit],"Winner","Looser"))

Hope this helps

M.

 

[MonsterMan]

Hi, thats works perfectly. Thankyou. Is there anyway to change it, so I only get the winner/loser to be shown after I enter the values in the setup/Entry/Exit cells, so once the exit is entered, it will change to winner or Loser? As you can see, on the 3^rd trade, it shows Winner, even thou there is not value entered in the exit cell. I’ve added another screen shot - https://imgur.com/4bUKNPG

 

[Marcelo Branco]

Try this

=IF(OR([@Setup]="",[@Entry]="",[@Exit]=""),"",IF([@Setup]="Long",IF([@Exit]>[@Entry],"Winner","Looser"),IF([@Entry]>[@Exit],"Winner","Looser")))

M.

 

[MonsterMan]

Hi again, thanks for the great help. I have added more features to the spreadsheet, I think all the formula is correct, but I do have another problem.
On the first entry, a ‘Short’. The table shows -0.57 risk per share, -1.75 Profit per share, Return $ (1,750.00) and Return % -18%. Would it be possible to have both Long and Short trades to be to be shown without brackets or ‘-‘ when they are winning trades. If the trade is a Loss, then can we have the brackets and ‘-‘shown on that that trade to show that it was a loss? I have uploaded it to - https://nofile.io/f/WyBgBAF4wNQ/Book1.xlsx