Home / Statistical Tools / Analysis Tools / Time Series / How-To / Moving Average
Moving Average¶
Smooth a series by averaging each point with the ones before it. This walkthrough uses the same twelve-month demand series as the Quick Start, so the two results can be compared directly.
Goal¶
Smooth twelve months of demand with a three-period window, and read the two different smoothed columns the output gives you.
Sample Data¶
| Demand |
|---|
| 129.0 |
| 136.1 |
| 132.5 |
| 132.6 |
| 136.4 |
| 150.2 |
| 148.8 |
| 151.9 |
| 169.4 |
| 174.9 |
| 183.8 |
| 179.6 |
Steps¶
-
Put the data in Excel
Press Copy for Excel, click cell A1 in a blank worksheet, and press Ctrl+V.
-
Launch the analysis
From the Excel ribbon, select QXL Stat Tools → Analysis Tools → Time Series → Moving Average.
-
Select your data
Select cells A1:A13.
-
Configure the analysis
Tick Demand under Data Columns, then open the Options tab. The Moving Average Options group holds one setting, Running Length:, which starts at 2. Set it to 3 and click Finish.
The box is clamped against the data: it will not accept less than 2, and it will not accept more rows than you selected.
Result¶
A worksheet named MovAvg Demand, with a chart and a table whose columns are Index, Actual, MA, Predicted and Error.
| Index | Actual | MA | Predicted | Error |
|---|---|---|---|---|
| 1 | 129.0 | |||
| 2 | 136.1 | |||
| 3 | 132.5 | 132.5333 | ||
| 4 | 132.6 | 133.7333 | 132.5333 | 0.0667 |
| 5 | 136.4 | 133.8333 | 133.7333 | 2.6667 |
| 6 | 150.2 | 139.7333 | 133.8333 | 16.3667 |
| 7 | 148.8 | 145.1333 | 139.7333 | 9.0667 |
| 8 | 151.9 | 150.3000 | 145.1333 | 6.7667 |
| 9 | 169.4 | 156.7000 | 150.3000 | 19.1000 |
| 10 | 174.9 | 165.4000 | 156.7000 | 18.2000 |
| 11 | 183.8 | 176.0333 | 165.4000 | 18.4000 |
| 12 | 179.6 | 179.4333 | 176.0333 | 3.5667 |
| Metric | Value |
|---|---|
| MAPE | 6.3475 |
| MAD | 10.4667 |
| MSD | 161.1452 |
There is no Equation table on this analysis and no forecast rows. Moving Average produces no forecast at all, whatever else the dialog offers.
Read the Two Smoothed Columns Carefully¶
MA and Predicted are not the same column, and the Error column uses the second one.
- MA is the average of the window ending at that row. At row 3 that is (129.0 + 136.1 + 132.5) / 3 = 132.5333.
- Predicted is the previous row's MA, shifted down one. At row 4 it is 132.5333, the value MA reported at row 3.
- Error is Actual minus Predicted, never Actual minus the MA printed beside it. At row 4 that is 132.6 − 132.5333 = 0.0667, not 132.6 − 133.7333.
The reason is what Predicted means: the one-step-ahead forecast you could actually have made, using only rows up to the previous period. MAPE, MAD and MSD are all built from that column, over the nine rows where it exists.
The first rows are blank on purpose. MA needs a full window, so rows 1 and 2 have none. Predicted is one further behind, so it starts at row 4.
The Trailing Window Lags a Trend¶
Compare the accuracy measures against the Quick Start, which fitted a straight line to these same twelve numbers:
| Trend Analysis, Linear | Moving Average, length 3 | |
|---|---|---|
| MAPE | 3.4591 | 6.3475 |
| MAD | 5.1333 | 10.4667 |
| MSD | 33.3018 | 161.1452 |
Every error in the Moving Average table from row 5 onward is positive: 2.6667, 16.3667, 9.0667, 6.7667, 19.1000, 18.2000, 18.4000, 3.5667. A trailing average of a rising series is always looking backwards at smaller numbers, so it sits below the data and misses in the same direction every time. That is arithmetic, not a fault in the data.
Things to try next¶
- Set Running Length to 2, then to 6, and compare MAD. A shorter window tracks the rise more closely; a longer one smooths harder and falls further behind.
- Blank out row 6 and re-run. Every window containing that row goes blank, so one missing reading removes three MA values, not one.
- Set Running Length to 13 with twelve rows selected. The run is refused, and the message names the numbers: Moving Average skipped for "Demand": Running Length 13 exceeds available rows (12).