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]

Thanks! I don't quite understand the formula but I am going to try this all out and I'll keep learning about formulas so I can make them!

 

[Robby19]

Thanks! I don't quite understand the formula but I am going to try this all out and I'll keep learning about formulas so I can make them!

I would recommend you convert your data into tables, but that is just my preference when working with Excel. I think it makes the formulas easier to read and see exactly where they are referring back to.

The INDEX(Match)) formula is doing 2 things. First it is matching 2 different criteria from 2 separate locations, in this example Table1 Country column and Table2 Country column. The 0 in the Match formula means it is looking for an exact match. If it finds a match, then the index portion of the formula kicks in. This will display the data from the column you selected and exact row that the Match formula found.

The IFERROR portion is just to remove any errors that may occur within the INDEX(MATCH)) formula. If an error is found, then instead of displaying the error the formula will show something else, in this case it will show the Country of the Customer.