I want to be able to calculate average values of 3 years prior and show them as the value for this year.
So, if I am forecasting for May 2023, it would be the average of values of May 2020, May 2021 and May 2022.
Please suggest how to achieve this?
You can use this formula
(OFFSET(Test Values, -12, 0) + OFFSET(Test Values, -24, 0) + OFFSET(Test Values, -36, 0)) / 3
This will calculate your past 3 year average and show them as value of current year
Hope, this resolves your issue1