Course / Data Analysis

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.

The ToolPak's Moving Average tool asks for the same thing: an input range and an interval, the size of the window.

Steps

  1. The first 3-week average is possible in week 3. In C4, average weeks 1 to 3, =AVERAGE(B2:B4), then fill it down to C17.

  2. Now a longer window. In D7, average the six weeks up to and including week 6, =AVERAGE(B2:B7), and fill it down to D17.

  3. In E4, compare each week with its 3-week average: =IF(B4>C4, "Above", "Below"). Fill it down to E17.

  4. The simplest forecast is the latest moving average. In H2, forecast next week as the average of the last three weeks, B15:B17.

Remember

In the function reference: AVERAGE, IF

Previous: Descriptive statistics: the summary table · Next: Your first PivotTable