← ACARA Digital Technologies Years 9–10

Flagging invalid entries with IF

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.

Part of: ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 77

Understanding Flagging invalid entries with IF in ACARA Digital Technologies Years 9–10

What it does: AND combines the lower and upper range tests so that only values inside the range show OK. Filtering on Check hides the OK rows, so only the invalid entries are left to look at and fix. AND joins both tests into one check. Without it, IF gets too many arguments and the formula breaks. 150 is greater than 120, so the AND test is false and IF returns the third part, "Check". Copying down makes B2 change to B3, B4 and so on, so every row gets its own check. -5 is less than 120, so the second formula would wrongly say OK. The first one also tests the lower limit of 0.

Common mistakes: The flag must appear beside every row, so the formula is copied down column C to C9, not across to D2. Invalid entries are marked Check, so you filter on Check. Filtering on OK would only show the valid rows.

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 Flagging invalid entries with IF to a class →

This module's key terms are also in a printable crossword (Modules 255–329) and word search (Modules 226–329).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which formula shows OK when B2 is between 0 and 120, and Check otherwise?
=IF(AND(B2>=0,B2<=120),"OK","Check")=IF(B2>=0,B2<=120,"OK","Check")
Matches
=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
Applying
Meanings
Term
=IF(AND(B2>=0,B2<=120),"OK","Check")
Meaning
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.
Practice
=IF(AND(B2>=0,B2<=100),"OK","Check")
Testing
Gaps
=(AND(B2>=0,B2<=120),"OK","Check")
Check Answer
Spots
3C2: =IF(B2>120,"Check","OK")
4Copy C2 across to D2

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

The 'Snow Leopard' badge — Silent Debugger, 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. 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")
AND joins both tests into one check. Without it, IF gets too many arguments and the formula breaks.
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 ✓
150 is greater than 120, so the AND test is false and IF returns the third part, "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 ✓
The check uses >= and <=, so 0 and 120 are both allowed. Both tests are true, so the result is 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
Copying down makes B2 change to B3, B4 and so on, so every row gets its own check.
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
Check marks the rows that failed the test, so filtering on Check leaves just the entries that need fixing.

Match the Pairs

=IF(AND(B2>=0,B2<=120),"OK","Check") → Shows OK when B2 is between 0 and 120, and Check for any other value
=IF(B2>=0,"OK","Check") → Shows OK when B2 is zero or more, and Check when B2 is negative
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")
After copying the flag formula down, filter the helper column to show only Check rows → Filter column C where value = "Check"
=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
=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

Key Term

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

Fill in the Blanks

1. =IF(AND(B2>=0,B2<=120),"OK","Check")
Hint: Type the function name that picks between OK and Check depending on a test.
2. =IF(AND(B2>=0,B2<=120),"OK","Check")
Hint: Type the function that is only true when both range tests pass.
3. =IF(AND(B2>=0,B2<=120),"OK","Check")
Hint: Type the comparison operator that keeps the value at or below the upper limit.
4. =IF(AND(B3>=0,B3<=120),"OK","Check")
Hint: This is the copied-down formula for the second data row. Type the cell reference being tested.
5. =IF(AND(B2>=0,B2<=120),"OK","Check")
Hint: Type the comparison operator that stops the value going below the lower limit.

Spot the 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
Line D — The flag must appear beside every row, so the formula is copied down column C to C9, not across to D2.
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
Line D — Invalid entries are marked Check, so you filter on Check. Filtering on OK would only show the valid rows.
Question 3
A B1: Age
B C1: Flag
C C2: =IF(B2>120,"OK","Check")
D Copy C2 down the column
Line C — The results are swapped. When the age is over 120 the entry is invalid and should show Check, otherwise OK.
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
Line B — A formula must start with an equals sign. It should be =IF(B2>250,"Check","OK").
Question 5
A B1: Age
B C1: Flag
C C2: =IF(B2<0,Check,OK)
D Copy C2 down to C9
Line C — Text results in an IF formula must be in double quotes. It should be =IF(B2<0,"Check","OK").

Student with a class code? Join your class →