Currency converter

Milko

Hi, I have project expenses in different currencies (EUR, GBP, CHF, AUD) and need to convert them all in USD. Is there a solution how I could enter fix exchange rates and after entering e.g. EUR 100.- to get it automatically in new cell / row converted in USD 112.-?

 I have added the example

Shaine Greenwood

Hello Milko—Smartsheet doesn't have active currency conversion functions built into the application. To accomplish what you need, you may need to build a custom formula that converts the currency based on your original amount, such as:

 

=[ORIGINAL AMOUNT]1 * 1.11 (EUR to USD)

 

You might also consider adding another column that has the current exchange rate on a 1:1 scale, so you can use a formula like:

 

=[ORIGINAL AMOUNT]1 * [CURRENT EXCHANGE]1

 

If you type in 1.11 in a newly created CURRENT EXCHANGE column, it'll be easier than having to go in and manually edit the formulas any time the exchange rate for that currency changes.

Milko

Hello Shane, many thanks for your reply. So in case I would have EUR, GBP, CHF and AUD currencies, I would need to add 4 columns.

Shouldn´t there be additionaly a formula that proofs the original currency to pick up the right exchange rate?

Cheers, Milko

Jeff K.

Another way may be to have a 2 columns

* The 1st column (CurrencyDrop) explicitly states the currency used (USD, EUR, YUAN, JPN) etc.

• * The 2nd column can be the currency ratio to USD with a large IF statement.

 

An example of that second column will be =(IF[CurrencyDrop]="EUR", EuroToUsRatio, IF(CurrencyDrop]="JPN', JpnToUsRatio, IF(....)))

 

Shaine Greenwood

You could just have one additional column, and you could put the current exchange rate for your currencies in cells down that one column. You'll want to place the like one in the example I gave down the cells of your AMOUNT USD column.

 

The formula will auto-fill by itself down the column if you add data to new rows.

J. Craig Williams

Here's a solution that requires no additional columns, only additional rows using the LOOKUP function.

 

1. Create set of rows to hold the current rates

- use the [Currency] column for the name (like in the data rows) and [Original Amount] column for the conversion rate.

 

2. In the [Amount USD] column for the data, add this formula:

 

=(LOOKUP(Currency62, Currency$57:[Original Amount]$60, 2),false) * [Original Amount]62

 

for row 62 and pointing to my rows 57-60 for the data.

I would put that at the top so 2-5 or 1-4 is more likely.

But using the $ absolute reference.

 

This looks to the left to find which currency to use and then returns that value multiplied by the original amount.

 

A major drawback to this solution however, is that any time the currency rate changes, it will change previous results.

The additional column solution is best because each row will have a SPECIFIC AND UNCHANGING conversion rate, if that is what you need.

 

If I have a project in GBP and need to convert that to USD, I'd want to know what my actuals are for each invoice and then a projected remaining (based on today's conversion or rolling average)  

 

The IF statement provided by Jeff has the same drawback as the LOOKUP plus the added burden of changing the IF statement whenever you update the conversion rates.

 

Hope this helps.

 

Craig