Formula for multiplying a currency exchange rate?

Rob_Krause · ‎Apr 22, 2017

Hey AT community!

Been working with Airtable for only a little while now and having trouble coming up with a formula that we will use in many of our tables.

I would simply like to input an amount in a USD$ field and have it automatically appear in the €EUR field by multiplying it against an exchange rate (ie. 0.93) Often, we receive both currencies in sales or donations.

In other words, if the Euro field is empty and the sale was in USD, we could enter the USD and exchange rate and have it calculate and populate the Euro column.

Here’s a screenshot of the columns and what I’m trying to figure out. How would you suggest I do this? Thanks in advance!

Tim_Wilson · ‎Apr 27, 2017

The problem, as you’ve no doubt found, is that you can’t have it both ways when it comes to a column. Either you can enter data or the contents are derived from a formula. In your case I would suggest a third column, say “Importo Combined” that looks at the contents of “Importo in € Euro” and “Importo in $ USD” uses an “if” to decide what to do. The logic would go like this:

If “Importo in € Euro” is not empty, just place that value in "Importo Combined"
If “Importo in € Euro” is empty and “Importo in $ USD” is not empty, multiply the USD cell by the exchange rate and put that value in “Importo Combined”

A single formula would do it. You might not even need that second “if”. All your totals would be based on “Importo Combined”.

I hope that helps.

Rob_Krause · ‎Apr 28, 2017

Hi Tim,

Thanks so much for replying to my question and yes, that’s brilliant. You’re correct, I did try to “have it both ways” to make it clean, smooth, and magical. :grinning_face_with_sweat:

So, I worked on it for a number of hours yesterday to improve my learning curve and to keep trying to find a solution. So, with your help, here is what I came up with in the picture below which I think follows your logic and does “do the trick”… I used a condition based on the type of currency and an IF phrase to “sum/multiply” the values and bring them over to the Total in Euro column.

Would you suggest any ways or syntax to make it cleaner or any more efficient?
(btw, as you can see, I’m helping an Italian association, so the word “valuta” simply means currency and “importo” means amount)

Really grateful again to you for responding and the active support community here!

Tim_Wilson · ‎Apr 28, 2017

It’s looks like you’ve got it working, Rob. Congrats.

I believe you could condense the two “Importo” columns into one since you’re specifying the currency in the “Valuta” column. I would make a single “Importo” column and base your logic off the “Valuta” column (as you already have). You wouldn’t need to have the currency abbreviations in the “Importo” column if you’ve got it in the “Valuta” column.

I hope that makes sense.

Rob_Krause · ‎Apr 28, 2017

Hey Tim,

Yes, it does make sense – in your post here, but not in my formula. That’s where I was stuck and need your help.

How would I write that (what would the formula syntax be for a combined “importo”)?

Thank you again for any help you can offer.
R

11124 · ‎Jan 29, 2018

I use this currencyrate.today more 173+ currencies

Rob_Krause · ‎Jan 29, 2018

Thank you for the link.
How do you connect that site with Airtable to insert the currency automatically?

11124 · ‎Jan 29, 2018

Exchange Rates Embeddable Widget for website

Embed the exchange rates widget in your website or blog with adaptive size and numerous options for integration in your design. Our widget have more 190+ currencies and popular cryptocurrencies with support 11 languages.

I use two widgets

W_Vann_Hall · ‎Jan 30, 2018

I realize you have undoubtedly found a solution by this point, but for others who may dig up this post while searching Support, I have a similar situation in the [dimension] table of my Wardrobe Manager base. Rather than Euros and dollars, though, I needed to support both inches and centimeters for a multinational audience.

My solution was to use a text field for data input and permit the user to ‘hint’ which unit of measurement to use. In my case, a numeral followed by '"', 'i', or 'in' is interpreted as a dimension in inches, and one followed by 'c' or 'cm' as a dimension in centimeters. (Unhinted values are interpreted either as inches or centimeters depending on the default value of another field — but that’s a complexity you probably don’t need.)

In your base, then, I could envision you using a single-line text field for data entry, where unhinted numerals, ones preceded with '€', and ones followed by 'e' would all be interpreted as a value in Euros and be displayed in your {Importo in € Euro} and {Totale in Euro} fields. Numerals preceded by '$' or followed by 'd' would be interpreted as a dollar amount, displayed in {Importo in $ USD} and multiplied by {Exchange Rate}, with the result displayed in {Totale in Euro}. This would allow you to dispense with the {Valuta} single-select and use a single column for data entry.

Filipa_Didier · ‎Sep 02, 2018

but how do you integrate these widgets with airtable?