33. Excel Tips - Moving Averages
This example shows you how to calculate moving averages.
They can be useful when you have a share price for example that changes every day, and you want to calculate the average of the last X days.
Here's the data we'll work with:
In cell F3, we enter the number of days we want to calculate the average for.
Cell F4 contains the formula:
Let's break it down to see what it's doing.
First of all, the OFFSET function returns a range.
- How do we make sure this range is the right one ?
- We want this range to be the last 3 populated cells in the table.
OFFSET takes the following arguments:
So, we tell the OFFSET function to create a new range with the starting cell being 10 cells below C4 (the first share price), and continuing for 3 cells down.
How does it know to start 10 cells down ?
- we enter COUNT(C:C)-F3 as the reference
COUNT(C:C) returns the amount of populated cells in the column C.. in this case 13. Subtract 3 cos we want the last 3.
... then we wrap this with the AVERAGE function.
Training Video on Moving Averages: