Currency Conversion

[Nmahler]

Do any of you know of an Excel function or add-in that would convert currency with custom rates? I was going to make an add-in that would do the simple conversions for me but before I jump into that I was wondering if there was something already out there that accomplishes this.
In case you need an example let's say I have an order from Australia this month for 100 Australian dollars. This month the rate is 1.28 so 100/1.28= 78.125, so the order cost 78.13 in US dollars. The following month the same order is placed but the conversion rate is now 1.30 so 100/1.30=76.923. So now the same order costs 76.92 (US dollars).

Basically, I need an add-in that I can control the conversion rates and change them when needed. Thanks for all the help!

 

[Nmahler]

25287- is the unique identifier of the of the customer that was upsold.

USA- is the country

MNY- is the initials of the employee that made the upsell

Does that make sense?

 

[Nmahler]

I have to look up each order individually I'm working on a script to do that for me in VBA.
Thaks for the congratulations!

 

[Robby19]

So where do you track the amount of money (currency) the customer spends? And where would you like to convert from?

 

[Nmahler]

We use a system made by Infotrax (this one will likely take more work, this company is growing fast so they don't have a lot of things that should be automated). I run an internal report and then put that data into excel and then edit it from there so I would be doing the converting in Excel.

 

[Robby19]

So let's say, for all intensive purposes, your spreadsheet looks like this.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Customer Number
[/TD]
[TD]Country
[/TD]
[TD]Customer Initials
[/TD]
[TD]Amount Paid
[/TD]
[TD]Currency
[/TD]
[TD]Conversion to USA
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]123
[/TD]
[TD]USA
[/TD]
[TD]ABC
[/TD]
[TD]$1,000
[/TD]
[TD]USA
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]456
[/TD]
[TD]AUS
[/TD]
[TD]DEF
[/TD]
[TD]$2,000
[/TD]
[TD]AUD
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My suggestion is to add 1 Column after E, "Conversion to USA." You would then also need to create another table somewhere in your workbook, either on the same tab or another, your choice.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Currency
[/TD]
[TD]Conversion Rate to USA
[/TD]
[/TR]
[TR]
[TD]CAN
[/TD]
[TD]1.1
[/TD]
[/TR]
[TR]
[TD]AUD
[/TD]
[TD].98
[/TD]
[/TR]
</tbody>[/TABLE]

In column F of the first table use this formula;

=IFERROR(INDEX(Table2[Conversion Rate to USA],MATCH([@Currency],Table2[Currency],0))*[@[Amount Paid]],[@[Amount Paid]])

Table 2 refers to the new Conversion table.

 

[Robby19]

You could also automate the Currency column of Table1 by referring to the country of the customer. Let me know if you would like to do that as well.

 

[Nmahler]

Does it know that Table 2 is another worksheet or how would it know where to look for it?

 

[Nmahler]

I think this way would be better, how would you do it? Specifically, "You could also automate the Currency column of Table1 by referring to the country of the customer. Let me know if you would like to do that as well."

 

[Robby19]

Does it know that Table 2 is another worksheet or how would it know where to look for it?

Using the Ribbon at the top, click INSERT > Table. This would ultimately create Table2, unless you already have multiple tables in your workbook. To see this, click inside the newly created table and at the top under TABLE TOOLS > DESIGN, you should see the Table Name in the Properties section at the top left side.

The way my formula is set up, it will only look in that specific table for a match and if it finds a match it will display the results from Conversion Rate to USA and multiply it by the current amount paid.

 

[Robby19]

I think this way would be better, how would you do it?

Add a Country column to your new Conversion table, such as;
[TABLE="width: 500"]
<tbody>[TR]
[TD]Currency[/TD]
[TD]Conversion Rate to USA[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]CAN[/TD]
[TD]1.1[/TD]
[TD]CAN[/TD]
[/TR]
[TR]
[TD]AUD[/TD]
[TD].98[/TD]
[TD]AUS[/TD]
[/TR]
</tbody>[/TABLE]

Then in column E or the Currency column in Table1, add this formula;

=IFERROR(INDEX(Table2[Currency],MATCH([@Country],Table2[Country],0)),[@Country])

This is looking at Table2 and finding a match between the new Country column and the Country column in Table1. If it finds a match it will list the appropriate Currency name. If not, it will list the Country in Table1. If you would rather have it show an error if it cannot find a match, just remove the "IFERROR" and ",[@Country])" portions