← ACARA Digital Technologies Years 7–8

Smoothing a trend with a moving average

A moving average. It averages the latest 3 values. Copy it down and the range slides by one row each time, smoothing the ups and downs to show the trend.

Part of: ACARA Digital Technologies Years 7–8 — Analysing and Visualising Data — Module 36, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 43

Understanding Smoothing a trend with a moving average in ACARA Digital Technologies Years 7–8

What it does: Averaging three neighbouring values evens out spikes and dips, which reveals the trend. Because B4:B6 has no $ signs, it is a relative reference. Each copy down moves the window by one row. AVERAGE adds the 3 values and divides by 3. SUM would only give the total, not the average. Absolute references stay locked in place, so every copy would average the same 3 cells and nothing would slide. The window is only 3 months wide. It slides down one row at a time, always averaging the latest 3 values. (10 + 14 + 12) / 3 = 36 / 3 = 12. 36 is just the total, not the average.

Common mistakes: A 3-month moving average uses exactly 3 values. B2:B5 covers 4 cells. It should be =AVERAGE(B2:B4). The window must slide down 1 row each time. C5 should average the latest 3 values, B3:B5, not repeat B2:B4.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Smoothing a trend with a moving average to a class →

This module's key terms are also in a printable crossword (Modules 302–324) and word search (Modules 261–324).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
What does a moving average do to a set of data that goes up and down?
It adds every value together into one totalIt smooths the ups and downs to show the trend
Matches
=AVERAGE($B$2:$B$4)
↓
The absolute references lock the range, so copying down gives the same average every time and the window never slides.
Applying
Meanings
Term
=AVERAGE(B2:B4)
Meaning
A moving average. It averages the latest 3 values. Copy it down and the range slides by one row each time, smoothing the ups and downs to show the trend.
Practice
=ROUND(AVERAGE(B6:B8),1)
Testing
Gaps
=(B2:B4)
Check Answer
Spots
1C4: =AVERAGE(B2:B5)
2C5: =AVERAGE(B3:B5)

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Spider' badge — Web Weaver, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. What does a moving average do to a set of data that goes up and down?
A. It smooths the ups and downs to show the trend ✓
B. It adds every value together into one total
A moving average averages a few recent values at a time, so short-term spikes and dips are evened out and the trend is easier to see.
2. Which formula is a 3-month moving average of B2, B3 and B4?
A. =SUM(B2:B4)
B. =AVERAGE(B2:B4) ✓
AVERAGE adds the 3 values and divides by 3. SUM would only give the total, not the average.
3. The formula =AVERAGE(B2:B4) is copied down one row. What does the new formula become?
A. =AVERAGE(B3:B5) ✓
B. =AVERAGE(B2:B4)
The references are relative, so they move down by one row when copied. This is what makes the window slide.
4. Why does a moving average formula need relative references like B2:B4, not absolute ones like $B$2:$B$4?
A. Relative references let the range slide down as you copy the formula ✓
B. Absolute references make the average more accurate
Absolute references stay locked in place, so every copy would average the same 3 cells and nothing would slide.
5. How many values are averaged in a 3-month moving average?
A. All of the values in the column
B. 3 values, the latest month and the 2 before it ✓
The window is only 3 months wide. It slides down one row at a time, always averaging the latest 3 values.

Match the Pairs

=AVERAGE(B2:B4) → Averages three values in a row-range. This is a 3-month moving average for the first window.
=AVERAGE(B3:B5) → The next moving average. The 3-month window has slid down one row.
A moving average smooths ups and downs by averaging the latest 3 months of sales in a sliding window → =AVERAGE(B2:B4)
Relative references let one moving average formula slide down a column when it is copied → =AVERAGE(B4:B6)
=AVERAGE($B$2:$B$4) → The absolute references lock the range, so copying down gives the same average every time and the window never slides.
=AVERAGE(B2:B3) → The range only covers 2 cells, so it is a 2-month average, not the 3-month window that was planned.

Key Term

=AVERAGE(B2:B4)
A moving average. It averages the latest 3 values. Copy it down and the range slides by one row each time, smoothing the ups and downs to show the trend.

Fill in the Blanks

1. =AVERAGE(B2:B4)
Hint: Type the function name that finds the mean of a range.
2. =AVERAGE(B2:B4)
Hint: Type the last cell of a 3-month window that starts at B2.
3. D4: =AVERAGE(B2:B4) Copied down to D5: =AVERAGE(B3:B5)
Hint: Type the first cell of the range after it slides down one row.
4. =AVERAGE(B2:B4) Number of months averaged: 3
Hint: Type how many values are in this moving average window.
5. Refs like B2 are relative and slide down
Hint: Type the word for references that change when a formula is copied down.

Spot the Error

Question 1
A C4: =AVERAGE(B2:B5)
B C5: =AVERAGE(B3:B5)
C C6: =AVERAGE(B4:B6)
Line A — A 3-month moving average uses exactly 3 values. B2:B5 covers 4 cells. It should be =AVERAGE(B2:B4).
Question 2
A C4: =AVERAGE(B2:B4)
B C5: =AVERAGE(B2:B4)
C C6: =AVERAGE(B4:B6)
Line B — The window must slide down 1 row each time. C5 should average the latest 3 values, B3:B5, not repeat B2:B4.
Question 3
A B1: Sales
B C1: 3-month moving average
C C4: =SUM(B2:B4)
Line C — A moving average needs the AVERAGE function. SUM would just add the 3 values and would not smooth the trend. It should be =AVERAGE(B2:B4).
Question 4
A C4: AVERAGE(B2:B4)
B Fill down to C12
C Each range slides down 1 row
Line A — Every spreadsheet formula must start with =. Without it the cell just shows text. It should be =AVERAGE(B2:B4).
Question 5
A B1: Rainfall (mm)
B C1: 3-day average
C C4: =AVERAGE(B2:B4)
D C5: =AVERAGE(B4:B6)
Line D — The window should end on the current row and cover the latest 3 values. C5 should use B3:B5. B4:B6 jumps ahead and uses a later value.

Student with a class code? Join your class →