Formula for displaying $ value and an existing cell value

[Ian_M]

Hi all.

Sure this is super simple and I’m no doubt being super simple myself in not getting the solution…

I have a sheet where I am keeping track on recurring charges. I’m dealing with 3 specific columns in this sheet

Col 1 [$ Charge]
Col 2 [currency selection…eg AUD, USD, GBP etc)
Col 3 [$AUD Charge]

Ultimately for each line item I add, I want all charges to be converted to AUD (this would be COL3) and from there I can run further calculation based on this number.

So I’m assuming the formulas for column 3 would be:

If “Currency” is “AUD” then display the value already entered in “$ Charge”.

And…

If “Currency” is “USD” then multiply by “<FX rate that I’ll input>” then display the calculation as a “$ value”

I’m just not sure how I would write those formulas in Airtable?

Possible?
Thanks so much
Ian

[Mohamed_Swella1]

strong textHi @Ian_M,

Welcome to Airtable Community! :grinning_face_with_big_eyes:

You will need a 4th field which is the exchange rate. The formula would then be

IF("Currency"="AUD",{$ Charge}, {$ Charge}*{FX Rate})

Hope this helps

BR,
Mo

[Ian_M]

Hey Mo! Thanks for your quick reply.

I dropped this in with the new field. It seems to work for the second condition but not quite for the the first.

So with the adjusted fields:

Field 1 ($ Charge) = $1.50
Field 2 [Currency) = AUD
Field 3 [FX Rate] = blank
Field 4 [$AUD Charge] = should therefore be the value of Field 1 - $1.50. But with your formula it seems to be showing as 0

Any thoughts?

Cheers
Ian

[Ian_M]

Hey Mo - realised if I just drop 1 into the FX Rate field, then it’ll solve that problem.

Which actually means I might as well just simplify this and not use IF formula. If I’m going to have the FX Rate field, then I just do a straight multiple Field 1 x Field 3 to get my answer.

Cheers
Ian

[Mohamed_Swella1 · Accepted solution]

Hi @Ian_M,

Ya I did a mistake in the formula, sorry. The Currency in the first argument should be referring to a field.

IF({Currency}="AUD",{$ Charge}, {$ Charge}*{FX Rate})

Now it should work.