As part of our project management system, I need to be able to calculate an amount using foreign exhange rates on a particular date.
I’ve made a google sheet that pulls in the exchange rate data once a day, and I use zapier to send it over to a coda table. That gives me the rates needed. Win. (Its not perfect - you can see in the graph that there’s been interruptions to the data… USD for instance doesn’t give the data on the weekends, and something strange happened wednesday… but I can fix that!)
Here’s the table I’m using (its setup is important to my question…)
My question is what formula can I use to find an exchange rate that is specified in a row of a table.
Ie : my table has the date needed, and the exchange rate code (say, USD) as a lookup. However, I cannot for the life of me work out how to do it - without perhaps a massive nested if statement.
I’ve created a test document to show the basic table setup - and a column where I’d like the calculation.
I’m wondering if perhaps my overall data schema is not great for this?!
Thanks for any help!