LinkedIn
Twitter
Heello team…
Wow, things now have become crazy on my finance tracker app and I have really hit a wall with an expression. It is not even the expression itself but I’m puzzled trying to develop the line of thought that will lead to the expression.
It is an expenses tracker where money is being spent in different currencies and being exchanged a lot (currencies exchange at borders for example).
The main 3 tables now are:
1 - Table “Currencies Exchange”:
Here we list all of exchange operations that happen (attached a random made up list of 4 exchange operations).
2 - Table "Expenses"
Here is a shot of the sheet where all expenses are listed. Note that the fields for exchange rate to the USD is blank. I want that to be calculate from “table 1”
3 - Table "Balance"
This is the sheet that matters to the accountant in charge. Note that despite the fact the money is spent in multiple currencies, the accountant keeps track of the finances in USD. That is why the expressions I need to develop here are the heart of this app.
Considering that “table 1” will usually have more than 1 exchange operations between currency A and B under different rates each time (lets say USD and CAD), I thought of trying Weighted Average. So, my idea was to create a table/slice and calculate tha using SUMPRODUCT IF, like in https://exceljet.net/formula/sumproduct-with-if
That would probably work IF we were always exchanging from USD to another currency, but a lot of times there are no USD involved and we are exchanging for example MXN to GTQ. Then I guess I would need qa formula to split this operation into 2 operations, being from MXN to USD and then from USD to GTQ. BUt there has to be an easier way.
At this point I am so lost that I don’t mind the calculation happening on the app or on Excel, as long as we are able to find that average.
I hope I didn’t “sound” as confusing as my mind is right now. Any inputs are appreciated,
Thank you very much!
