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

Polat

New Member
Joined
Jul 3, 2018
Messages
19
Hello Dears,

show.php

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.
 
Try:

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

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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

show.php
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top