Create a variable moving average formula in MS Excel
на канале: AuditExcel Advanced Excel and Financial Model Training and Consulting
00:00 Average versus a 'variable moving average'
00:13 Using OFFSET to allow for variable number of cells included in AVERAGE
01:05 Wrap an AVERAGE around the OFFSET to make it work
01:42 Change the number of cells the AVERAGE looks at
From https://www.auditexcel.co.za/blog/var...
Images on website
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.
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.
Смотрите видео Create a variable moving average formula in MS Excel онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь AuditExcel Advanced Excel and Financial Model Training and Consulting 05 Март 2020, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 3,512 раз и оно понравилось 22 людям.