The MEDIAN function

Tip: Choose Save as PDF in the print dialog to download. Use A4 paper size.

The MEDIAN function

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which formula finds the middle value of the numbers in B2:B30?
A =MEDIAN(B2:B30)
B =MIDDLE(B2:B30)
2. A range holds 5, 9, 2, 7, 4 (5 values). What does =MEDIAN return?
A 7
B 5
3. A range holds 2, 4, 6, 10 (4 values). What does =MEDIAN return?
A 5
B 6
4. Do you need to sort the cells before using =MEDIAN?
A No, MEDIAN orders the numbers itself
B Yes, the cells must be sorted first
5. A range has 9 numbers. How does MEDIAN choose its result?
A It averages the two middle values
B It returns the 5th value once ordered
B

Match the Pairs

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

Left columnRight column
1=MEDIAN(B2:B30) AThe range is not in brackets. The function needs =MEDIAN(B2:B30).
2=MEDIAN(C2:C11) BWith 10 values, returns the mean of the 5th and 6th values when ordered
3Find the median of the marks in D2:D20 without sorting the cells first CReturns the middle value of the numbers in B2:B30 once they are in order
4With an odd number of values (such as 5 numbers in A1:A5), the median is the exact middle value DThe student thinks the cells must be sorted first, but MEDIAN orders the values itself, so no sorting is needed.
5=MEDIAN B2:B30 E=MEDIAN(A1:A5)
6=MEDIAN(B2:B30) after sorting is required F=MEDIAN(D2:D20)
C

Definitions

=MEDIAN(B2:B30)
D

Fill in the Blanks

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

1.
=______(B2:B30)
2.
=MEDIAN(______)
3.
=MEDIAN(C2:C__)
4.
=______(D2:D12)
5.
=MEDIAN(_____)
E

Spot the Error

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

Question 1
A A2: 5
B A3: 9
C A4: 7
D B1: =MEDAIN(A2:A4)
Question 2
A Values: 8, 3, 5, 9, 1
B Sorted: 1, 3, 8, 5, 9
C Middle value: 5
D =MEDIAN(A2:A6) returns 5
Question 3
A MEDIAN(B2:B5)
B B2:B5 holds 2, 4, 6, 10
C Middle two values: 4 and 6
D Result: (4 + 6) / 2 = 5
Question 4
A Marks in B2:B6: 7, 3, 9, 5, 6
B No need to sort the cells first
C =MEDIAN(B2:B6
D Result: 6
Question 5
A Scores in C2:C8 are unsorted
B =AVERAGE(C2:C8)
C Excel orders them itself
D Result is the middle value

Answer Key

The MEDIAN function
Section A: Quick Questions
Q1
A — =MEDIAN(B2:B30)
Q2
B — 5
Q3
A — 5
Q4
A — No, MEDIAN orders the numbers itself
Q5
B — It returns the 5th value once ordered
Section B: Match the Pairs
Q1
=MEDIAN(B2:B30) → Returns the middle value of the numbers in B2:B30 once they are in order
Q2
=MEDIAN(C2:C11) → With 10 values, returns the mean of the 5th and 6th values when ordered
Q3
Find the median of the marks in D2:D20 without sorting the cells first → =MEDIAN(D2:D20)
Q4
With an odd number of values (such as 5 numbers in A1:A5), the median is the exact middle value → =MEDIAN(A1:A5)
Q5
=MEDIAN B2:B30 → The range is not in brackets. The function needs =MEDIAN(B2:B30).
Q6
=MEDIAN(B2:B30) after sorting is required → The student thinks the cells must be sorted first, but MEDIAN orders the values itself, so no sorting is needed.
Section C: Definitions
Q1
=MEDIAN(B2:B30) — Returns the middle value of the numbers in B2:B30 once ordered. With an even count it averages the middle two. You do not need to sort the cells first.
Section D: Fill in the Blanks
Q1
=MEDIAN(B2:B30)
Q2
=MEDIAN(B2:B30)
Q3
=MEDIAN(C2:C20)
Q4
=MEDIAN(D2:D12)
Q5
=MEDIAN(A1:A6)
Section E: Spot the Error
Q1
D — The function name is misspelt. The spreadsheet does not recognise MEDAIN. It should be =MEDIAN(A2:A4), which returns 7.
Q2
B — The list is not in order. Sorted from smallest to largest it is 1, 3, 5, 8, 9, so the middle value is 5.
Q3
A — A formula must start with =. Without it the spreadsheet treats the entry as plain text. It should be =MEDIAN(B2:B5).
Q4
C — The closing bracket is missing. The function call must end with ), so it should be =MEDIAN(B2:B6).
Q5
B — AVERAGE finds the mean, not the middle value. To get the median the formula should be =MEDIAN(C2:C8).