Help

Re: Formula for multiplying a currency exchange rate?

3924 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Rob_Krause
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

Screen Shot 2017-04-22 at 8.09.58 AM.png

17 Replies 17
Tim_Wilson
7 - App Architect
7 - App Architect

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

Screen Shot 2017-04-28 at 10.53.41 AM.png

Tim_Wilson
7 - App Architect
7 - App Architect

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
4 - Data Explorer
4 - Data Explorer

I use this currencyrate.today more 173+ currencies

Rob_Krause
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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

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.

but how do you integrate these widgets with airtable?

Hi Filipa Didier

There is a MESSAGE option, maybe you could PM him. I just did a quick check and it seems this person has not posted anything since January, I could be wrong though, but I thought I would let you know.

MK

Has anyone figured this one out? I’m used to coming from Google Sheets where I can pull in an array formula which can get GoogleFinance to autocalculate currencies, and I’m hoping to move everything over to Airtable though.

Prettysim.pl

exactly same here, no good solution found.

Sean_io
5 - Automation Enthusiast
5 - Automation Enthusiast

Airtable… please add a CURRENCY(‘USDGBP’) type function

:pleading_face:

Yuval_Klein1
4 - Data Explorer
4 - Data Explorer

Does anyone know how to use this script?

Joseph_Tandle
4 - Data Explorer
4 - Data Explorer

I also need to convert currencies in another column. Anyone figure this out?

GavinA
8 - Airtable Astronomer
8 - Airtable Astronomer

@Joseph_Tandle Maybe our app will help with this? Its for bulk converting one currency to another. We also allow the use of a date column to ensure the exchange is historically accurate. Currency Converter - Apps - Airtable Marketplace
Any features missing that wll solve your problems - we’d love to know :slightly_smiling_face:

The Data Fetcher extension will let you convert currency in bulk easily in Airtable!