StatPakMoving average in Google Sheets
A moving average smooths a series by averaging the latest few values. It is a simple way to see a trend through noise and to make a one-step-ahead forecast. Example: 18 months of product demand.
Run the Moving Average tool
- Open Extensions, StatPak, Data Analysis and choose Moving Average.
- Input range: the Demand column with its header,
B1:B19. Tick Labels in first row. - Interval: 3, the number of periods in each average.
- Tick Standard errors.
- Under Output choose Output range and pick
C2, so each result lines up with its month. Click Run.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Demand | Moving average | Standard error |
| 2 | Jan 2025 | 120 | #N/A | #N/A |
| 3 | Feb 2025 | 132 | #N/A | #N/A |
| 4 | Mar 2025 | 128 | 126.6667 | #N/A |
| 5 | Apr 2025 | 141 | 133.6667 | #N/A |
| 6 | May 2025 | 150 | 139.6667 | 7.356025 |
| 7 | Jun 2025 | 138 | 143 | 7.864595 |
| 8 | Jul 2025 | 155 | 147.6667 | 7.864595 |
| 9 | Aug 2025 | 162 | 151.6667 | 7.864595 |
| 10 | Sep 2025 | 149 | 155.3333 | 8.178563 |
| 11 | Oct 2025 | 170 | 160.3333 | 8.950481 |
| 12 | Nov 2025 | 176 | 165 | 9.211506 |
| 13 | Dec 2025 | 168 | 171.3333 | 8.670939 |
| 14 | Jan 2026 | 181 | 175 | 7.485789 |
| 15 | Feb 2026 | 190 | 179.6667 | 7.162143 |
| 16 | Mar 2026 | 184 | 185 | 6.922855 |
| 17 | Apr 2026 | 197 | 190.3333 | 7.123254 |
| 18 | May 2026 | 205 | 195.3333 | 6.804138 |
| 19 | Jun 2026 | 199 | 200.3333 | 6.823163 |
How to read the output
- The first two rows show #N/A because a 3-period average needs three values. The March 2025 value, 126.6667, is the average of January, February and March.
- Like Excel, the tool writes each average on the row of the last period it includes. To use it as a forecast, read it as the forecast for the next period: the June 2026 value, 200.3333, is the forecast for July 2026.
- Standard error is the square root of the average squared gap between actual demand and the moving average over the latest 3 periods. It needs earlier averages to compare against, so it starts in row 6. Lower values mean the average tracks the data closely. The last standard error is 6.823163.
Choosing the interval
A longer interval smooths more but reacts more slowly. With an interval of 6 the June 2026 average is 192.6667 and the last standard error is 13.38981, against 200.3333 and 6.823163 with an interval of 3. Because this demand series has a steady upward trend, the longer average lags further behind. For monthly data with a yearly pattern, an interval of 12 removes the seasonal swings.
Excel's Moving Average dialog also has a Chart Output option. StatPak writes the numbers only; to chart them, select the Month, Demand and Moving average columns and choose Insert, Chart, Line chart.
With a formula
In C4 enter =AVERAGE(B2:B4) and fill down. The standard error in D6 is =SQRT(SUMXMY2(B4:B6, C4:C6) / 3), also filled down. These give the same values as the tool. If the series trends strongly, consider exponential smoothing or a regression on time, which follow a trend with less lag.