Smoothing a trend with a moving average

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

2. Which formula is a 3-month moving average of B2, B3 and B4?

A =SUM(B2:B4)

B =AVERAGE(B2:B4)

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)

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

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

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

Definitions

=AVERAGE(B2:B4)

Fill in the Blanks

Complete each line of code by filling in the missing parts.

=_______(B2:B4)

=AVERAGE(B2:__)

D4: =AVERAGE(B2:B4) Copied down to D5: =AVERAGE(__:B5)

=AVERAGE(B2:B4) Number of months averaged: _

Refs like B2 are ________ and slide down

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A C4: =AVERAGE(B2:B5)

B C5: =AVERAGE(B3:B5)

C C6: =AVERAGE(B4:B6)

Question 2

A C4: =AVERAGE(B2:B4)

B C5: =AVERAGE(B2:B4)

C C6: =AVERAGE(B4:B6)

Question 3

A B1: Sales

B C1: 3-month moving average

C C4: =SUM(B2:B4)

Question 4

A C4: AVERAGE(B2:B4)

B Fill down to C12

C Each range slides down 1 row

Question 5

A B1: Rainfall (mm)

B C1: 3-day average

C C4: =AVERAGE(B2:B4)

D C5: =AVERAGE(B4:B6)

Answer Key

Smoothing a trend with a moving average

Section A: Quick Questions

A — It smooths the ups and downs to show the trend

B — =AVERAGE(B2:B4)

A — =AVERAGE(B3:B5)

A — Relative references let the range slide down as you copy the formula

B — 3 values, the latest month and the 2 before it

Section B: 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.

Section C: Definitions

=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.

Section D: Fill in the Blanks

=AVERAGE(B2:B4)

D4: =AVERAGE(B2:B4)
Copied down to D5: =AVERAGE(B3:B5)

=AVERAGE(B2:B4)
Number of months averaged: 3

Refs like B2 are relative and slide down

Section E: Spot the Error

A — A 3-month moving average uses exactly 3 values. B2:B5 covers 4 cells. It should be =AVERAGE(B2:B4).

B — The window must slide down 1 row each time. C5 should average the latest 3 values, B3:B5, not repeat B2:B4.

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).

A — Every spreadsheet formula must start with =. Without it the cell just shows text. It should be =AVERAGE(B2:B4).

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.