Creating a variable moving average formula in Excel is a common requirement. But sometimes you want a moving average over 3 months and other times 5 months. You could do it manually or use an IF formula, but this is not a great way to do it. A better option is using OFFSET.
Estimated reading time: 3 minutes
Table of contents
YouTube Variable moving average formula in Excel
Watch, like, subscribe
Using OFFSET for variable moving averages
So below we have created a spreadsheet that changes the average calculation in column C when the number changes in C3. When it is a 5, it shows the moving average of the last 5 months (including the current month). So C452 is the average of the house price values in the red square. If you change cell C3 to say 3 months, the average column will change with no further manual intervention.
The important function is the OFFSET function. If you have never heard of it, it is extremely useful (you can learn more about it in the online Advanced Excel course).
You will note below that the OFFSET function refers to a cell (B452 in this case) and then in the Height option it refers to cell C2 but has a minus in front of it. This tells Excel to put it’s cursor on cell B452 and highlight the 5 (like you click and dragging) cells upwards (minus means go up).
If you left it like this it wouldn’t work, as the OFFSET will not know what to do with this range of cells. Around the OFFSET you need to tell it what to do with these cells. Note in the formula bar, we have wrapped an AVERAGE around the OFFSET.
As a result, Excel now knows it must highlight those 5 cells (OFFSET) and get the average of it.
If we change cell C2 to say a 3, the OFFSET will now only highlight 3 cells, thus achieving your objective of a variable moving average calculation.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
Convert Columns of Budgets and Actuals into database