Using IF statements to convert currency (?)

I’ve got an opportunity tracker base that’s logging potential project opportunities from five different countries.

I’m trying to set up a column that takes those individual budgets and converts them all to USD so that I can see our global pipeline in one currency.

To do this, I’m assuming I need to set up a formula that cross references each project budget against the city of origin for that project and only does currency conversion if necessary.

Basically I want to do something along the lines of

If {City of Origin} = “New York” or “Los Angeles”, then just reprint {Budget}
If {City of Origin} = “London”, then just multiply {Budget} * 1.3
If {City of Origin} = “Paris”, then just multiply {Budget} * 1.4
etc etc

But I can’t figure out the syntax.

Would anyone be willing to help?

Also, can anyone recommend best practice for referring to currency conversion multipliers in Airtable that might be simpler than hard-coding the numbers right into the formula?

Thanks so much in advance for any help you can offer! :slight_smile:

Hi there,

I would recommend to add the rates in another field. Then the formula syntax would be:

IF(OR({City of Origin}="New York",{City of Origin}="Los Angeles"),Budget,
  IF({City of Origin}="London", Budget * {Pound rate},
    IF({City of Origin}="Paris", Budget * {Euros rate}, BLANK()
      )
    )
  )

Thanks so much - that did it!

And for the currency, are you suggesting that if I have six different currency conversions, that I should add six different columns and autopopulate each one somehow with that rate?

Hi @markp,

The question regarding the currency multiplier is wherever or not you will want this same number to change the old entries or just the new entries. i.e.: you have an entry that is 2 months old, do you want it to use the new number or stay on the old number?

In all cases, you will need to add a new Field called Currency Multiplier. Depending on you answer above you can either make it an automatic task using a formula or do it manually. You will use this field in the formula above from @Noamsay