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]

1) To fix transaction 1, change the multiplier in the table for OIL from 10 to 100.

2) When posting a sample sheet, please post the expected results. On your original post, you had 30 as the result of the transaction, so that's what I tried to match. Had you posted 208, I would have made the OIL multiplier 100.

3) If you already have hidden columns, there's no reason you can't hide the Symbol/Multiplier table, or put it on another sheet.

4) There is no way with a normal Excel function that can detect how many decimals are used in the formatting. This makes me wonder how your data is entered. Do you enter it manually, then manually adjust the formatting? Or it is imported via some macro that adjusts the formatting at the same time? If it's via a macro, that macro can also be adjusted to insert the totals in columns J:K.

5) There is an odd Excel function that could do what you want. You can try these formulas:

J3: =IF(I3="BEP",0,IF(I3="P","",-ABS(F3-H3)*10^RIGHT(CELL("format",F3))))
K3: =IF(I3="BEP",0,IF(I3="L","",ABS(F3-G3)*10^RIGHT(CELL("format",F3))))

Copy down the columns as needed. The problem with these formulas is that they are not dynamic. If you change the formatting on F3, these formulas will not change. You'd need to manually recalculate them by pressing F9. Or if you change the values in F3:H3, remember to change the formatting first, then the values which does cause a recalculate. But that seems a bit risky if you forget. Also, using these formulas will require you to save your workbook as a macro-enabled workbook.

6) I wrote a UDF just to see how that would work. It has the same limitation as the CELL formula, it does not update as a result of a format change, so there's no benefit in going that route.


So that pretty much sums up your options.

 

[Polat]

Hello Eric,

Thanks a lot for doing your best to help me

But unfortunately the formulas you provide is not working.

Anyway you can access my sheet on Excel Online Click Here

This is a copy and i keep the original for me.

All what i want from you is to test all the types of (Buy , Sell , Buy Limit, Sell Limit, Buy Stop , Sell Stop)

Enter a deal by yourself and try to get the "Pips In Lose" and "Pips In Profit" depending on deal type and price format.

I am sure you are an expert and when you work on the sheet itself , you gonna find the solution !

All what i need at the end is to get the J AND k Auto-filler with correct result. Please without adding any column

Once the formulas are done correctly and tested 100% , Please come here and paste them to me.
Waiting your reply as soon as possible.

 

[Polat]

You can try those deals as test :

S/N	Symbol	Type	Entry Point	Take Profit	Stope Lose
1	EUR/USD	Buy	1.1600	1.1650	1.15050
2	EUR/JPY	Buy Limit	127.00	128.50	126.50
3	GBP/JPY	Buy Stop	145.00	146.20	144.80
4	GOLD	Sell	1250.00	1240.00	1260.00
5	OIL	Sell Limit	73.00	72.50	73.50
6	GBP/USD	Sell Stop	1.3200	1.3100	1.3260

<tbody>
</tbody>

 

[Polat]

1) EUR/USD should give 50 pips profit in case we type P and -50 pips lose in case L
2) EUR/JPY should give 150 pips profit in case we type P and -50 pips lose in case L
3) GBP/JPY should give 120 pips profit in case we type P and -20 pips lose in case L
4) GOLD should give 100 pips profit in case we type P and -100 pips lose in case L
5) OIL should give 50 pips profit in case we type P and -50 pips lose in case L
6) GBP/USD should give 100 pips profit in case we type P and -60 pips lose in case L

 

[Eric W]

The "format" suboption of CELL does not work on Excel Online. https://support.office.com/en-us/article/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf

In any case, I don't recommend using it, since it doesn't update dynamically. My best recommendation is to create a table like I showed, with the Symbol/Multiplier. You can even use the same table with Conditional Formatting to automatically show the right number of decimals so you don't have to constantly reformat the price cells.

But I'm not aware of any other ways to do what you want. Sorry.

 

[Polat]

Okay Thanks for your time.

 

[Polat]

Hello Mr. Eric ,

I made as you said and everything is okay now but :

- I still have 1 problem i think you can solve it for me and i will appreciate that really.

Check the following Image for explanations:

And here is the formula i have for the I3 cell "Profit" :

=IF(D3="Buy",(H3-E3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Buy Limit",(H3-E3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Buy Stop",(H3-E3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Sell",(E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Sell Limit",(E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Sell Stop",(E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0),0))))))

waiting your reply as soon as possible,

Regards.

 

[Eric W]

You can try this:

=IF(H3="","",IF(D3="Buy",(H3-E3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Buy Limit",(H3-E3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Buy Stop",(H3-E3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Sell",(E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Sell Limit",(E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0),IF(D3="Sell Stop",(E3-H3)*VLOOKUP(C3,$M$3:$N$35,2,0),0)))))))

Also, there's a lot of duplication in that formula. By consolidating some things, you get this:

=IF(H3="","",IF(OR(D3={"Buy","Buy Limit","Buy Stop"}),H3-E3,IF(OR(D3={"Sell","Sell Limit","Sell Stop"}),E3-H3))*VLOOKUP(C3,$M$3:$N$35,2,0))

In fact, if you take advantage of the fact that all 3 of your Buy options start with B, and if you only have those 6 options, you can get it all the way down to:

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

You may prefer the second version though, because it explicitly shows the options.

 

[Polat]

The Last one is worked perfect !

Thanks a lot my brother for helping me solving this issue ,

Appreciated,. GOD BLESS you.

My Regards.

 

[Polat]

Sorry for bother , but i need a last small thing from you and everything will be perfect!

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

What i need is to add to same formula this :

if H3 value = E3 value return 0 zero

Thanks.