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:
=AVERAGE(OFFSET(C4,COUNT(C:C)-F3,0,F3))

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:
reference,rows,cols,height,width

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.

Download Example Sheet to play with moving averages

Training Video on Moving Averages: