# Thread: Trying to make a formula to calculate the Profit and Lose in Forex trades. Thanks:  1 Post #5105942 (1) Likes: 0

1. ## Re: Trying to make a formula to calculate the Profit and Lose in Forex trades.

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.  Reply With Quote

2. ## Re: Trying to make a formula to calculate the Profit and Lose in Forex trades.

Hello Eric,

Thanks a lot for doing your best to help me

But unfortunately the formulas you provide is not working.

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.  Reply With Quote

3. ## Re: Trying to make a formula to calculate the Profit and Lose in Forex trades.

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  Reply With Quote

4. ## Re: Trying to make a formula to calculate the Profit and Lose in Forex trades.

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  Reply With Quote

5. ## Re: Trying to make a formula to calculate the Profit and Lose in Forex trades.

The "format" suboption of CELL does not work on Excel Online. https://support.office.com/en-us/art...f-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.  Reply With Quote

6. ## Re: Trying to make a formula to calculate the Profit and Lose in Forex trades.  Reply With Quote

7. ## Re: Trying to make a formula to calculate the Profit and Lose in Forex trades.

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" :

Regards.  Reply With Quote

8. ## Re: Trying to make a formula to calculate the Profit and Lose in Forex trades.

You can try this:

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

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.  Reply With Quote

9. ## Re: Trying to make a formula to calculate the Profit and Lose in Forex trades.

The Last one is worked perfect !

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

Appreciated,. GOD BLESS you.

My Regards.  Reply With Quote

10. ## Re: Trying to make a formula to calculate the Profit and Lose in Forex trades.

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.  Reply With Quote

## User Tag List

dears, forex, lose, profit, trades 