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?
Nested If statement on Text
+4Hi 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
+18Julian_Kirkness wrote:
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…
+18Julian_Kirkness wrote:
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…
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.