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.


Reply