Lesson 3 of 7 · 18 min

Smoothing with moving means and medians

Smooth a time series with a simple moving mean or median over an odd number of values, by hand and with a spreadsheet, and choose a sensible window.

What you will be able to do

  • Calculate a 3-, 5- or 7-point moving mean and place it at the middle period of its window.
  • Calculate a moving median and explain why it resists outliers.
  • Write spreadsheet formulas such as =AVERAGE(B2:B4) and =MEDIAN(B2:B6) to smooth a whole series.
  • Choose a window that smooths out the irregular fluctuations (and, for an odd-length cycle, the seasonality).

A café in Toowoomba counts its customers every day. Every Saturday is busy and every Monday is quiet, and the counts jump around from day to day. Is the café actually getting busier?

Averaging takes the noise out. Replace each day with the mean of a whole week centred on it — three days before, the day itself, three days after — and the weekly pattern and the day-to-day bumps cancel, leaving the trend.

Interactive

Smoothing daily customer numbers

Customers each day for three weeks (data are illustrative). The 7-point moving mean is almost a straight line: the weekly pattern is gone. Switch to a 3-point window and the weekend peaks come back. Try the moving median too.

Definition

Simple moving average

A kk-point moving mean (kk odd) replaces each value by the mean of the kk values centred on it. A kk-point moving median uses their median instead.

  • Each smoothed value is placed at the middle period of its window.
  • The window then moves on one period and the calculation is repeated.
  • The first k−12\frac{k-1}{2} and last k−12\frac{k-1}{2} periods have no smoothed value, so a kk-point moving average of nn values gives n−k+1n - k + 1 smoothed values.
Here yty_t is the value at period tt.
3-point moving mean at period t=yt−1+yt+yt+13\text{3-point moving mean at period } t = \frac{y_{t-1} + y_t + y_{t+1}}{3}

Worked example

Smooth water-use data with a 3-point moving mean

  • Simple familiar

Mean daily water use per person (litres) in a south-east Queensland town is shown. Data are illustrative.

Year2015201620172018201920202021202220232024
Water use (L)182176179171168170163160162155

a) Calculate the 3-point moving means for 2016, 2017 and 2018, correct to two decimal places.

b) The years are in cells A2 to A11 and the water use in B2 to B11 of a spreadsheet. Write the formula for the 3-point moving mean for 2016, and say where it goes.

4 steps

Try it yourself first, then check the working one step at a time.

Method

  1. Centre a window of kk values on the period.

  2. Average them and place the result at the middle period.

  3. Move the window on one period and repeat. In a spreadsheet, fill the formula down.

Watch out for

  • Putting the 2015–2017 average next to 2015 (or 2017) instead of 2016.

  • Calculating a value for 2015 or 2024: the end periods have no 3-point moving mean.

Holiday-park guest nights (thousands) for 2022 were Q1 38, Q2 21, Q3 29, Q4 32, and Q1 2023 was 41. Calculate the 3-point moving mean for Q3 2022, correct to two decimal places.

in thousand

Round to 2 decimal places.

In a spreadsheet: type the first formula next to the second value, then fill it down. Each row's formula covers the row above, itself and the row below.
RowABCD
1YearAttendance ('000)3-point moving mean3-point moving median
2201443.5
3201542.8=AVERAGE(B2:B4)=MEDIAN(B2:B4)
4201645.9=AVERAGE(B3:B5)=MEDIAN(B3:B5)
5201747.1=AVERAGE(B4:B6)=MEDIAN(B4:B6)
⋮⋮⋮⋮⋮

A time series is in cells B2 to B13 of a spreadsheet. Which formula gives the 5-point moving mean for the value in row 6?

Choose one answer

The moving median is calculated in the same way, but takes the middle value of each window. One wild value can drag a mean a long way; it can't move a median much, because the median only cares about which value is in the middle. That makes the moving median the better smoother when the series has an outlier.

Worked example

Moving mean or moving median when there is an outlier

  • Complex familiar

Show attendance (thousands) for 2018 to 2022 was 46.4, 49.3, 18.2, 47.6 and 50.8. The 2020 value is an outlier.

a) Calculate the 3-point moving mean and the 3-point moving median for 2019, 2020 and 2021 (to two decimal places where needed).

b) Which smoother better shows the trend in attendance? Explain.

3 steps

Try it yourself first, then check the working one step at a time.

Method

  1. Calculate both smoothers for every window that contains the outlier.

  2. Compare them with the values on either side of the outlier.

  3. Choose the median when an outlier is present; the mean otherwise.

Watch out for

  • Forgetting to sort a window before taking its median.

  • Thinking the outlier only affects the 2020 moving average. With a 3-point window it affects 2019, 2020 and 2021.

Mean daily water use (litres per person) for 2015 to 2019 was 182, 176, 179, 171, 168. Calculate the 5-point moving median for 2017.

in L

Key idea

Choosing the window

  • A larger window smooths more, but loses more periods at each end and responds more slowly to real changes.
  • If the data has a seasonal cycle with an odd number of seasons (7 days in a week, 3 trimesters in a year), a window equal to the cycle length removes the seasonality completely: every window contains each season once.
  • Use the median when there are outliers; the mean otherwise.

A time series has 12 values. How many values does a 5-point moving mean produce?

Common mistake

Putting the smoothed value in the wrong place

The mean of periods 1, 2 and 3 belongs to period 2, not period 1 or 3. If your smoothed column starts in the first row, everything is shifted and your smoothed graph will lag behind the data. In a spreadsheet, the first formula goes beside the second value for a 3-point average and beside the third for a 5-point average.

Note

What about even windows?

Quarterly and monthly data have an even cycle length. A 4-point mean sits halfway between two quarters, so it has to be "centred" with a second averaging step. The syllabus only asks for odd windows; for quarterly data you deal with seasonality using seasonal indices instead (next lesson).

Summary

  • A kk-point moving mean (or median) uses kk consecutive values, kk odd, and is placed at the middle one.

  • There are no smoothed values for the first and last k−12\frac{k-1}{2} periods: n−k+1n - k + 1 values in all.

  • In a spreadsheet: =AVERAGE(B2:B4) beside the second value, then fill down. =MEDIAN(...) for a moving median.

  • The moving median resists outliers; the moving mean is pulled towards them.

  • Larger windows smooth more. A window equal to an odd cycle length removes the seasonality.

Finished the lesson?

Try the 4 quick checks above and the lesson completes itself, or mark it complete now.

Lesson 3 of 70% read

Save your progress with a free account