Trying to make a formula to calculate the Profit and Lose in Forex trades.

[Polat]

Hello Dears,

As in image above : i am trying to make a formula for column J and K:

- As in column "J" i need the Pips In Lose to be calculated in case i type "L" in column "I"
- As in column "K" i need the Pips in Profit to be calculated in case i type "P" in column "I"
- In column "I" when i type BEP (Break Even Point) it should give 0 in both "J" and "K"

So, To calculate the Pips In Lose and Pips In Profit you have to check the following first:

1 - Check if the deal is (Buy/Buy Limit) SAME JOB Or (Sell/Sell Limit) SAME JOB "But Mist type the 4 types in formula"
2- The Price format for example EUR/USD is x.xxxx e.g 1.1600 , and EUR/JPY is xxx.xx e.g 129.00 , and Oil is xx.xx e.g 72.00.

I THINK FOR (1 AND 2) ABOVE, NEEDS TO USE NESTED IF , AND I DON'T KNOW HOW LOL


Now how to calculate Pips In Lose and Pips In Profit ..

* IN case the position is Buy Or Buy Limit :

Pips In Profit = Take Profit - Entry Point.
Pips In Lose = Entry Point - Stope Lose (result must be in minus.)

* In case the position is Sell or Sell Limit :

Pips In Profit = Entry Point - Take Profit.
Pips In Lose = Stope Lose - Entry Point (result must be in minus.)

Hope you help me with the formula to contains all the above cases ,

My Regards.

 

[Eric W]

Try:

=IF(H3="","",IF(H3=E3,0,IF(LEFT(D3)="B",H3-E3,E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0)))

 

[Polat]

Hello, Thanks for your time

But thet is not working , check (I3) is return nothing even whith the new formula you provide. It should return 0

 

[Polat]

Mr. Eric , you there?

 

[Eric W]

It occurs to me that you should be able to use the formula from post # 20. If H3 = E3, the formula will already return 0, no need to add the extra condition from post # 21. The reason your example in post # 22 is not showing the zero is probably the same reason from post # 19, whatever that reason may be.

Some possible reasons why a 0 does not display: I see you have Conditional Formatting on that column, green for profit, red for loss. Is there a rule that says if zero, hide it?

Also there is a setting under Options > Advanced > Display options for this worksheet > Show a zero in cells that have zero value. Make sure that's checked.

 

[Polat]

Also there is a setting under Options > Advanced > Display options for this worksheet > Show a zero in cells that have zero value. Make sure that's checked.

This one solved my problem lol thanks.