Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'

 

 

Variable moving average formula in Excel

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

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.

Variable moving average formula in Excel

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.

Variable moving average formula in Excel

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.

Offset Excel 2013

Convert Columns of Budgets and Actuals into database

Get the average from the previous week (or other dates)

Weighted average versus average in Excel