Flagging invalid entries with IF

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

Flagging invalid entries with IF

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which formula shows OK when B2 is between 0 and 120, and Check otherwise?
A =IF(AND(B2>=0,B2<=120),"OK","Check")
B =IF(B2>=0,B2<=120,"OK","Check")
2. A student types =IF(AND(B2>=0,B2<=120),"OK","Check") in C2. What does C2 show if B2 is 150?
A OK
B Check
3. What does C2 show if B2 is 0 and the formula is =IF(AND(B2>=0,B2<=120),"OK","Check")?
A Check
B OK
4. You wrote the IF formula in C2 and want to flag every row in the data. What should you do next?
A Copy the formula down the column beside the data
B Retype the formula in each row with B2 unchanged
5. After copying the formula down, how do you quickly see only the invalid entries?
A Filter the flag column on Check
B Filter the flag column on OK
B

Match the Pairs

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

Left columnRight column
1=IF(AND(B2>=0,B2<=120),"OK","Check") AFilter column C where value = "Check"
2=IF(B2>=0,"OK","Check") BShows OK when B2 is zero or more, and Check when B2 is negative
3Use AND to flag an age in B2 that is outside the range 0 to 120 as Check COK and Check are not in quotation marks, so the spreadsheet reads them as names and shows #NAME? instead of text
4After copying the flag formula down, filter the helper column to show only Check rows D=IF(AND(B2>=0,B2<=120),"OK","Check")
5=IF(AND(B2>=0,B2<=120),OK,Check) EShows OK when B2 is between 0 and 120, and Check for any other value
6=IF(OR(B2>=0,B2<=120),"OK","Check") FOR passes when either test is true, so every number counts as OK, including -5 and 200
C

Definitions

=IF(AND(B2>=0,B2<=120),"OK","Check")
D

Fill in the Blanks

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

1.
=__(AND(B2>=0,B2<=120),"OK","Check")
2.
=IF(___(B2>=0,B2<=120),"OK","Check")
3.
=IF(AND(B2>=0,B2__120),"OK","Check")
4.
=IF(AND(__>=0,B3<=120),"OK","Check")
5.
=IF(AND(B2__0,B2<=120),"OK","Check")
E

Spot the Error

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

Question 1
A B1: Age
B C1: Flag
C C2: =IF(B2>120,"Check","OK")
D Copy C2 across to D2
E Filter column C on Check
Question 2
A B1: Score
B C1: Flag
C C2: =IF(B2>100,"Check","OK")
D Filter column C on OK
E Fix or remove those rows
Question 3
A B1: Age
B C1: Flag
C C2: =IF(B2>120,"OK","Check")
D Copy C2 down the column
Question 4
A B1: Height (cm)
B C2: IF(B2>250,"Check","OK")
C Copy C2 down to C20
D Filter column C on Check
Question 5
A B1: Age
B C1: Flag
C C2: =IF(B2<0,Check,OK)
D Copy C2 down to C9

Answer Key

Flagging invalid entries with IF
Section A: Quick Questions
Q1
A — =IF(AND(B2>=0,B2<=120),"OK","Check")
Q2
B — Check
Q3
B — OK
Q4
A — Copy the formula down the column beside the data
Q5
A — Filter the flag column on Check
Section B: Match the Pairs
Q1
=IF(AND(B2>=0,B2<=120),"OK","Check") → Shows OK when B2 is between 0 and 120, and Check for any other value
Q2
=IF(B2>=0,"OK","Check") → Shows OK when B2 is zero or more, and Check when B2 is negative
Q3
Use AND to flag an age in B2 that is outside the range 0 to 120 as Check → =IF(AND(B2>=0,B2<=120),"OK","Check")
Q4
After copying the flag formula down, filter the helper column to show only Check rows → Filter column C where value = "Check"
Q5
=IF(AND(B2>=0,B2<=120),OK,Check) → OK and Check are not in quotation marks, so the spreadsheet reads them as names and shows #NAME? instead of text
Q6
=IF(OR(B2>=0,B2<=120),"OK","Check") → OR passes when either test is true, so every number counts as OK, including -5 and 200
Section C: Definitions
Q1
=IF(AND(B2>=0,B2<=120),"OK","Check") — Shows OK if B2 is between 0 and 120, otherwise Check. Copy it down the column beside the data, then filter on Check to find the invalid entries.
Section D: Fill in the Blanks
Q1
=IF(AND(B2>=0,B2<=120),"OK","Check")
Q2
=IF(AND(B2>=0,B2<=120),"OK","Check")
Q3
=IF(AND(B2>=0,B2<=120),"OK","Check")
Q4
=IF(AND(B3>=0,B3<=120),"OK","Check")
Q5
=IF(AND(B2>=0,B2<=120),"OK","Check")
Section E: Spot the Error
Q1
D — The flag must appear beside every row, so the formula is copied down column C to C9, not across to D2.
Q2
D — Invalid entries are marked Check, so you filter on Check. Filtering on OK would only show the valid rows.
Q3
C — The results are swapped. When the age is over 120 the entry is invalid and should show Check, otherwise OK.
Q4
B — A formula must start with an equals sign. It should be =IF(B2>250,"Check","OK").
Q5
C — Text results in an IF formula must be in double quotes. It should be =IF(B2<0,"Check","OK").