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.
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 -point moving mean ( odd) replaces each value by the mean of the values centred on it. A -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 and last periods have no smoothed value, so a -point moving average of values gives smoothed values.
Worked example
Smooth water-use data with a 3-point moving mean
Mean daily water use per person (litres) in a south-east Queensland town is shown. Data are illustrative.
| Year | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 |
|---|---|---|---|---|---|---|---|---|---|---|
| Water use (L) | 182 | 176 | 179 | 171 | 168 | 170 | 163 | 160 | 162 | 155 |
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.
Step 1: Find the first window
A 3-point window has one value either side of the middle, so 2016's window is 2015, 2016 and 2017. There is no moving mean for 2015, because it has no value before it.
Step 2: Calculate the 2016 moving mean
Step 3: Move the window on one year
2017: . 2018: .
Step 4: Write the spreadsheet formula
2016 is in row 3, so in cell C3 enter
=AVERAGE(B2:B4), then fill down to C10 (2023). C2 and C11 stay empty.Filling down moves the window one row at a time: C4 becomes
=AVERAGE(B3:B5), and so on.
Try it yourself first, then check the working one step at a time.
Answer
a) 2016: 179.00 L, 2017: 175.33 L, 2018: 172.67 L. b) =AVERAGE(B2:B4) in cell C3, filled down to C10.
Method
Centre a window of values on the period.
Average them and place the result at the middle period.
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.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Year | Attendance ('000) | 3-point moving mean | 3-point moving median |
| 2 | 2014 | 43.5 | ||
| 3 | 2015 | 42.8 | =AVERAGE(B2:B4) | =MEDIAN(B2:B4) |
| 4 | 2016 | 45.9 | =AVERAGE(B3:B5) | =MEDIAN(B3:B5) |
| 5 | 2017 | 47.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?
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
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.
Step 1: Moving means
2019:
2020:
2021:
Step 2: Moving medians
2019: middle of 18.2, 46.4, 49.3 is 46.4
2020: middle of 18.2, 47.6, 49.3 is 47.6
2021: middle of 18.2, 47.6, 50.8 is 47.6
Sort each window first; the median is the middle value.
Step 3: Compare
The outlier is in all three windows, and it drags every moving mean down by about 10 thousand, making a three-year dip that never happened. The moving medians stay close to the values either side of 2020 (around 46 to 51 thousand).
Try it yourself first, then check the working one step at a time.
Answer
Moving means: 37.97, 38.37, 38.87. Moving medians: 46.4, 47.6, 47.6. The moving median better shows the trend: the median is resistant to the 2020 outlier, while the mean is pulled down for three years.
Method
Calculate both smoothers for every window that contains the outlier.
Compare them with the values on either side of the outlier.
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.
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 -point moving mean (or median) uses consecutive values, odd, and is placed at the middle one.
There are no smoothed values for the first and last periods: 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.