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!

 

[Robby19]

Create a separate table with the conversion rates the you most commonly use. For example;

Table - Conversion
[TABLE="width: 500"]
<tbody>[TR]
[TD]Country
[/TD]
[TD]Conversion Rate
[/TD]
[/TR]
[TR]
[TD]Canada
[/TD]
[TD]0.98
[/TD]
[/TR]
[TR]
[TD]Australia
[/TD]
[TD]0.78
[/TD]
[/TR]
</tbody>[/TABLE]
etc...

This way you can always adjust the rates as required.

In your original table add a hidden helper column that pulls the Country or unique identifier you use for that country (Lets say for example in Column B) as well as an Index(Match)) formula to pull the Conversion Rate from your new Table - Conversion (Lets say for example in Column C). Then instead of doing =D1 (Price)/0.98, you could do =D1/C1

 

[Robby19]

Keep in mind though, with this solution, if you ever change the conversion rate it will change all of the conversion rates for previous orders as well. Not sure how you track your orders, but this might not be the most probable solution.

Although, you could add in an override to the formula. I'm just spit balling now, but if I saw the data I could work out a solution for you.

 

[Nmahler]

I don't know if changing the conversion rates in the table would be an issue because all of the orders from each month are in different workbooks. Also, what is a helper column? I will get some of the data for you tomorrow so we're at least both on thinking on the right lines Your suggestions are helpful I am beginning my Excel journey and am learning new things every day so I really do appreciate everything!

 

[Robby19]

Also, what is a helper column?

A helper column is a hidden column with formulas and/or data that can work in the background and pulls unique values from other data/tables to help make all of the front-end formulas work properly. If that makes sense.

 

[Nmahler]

It does make sense. So a helper column is just a hidden column that "helps" a visible column, is that correct? Are hidden columns seen by Macros? What about when you copy and paste data are the hidden columns included?

 

[Robby19]

You are correct.

For all intents and purposes the hidden columns are visible to Excel and can be visible to the user as well through the Hide/Unhide function. So yes, they are visible to Macros. A hidden column will not print, unless it is Unhidden.

As for copy/pasting, it would depend on where the hidden column was and where you are copy pasting. If the hidden column is in Column B, and you copy/paste data from A-C then the data in Column B will be overridden. I normally place my hidden columns in the very front of the table so that the Users can easily add on to the table from the back without messing anything up. Normally when data is added, it is added to the back. But that all depends on how you set up your database. But it does not necessarily matter where the hidden column is, technically it doesn't even have to be part of the table just on the same row, so long as you don't override the formulas.

 

[Nmahler]

Hey! Sorry I am late, my fiancee and I had Bridal photos yesterday and we got our marriage license. Below is some example data (it has been changed so no privacy violations). [TABLE="width: 448"]
<tbody>[TR]
[TD="width: 64, align: right"]25287[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="width: 64"]USA[/TD]
[TD="width: 64"]MNY(initials)
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Nmahler]

I'm not sure why those two boxes popped up (I didn't put them in there) but there are hundreds of rows of data like the ones I posted above.

 

[Robby19]

[TABLE="width: 448"]
<tbody>[TR]
[TD="width: 64, align: right"]25287
[/TD]
[TD="width: 64"]USA
[/TD]
[TD="width: 64"]MNY(initials)
[/TD]
[/TR]
</tbody>[/TABLE]

Congrats on the marriage!

So If I understand this correctly, 25287 is the Price? And USA is the current currency?