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.
