Forum Discussion
Pull Yearly CAD/USD Averages Into Simple Table
I'm looking to pull in the monthly average of CAD to USD exchange rates into a nice monthly table without having to update it. I can currently pull in the daily numbers, going back a year using TODAY()-365, but I'd like to now take those columns, average them according to month, and then have them pull into a table on the main sheet. The issue I'm running into is the data will change from day to day, so the formulas will need to take that into account, as well as having Dec-2022 and Dec-2023 in the same data list. I was thinking of using the =IF() formula, but can't think how to make it work. Any help would be great!
- Can you share an Excel file or some screenshots highlighting what you're trying to achieve?
LouisDeconinck Here is a screengrab of what I'm trying to achieve, a running average per month of the CAD to USD exchange rate. I would like the month in the title, as well as the number below to update regularly without us having to google the exchange rate for each month when we need it. We only ever need the last year's data, so as time passes it should update accordingly.
So in my perfect situation, the far right would be today's month and average rate, then working backwards to the left per month.
- Turns out ChatGPT solved my problem! After a day of asking the wrong questions, I finally asked the right one and it spat out the formulas.
It depends on what is the data source and what is desired output. If take exchange rate with STOCKHISTORY and take average only for the trading dates in the month
it could be
=AVERAGE(STOCKHISTORY($B3, EOMONTH(C$2,-1)+1, EOMONTH(C$2, 0), 0, 0,1))
