← ACARA Digital Technologies Years 7–8

Flagging out-of-range values with IF and OR

Shows Check if B2 is below 0 or above 100, otherwise OK. OR needs just one true test, so it catches both ends. AND would never work because no value is both below 0 and above 100.

Part of: ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 61

Understanding Flagging out-of-range values with IF and OR in ACARA Digital Technologies Years 7–8

What it does: OR returns TRUE when at least one of its tests is true, so it catches a value that is too low or too high. The IF formula writes Check next to every out-of-range value, so filtering for Check shows just those rows. AND needs every test to be true together. A mark of 150 is above 100 but not below 0, so AND would say OK even though it is a problem. B2<0 is true. OR catches the low end, so the result is Check. Each test inside OR is separated by a comma. Leaving out the comma causes an error.

Common mistakes: AND needs both tests true, but no rating is below 1 and above 5 at once, so nothing is ever flagged. It should be OR. The second test should be B3>5 to catch values above the maximum. B3<5 would flag most valid ratings.

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 out-of-range values with IF and OR to a class →

This module's key terms are also in a printable crossword (Modules 261–300) and word search (Modules 261–324).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which formula flags a mark in B2 that is below 0 OR above 100?
=IF(AND(B2<0,B2>100),"Check","OK")=IF(OR(B2<0,B2>100),"Check","OK")
Matches
=IF(AND(B2<0,B2>100),"Check","OK")
↓
AND needs both tests to be true, but no value is below 0 and above 100 at the same time. Every row shows OK. Use OR instead.
Applying
Meanings
Term
=IF(OR(B2<0,B2>100),"Check","OK")
Meaning
Shows Check if B2 is below 0 or above 100, otherwise OK. OR needs just one true test, so it catches both ends. AND would never work because no value is both below 0 and above 100.
Practice
=IF(OR(B2<0,B2>100),"Check","OK")
Testing
Gaps
=IF((B2<0,B2>100),"Check","OK")
Check Answer
Spots
1=IF(AND(B2<1,B2>5),"Check","")
2=IF(OR(B3<1,B3>5),"Check","")

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

The 'Constellation Wolf' badge — Space Legend, 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 flags a mark in B2 that is below 0 OR above 100?
A. =IF(AND(B2<0,B2>100),"Check","OK")
B. =IF(OR(B2<0,B2>100),"Check","OK") ✓
OR flags the value if either test is true. AND needs both tests true at once, which can never happen for a single mark.
2. Why does AND fail when checking for values below 0 and above 100?
A. No value can be below 0 and above 100 at the same time ✓
B. AND only works with text, not numbers
AND needs every test to be true together. A mark of 150 is above 100 but not below 0, so AND would say OK even though it is a problem.
3. B2 holds 105. What does =IF(OR(B2<0,B2>100),"Check","OK") show?
A. Check ✓
B. OK
B2>100 is true, and OR only needs one true test, so the formula shows Check.
4. B2 holds -5. What does =IF(OR(B2<0,B2>100),"Check","OK") show?
A. Check ✓
B. OK
B2<0 is true. OR catches the low end, so the result is Check.
5. B2 holds 100. What does =IF(OR(B2<0,B2>100),"Check","OK") show?
A. Check
B. OK ✓
100 is not below 0 and not above 100, so both tests are false and the formula shows OK. Using > keeps 100 as a valid value.

Match the Pairs

=IF(OR(B2<0,B2>100),"Check","OK") → Shows Check if the mark in B2 is below 0 or above 100, otherwise shows OK
=IF(OR(C2<0,C2>100),"Check","OK") → Flags an English mark in C2 that is out of range, and shows OK for a valid one
OR flags a value that fails either the minimum test or the maximum test, so one true test is enough → =IF(OR(B2<0,B2>100),"Check","OK")
To list only the problem rows, filter the flag column for the word Check → Filter column E where the value = "Check"
=IF(AND(B2<0,B2>100),"Check","OK") → AND needs both tests to be true, but no value is below 0 and above 100 at the same time. Every row shows OK. Use OR instead.
=IF(OR(B2<0,B2>100,"Check","OK") → The OR bracket is never closed after B2>100, so Check and OK become extra OR tests. Close OR with ) before the comma.

Key Term

=IF(OR(B2<0,B2>100),"Check","OK")
Shows Check if B2 is below 0 or above 100, otherwise OK. OR needs just one true test, so it catches both ends. AND would never work because no value is both below 0 and above 100.

Fill in the Blanks

1. =IF(OR(B2<0,B2>100),"Check","OK")
Hint: Type the function that is true when either test is true.
2. =IF(OR(B2<0,B2>100),"Check","OK")
Hint: Type the comparison operator that tests for a value above the maximum.
3. =IF(OR(B2<0,B2>100),"Check","OK")
Hint: Type the comparison operator that tests for a value below the minimum.
4. =IF(OR(B2<0,B2>100),"Check","OK")
Hint: Type the function that picks one label when the test is true and another when it is false.
5. =IF(OR(B2<0,B2>100),"Check","OK")
Hint: Type the cell reference being tested, the same cell as in the first test.

Spot the Error

Question 1
A =IF(AND(B2<1,B2>5),"Check","")
B =IF(OR(B3<1,B3>5),"Check","")
C =IF(OR(B4<1,B4>5),"Check","")
Line A — AND needs both tests true, but no rating is below 1 and above 5 at once, so nothing is ever flagged. It should be OR.
Question 2
A =IF(OR(B2<1,B2>5),"Check","")
B =IF(OR(B3<1,B3<5),"Check","")
C =IF(OR(B4<1,B4>5),"Check","")
Line B — The second test should be B3>5 to catch values above the maximum. B3<5 would flag most valid ratings.
Question 3
A =IF(OR(C2<1,C2>5),Check,"")
B =IF(OR(C3<1,C3>5),"Check","")
C =IF(OR(C4<1,C4>5),"Check","")
Line A — Text results must be inside quotes. Check should be "Check", otherwise the spreadsheet treats it as a name and shows an error.
Question 4
A =IF(OR(B2<1,B2>5),"Check","")
B =IF(OR(B3<1,B3>5),"Check","")
C =IF(OR(B4<1,B4>5),"Check","")
D =IF(OR(B5<1,B4>5),"Check","")
Line D — Both tests must look at the same cell. The second test should be B5>5, not B4>5, which checks the row above.
Question 5
A =IF(OR(B2<1,B2>5),"Check","")
B Copy the formula down column C
C Filter column C for OK
Line C — To find the out-of-range values you filter for Check. The formula leaves the valid rows blank, so filtering for OK finds nothing.

Student with a class code? Join your class →