Turning an age into an age band with IF

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

Turning an age into an age band with IF

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which formula turns the age in B2 into an age band?
A =IF(B2<13,"Under 13",IF(B2<16,"13-15","16+"))
B =B2
2. Why do we swap an exact age for an age band?
A A band shows less personal data than an exact age
B A band is more accurate than an exact age
3. In =IF(B2<13,"Under 13",IF(B2<16,"13-15","16+")), what is the second IF doing?
A It runs a separate formula in another cell
B It is placed inside the first IF to check the next age limit
4. B2 holds the age 14. What does the nested IF formula return?
A Under 13
B 13-15
5. After typing the formula in C2, how do you get age bands for the whole column?
A Copy the formula down the column
B Retype the age numbers into column C
B

Match the Pairs

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

Left columnRight column
1=IF(B2<13,"Under 13",IF(B2<16,"13-15","16+")) APaste special > Values only, then delete column B
2IF(B2<16,"13-15","16+") BChecks the age in B2 and shows an age band (Under 13, 13-15 or 16+) instead of the exact age
3Copy the age band formula down the column so every student gets a band, with B2 changing to B3, B4 and so on CDrag the fill handle from C2 down to C9
4Paste the age bands as values only, then delete the exact age column, so the exact ages are really gone DThe formulas in column C still need the exact ages in column B, so deleting B breaks them with #REF! errors. Paste the bands as values first.
5=IF(B2<13,"Under 13",IF(B2<16,"13-15")) EThe inner IF has no value for ages 16 and over, so the formula gives FALSE for those students. It needs a third part such as "16+".
6Delete column B while column C still holds the age band formulas FThe inner IF, which only runs when the age is not under 13 and decides between 13-15 and 16+
C

Definitions

=IF(B2<13,"Under 13",IF(B2<16,"13-15","16+"))
D

Fill in the Blanks

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

1.
=IF(B2<13,"0-12",__(B2<16,"13-15","16+"))
2.
=IF(B2_13,"0-12",IF(B2<16,"13-15","16+"))
3.
=IF(__<13,"0-12",IF(B2<16,"13-15","16+"))
4.
=IF(B_<13,"0-12",IF(B3<16,"13-15","16+"))
5.
Copy C, Paste special: ______ only Delete column B, the exact age
E

Spot the Error

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

Question 1
A =IF(B2<13,"Under 13",
B IF(B2<16,"13-15",
C "16+")
Question 2
A =IF(B2<13,"Under 13",
B IF(B2<16,"13-15","16+"))
C Copy D2 down to D30
D Paste C2:C30 as values only
E Delete column B (exact age)
Question 3
A =IF(B2>13,"Under 13",
B IF(B2<16,"13-15","16+"))
C Copy C2 down to C30
Question 4
A =IF(B2<13,"Under 13",
B IF(B2<6,"13-15",
C "16+"))
Question 5
A =IF(B2<13,Under 13,
B IF(B2<16,"13-15","16+"))
C Paste C2:C30 as values only
D Delete column B (exact age)

Answer Key

Turning an age into an age band with IF
Section A: Quick Questions
Q1
A — =IF(B2<13,"Under 13",IF(B2<16,"13-15","16+"))
Q2
A — A band shows less personal data than an exact age
Q3
B — It is placed inside the first IF to check the next age limit
Q4
B — 13-15
Q5
A — Copy the formula down the column
Section B: Match the Pairs
Q1
=IF(B2<13,"Under 13",IF(B2<16,"13-15","16+")) → Checks the age in B2 and shows an age band (Under 13, 13-15 or 16+) instead of the exact age
Q2
IF(B2<16,"13-15","16+") → The inner IF, which only runs when the age is not under 13 and decides between 13-15 and 16+
Q3
Copy the age band formula down the column so every student gets a band, with B2 changing to B3, B4 and so on → Drag the fill handle from C2 down to C9
Q4
Paste the age bands as values only, then delete the exact age column, so the exact ages are really gone → Paste special > Values only, then delete column B
Q5
=IF(B2<13,"Under 13",IF(B2<16,"13-15")) → The inner IF has no value for ages 16 and over, so the formula gives FALSE for those students. It needs a third part such as "16+".
Q6
Delete column B while column C still holds the age band formulas → The formulas in column C still need the exact ages in column B, so deleting B breaks them with #REF! errors. Paste the bands as values first.
Section C: Definitions
Q1
=IF(B2<13,"Under 13",IF(B2<16,"13-15","16+")) — A nested IF formula that replaces an exact age with an age band, so the sheet shows a group like 13-15 instead of the exact number. This keeps less personal data.
Section D: Fill in the Blanks
Q1
=IF(B2<13,"0-12",IF(B2<16,"13-15","16+"))
Q2
=IF(B2<13,"0-12",IF(B2<16,"13-15","16+"))
Q3
=IF(B2<13,"0-12",IF(B2<16,"13-15","16+"))
Q4
=IF(B3<13,"0-12",IF(B3<16,"13-15","16+"))
Q5
Copy C, Paste special: Values only Delete column B, the exact age
Section E: Spot the Error
Q1
C — A nested IF needs one closing bracket for each IF. The formula has two IFs, so the last line must end with two brackets: "16+")).
Q2
C — The formula is in C2, and D2 is empty, so copying D2 copies nothing. It should say: Copy C2 down to C30.
Q3
A — The test must be B2<13 so that only ages below 13 get "Under 13". With > 13, older students would be put in the Under 13 band.
Q4
B — The second test should be B2<16. With B2<6, almost nobody gets 13-15, so a 14 year old would be labelled 16+.
Q5
A — Text results in a formula must be inside quotes. It should be =IF(B2<13,"Under 13", so the sheet does not read Under 13 as a name.