33. Excel Tips - Moving Averages

Nick's picture


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:
moving average

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:

change to row format

How do I get this formula to work across a row instead of upwards =AVERAGE(OFFSET(C4,COUNT(C:C)-F3,0,F3))

gweilow's picture

33. Tips Moving Average

When I use count(c:c) I get 12 not 13 so 12-3=9. This means that the starting point for the offset array is 9 rows below c4. Is this correct?