Skip to main content

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?

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

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

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


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…

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


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

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

Terms & ConditionsAccessibility statement