Nested If statement on Text

[George_Bennett]

Hi im trying to get to “Total Spent In USD”
Currently i have “Ammount Spent in Original Currency” and “Currency”
I (think) i want an If statement in an If statement, like in excel.
But this is giving me an error everytime i try it.
Whats the best way to get my desired result?

[Julian_Kirkness]

Hi George

A more ‘databasey’ way of doing this would be to have a currency table with a conversion rate held in it and then use lookup and formula fields to do the calculation.

This will work fine if you want a single currency rate used in your calculation - however if you need to do the calculation for the date the transaction took place based upon the rate held at the time then this is currently a problem with Airtable as the calculation will recalculate if the rate changes.

If anyone has a way round this I would be interested to hear.

Julian

[Christoff]

You are getting an error every time you try it. Please tell us what “it” is.
What formula are you using?
Also, take a look at this: Nested IF formulas

[Tuur]

I would recommend simply storing the calculated value from the function in a ‘real’ column (Zap?).

I’ve done some advanced trickery with circular rollups and a bunch of function fields, but it’s pretty messy to be honest, plus I’m not even sure if that would work in this scenario…

[Tuur]

Or of course make the conversion rate table historical (add a date). But that could potentially mean a lot of records…

[George_Bennett]

Julian_Kirkness:

This will work fine if you want a single currency rate used in your calculation - however if you need to do the calculation for the date the transaction took place based upon the rate held at the time then this is currently a problem with Airtable as the calculation will recalculate if the rate changes.

Thanks Guys, just made a new table in the end.