Moving averages: smoothing out the noise
Smooth weekly visitor numbers with 3- and 6-week moving averages, spot the weeks above trend, and make a simple forecast. Intermediate · 5 min · Moving average, AVERAGE, Relative references, IF
Week-to-week numbers are noisy. A moving average smooths them by averaging each week with the ones just before it. As the window moves down the column, the average moves with it.
There's no special function: it's AVERAGE over a small range, filled down. Because the range is relative (no $), it slides one row with every copy.
- A short window (3 weeks) follows changes quickly but stays bumpy.
- A long window (6 weeks) is smoother but slower to react.
The ToolPak's Moving Average tool asks for the same thing: an input range and an interval, the size of the window.
Steps
The first 3-week average is possible in week 3. In
C4, average weeks 1 to 3,=AVERAGE(B2:B4), then fill it down toC17.Now a longer window. In
D7, average the six weeks up to and including week 6,=AVERAGE(B2:B7), and fill it down toD17.In
E4, compare each week with its 3-week average:=IF(B4>C4, "Above", "Below"). Fill it down toE17.The simplest forecast is the latest moving average. In
H2, forecast next week as the average of the last three weeks,B15:B17.
Remember
- A moving average is
AVERAGEover a window with relative references, filled down. - Short windows react fast but stay bumpy. Long windows are smooth but lag.
- Compare each value with its moving average to see what is above or below the recent norm.
- The latest moving average is a simple forecast for steady data. For trending data, use TREND or FORECAST.
In the function reference: AVERAGE, IF
Previous: Descriptive statistics: the summary table · Next: Your first PivotTable