**Question description:**

*This user has given permission to use the problem statement for this blog.*

To write an Excel formula which calculates Notional based on the following criteria:
If there is USD in Currency Pair and:
1. the Currency equals the 1st currency in the Currency Pair, then Trade Amt multiply by Fwd Rate
2. the Currency equals the 2nd currency in the Currency Pair, then Trade Amt divided by Fwd Rate
And if there is no USD in Currency Pair,
then Trade Amt divided by Spot Rate.

Solved by T. Y. in 13 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
23/08/2017 - 10:46

Hi, It's pleasure to help you today:)

Excelchat Expert
23/08/2017 - 10:46

Explanation phase:

User
23/08/2017 - 10:46

Let me know if my question makes sense

Excelchat Expert
23/08/2017 - 10:46

I am checking it.

User
23/08/2017 - 10:47

If you can help me create one formula to cater for 3 scenarios stated, I would be grateful

Excelchat Expert
23/08/2017 - 10:47

Ok. I can work it out

Excelchat Expert
23/08/2017 - 10:47

I am writing the formula

User
23/08/2017 - 10:47

cool thanks

Excelchat Expert
23/08/2017 - 10:48

The formula is for Notional

User
23/08/2017 - 10:48

yes

Excelchat Expert
23/08/2017 - 10:48

And i Expect currency is in 3 letters

User
23/08/2017 - 10:49

the Currency refers to Column B e.g. GBP is equal to the 2nd currency in Column A

Excelchat Expert
23/08/2017 - 10:50

Whether all will be 3 letters only?

User
23/08/2017 - 10:50

yes 3 letters each currency

User
23/08/2017 - 10:51

I had written a formula but not complete coz only for the first 2 rows. I can share that with you if it helps

User
23/08/2017 - 10:51

IF(LEFT(A2,3)=B2,C2*D2,IF(RIGHT(A2,3)=B2,C2/D2,

Excelchat Expert
23/08/2017 - 10:52

=iferror(if(find("USD",A2,1)>0,if(mid(A2,1,3)=B2,C2*D2,C2/D2),""),C2/F2)

Excelchat Expert
23/08/2017 - 10:52

It is for row 2

Excelchat Expert
23/08/2017 - 10:53

And can be copied down

Excelchat Expert
23/08/2017 - 10:53

Discussion phase:

Excelchat Expert
23/08/2017 - 10:53

The problem is solved :)

User
23/08/2017 - 10:53

Can I have one same formula for all rows

Excelchat Expert
23/08/2017 - 10:54

Yes. It is only one formula for all rows

User
23/08/2017 - 10:54

oh good

Excelchat Expert
23/08/2017 - 10:54

You can just copy and paste downwards

User
23/08/2017 - 10:55

Wow thanks so much

Excelchat Expert
23/08/2017 - 10:55

Have a great day ahead:). Please visit Got it Pro for new queries

User
23/08/2017 - 10:56

Can i have a link to this file?

Excelchat Expert
23/08/2017 - 10:56

You can copy from this and paste into your excel file

Excelchat Expert
23/08/2017 - 10:56

or just copy the formula

Excelchat Expert
23/08/2017 - 10:56

remaining sheet is same

User
23/08/2017 - 10:56

thanks!!

Excelchat Expert
23/08/2017 - 10:56

https://docs.google.com/spreadsheets/d/1-kaFpluSrpWzQpTrv6K3_6vHYhTAg0FXf3JB7CL0gKE/edit?authuser=1

Excelchat Expert
23/08/2017 - 10:57

Here is the link.

Excelchat Expert
23/08/2017 - 10:57

Please end the session by ending the timer. Have a great day ahead :_

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*