At a glance
Question
Is there a formula that can allow the user to specify how many months to average in a moving average calculation?
Answer
Moving averages are a common reporting requirement. They typically use three-, six- or 12-month time frames. Moving averages reduce the variability of monthly figures and seasonal fluctuations.
Figure 1 shows the structure we will work with. Row 1 contains the months, row 2 contains the number of months. Row 2 will make the final formula easier to create. Row 3 contains sales figures. Row 4 will contain the moving average for the sales figures based on the entry in the yellow cell, A4. (The other months of the year continue to the right of the image.)
If we want a three-month moving average in cell E4 we can enter the following formula and copy it across: =AVERAGE(C3:E3)
This only calculates a three-month moving average. We want the user to change cell A4 and vary the number of months used for the average. To do this, we need a flexible range within the AVERAGE brackets. The OFFSET function can provide that flexibility.
The OFFSET function allows you to specify a starting cell or range and then move (offset) from that cell or range by a number of rows or columns. It can also create a range reference from a starting cell. This range version will be used in the final solution to our problem.
OFFSET syntax
OFFSET(Reference, Rows, Columns, Height, Width)
Reference: The starting cell or range. This is like an anchor, or base, cell or range.
Rows: A whole number that represents how many rows to move from the Reference. A positive number moves down the sheet from the Reference, a negative number moves up the sheet.
Columns: A whole number that represents the number of columns to move from the Reference. A positive number moves to the right, a negative number moves to the left. The next two function arguments are optional and create a range reference.
Height: This is a whole number that represents how many rows the final range will contain. A negative number creates a range that goes up from, and ends with the Reference. A positive number creates a range that goes down from, and starts with the Reference.
Width: This is a whole number that represents how many columns the final range will contain. A negative number creates a range that goes to the left, ending with the Reference. A positive number creates a range that goes to the right, starting with the Reference.
The major difference between the Rows, Columns, Height and Width entries is that Rows and Columns move away from the Reference. The Height and Width values include the Reference in the range that it creates. The examples below demonstrate this difference.
Note: When you use Height and Width to create a range reference, the OFFSET function must be used with another function, like SUM or AVERAGE, which use range references.
Examples of the offset function
In Figure 2 we use the structure from Figure 1 to demonstrate how the OFFSET function works.
=OFFSET(B1,2,3) will return 176, which is the value in cell E3.
Starting in cell B1 we move two rows down (positive row numbers move down the sheet) and three columns to the right (positive column numbers move to the right) to arrive at cell E3.
=OFFSET(F3,-1,-2) will return 2, which is the value from cell D2. Starting in cell F3 we move one row up (negative row numbers move up) and two columns to the left (negative column numbers move to the left) and arrive at cell D2.
Warning: When using negative numbers you need to be careful they don’t refer to a cell above row 1, or to the left of column A. If you do, it will display the #REF! error.
The two examples above show that the Rows and Columns values move away (offset) from the Reference cell. The following example creates a range and the Reference will form part of that range.
=OFFSET(E3,0,0,1,-3) will refer to the range C3:E3. The two zeros in the formula instruct Excel to keep cell E3 as the anchor cell (zero rows and zeros columns). When you use Height and Width the Reference is included in the range. This formula refers to a range that is one row high, and three columns wide. Since negative three was used, the range is three columns wide ending in cell E3. See Figure 3
This particular OFFSET formula will form the basis of our ultimate solution. Since it created a range, this formula must be used with another function, see the solution that follows. The formula in cell E4 that provides a flexible moving average is: =AVERAGE(OFFSET(E3,0,0,1,-$A4))
The -3 has been replaced by the -$A4 cell reference. This means that the number entered in cell A4 will amend the range created by the OFFSET function. The AVERAGE function will then use the OFFSET range for the final calculation. Figure 4 shows the result.
Figure 5 shows a problem that can occur.
Since the user can enter values in cell A4, the formula needs to be able to handle different values. You can use an IF function to compare the value in cell A4 to the value in the same column in row 2. The average calculation will only be done if the value in the same column in row 2 is greater than, or equal to, the value in cell A4 – the formula for cell E4 is below: =IF(E$2>=$A4,AVERAGE(OFFSET(E3,0,0,1,-$A4)),"")
This will display a blank cell in cell E4 if the value in A4 is too large. Two quotation marks "" instructs Excel to display a blank cell. This formula can be copied across to the other months in the report.
Charts and trendlines
It is worth noting that you can automatically add a moving average trendline to a chart series by right clicking the series involved and choosing Add Trend Line. Then choose Moving Average and amend the number of periods to average.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to various organisations. Questions can be sent to [email protected].
