I have a table with daily exchange rate data called Daily Exchange Rates.
I have another table, Investment Balance, in which I have to use the data from a column called Foreign Balance, to calculate another column called Local Balance, using the AVERAGE exchange rate of the month in the formula.
How on earth do I do this?
I know the Local Balance column must be of type formula, but I don't know how to write the formula to use the Balance Date and use that to extract an average from the other table, and then do the calculation.