← VCE Data Analytics U3O1

Range checking in a spreadsheet

A range check formula. It returns TRUE only if the value in B2 is at least the minimum (0) and at most the maximum (100), so boundary values pass and anything outside is rejected.

Part of: VCE Data Analytics - U3O1 - Data Analytics — Module 49, ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 60, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 73

Understanding Range checking in a spreadsheet in VCE Data Analytics U3O1

What it does: Using >= and <= means 0 and 100 themselves pass the check, as they are the boundary values. If the AND test is FALSE, IF returns the warning text so the invalid entry is easy to spot. AND needs BOTH conditions to be true. OR would accept any number at all, because every number is either at least 0 or at most 100. 100 is a boundary value. Because the formula uses <=, the maximum itself is allowed, so the check passes. 101 is just above the maximum of 100, so B2<=100 is FALSE and AND returns FALSE. The minimum goes with >= and the maximum goes with <=. Swapping them means no value can ever pass.

Common mistakes: The upper limit uses >= instead of <=. Only values of 100 or more would pass the maximum test. It should be =AND(B2>=0,B2<=100). C5>1 excludes the boundary value 1, so a valid rating of 1 would be rejected. The minimum test should use >=: =AND(C5>=1,C5<=10).

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 Range checking in a spreadsheet to a class →

This module's key terms are also in a printable crossword (Modules 17–49) and word search (Modules 17–91).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which formula is a correct range check that accepts marks from 0 to 100?
=AND(B2>=0,B2<=100)=OR(B2>=0,B2<=100)
Matches
=AND(B2>=0,B2>=100)
↓
The second test uses >= instead of <=, so the maximum is never enforced and a value such as 150 passes while 50 fails.
Applying
Meanings
Term
=AND(B2>=0,B2<=100)
Meaning
A range check formula. It returns TRUE only if the value in B2 is at least the minimum (0) and at most the maximum (100), so boundary values pass and anything outside is rejected.
Practice
=IF(AND(B2>=0,B2<=100),"OK","Out of range")
Testing
Gaps
=(B2>=0,B2<=100)
Check Answer
Spots
1=AND(B2>=0,B2>=100)
2=AND(B3>=0,B3<=100)

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

The 'Gulper Eel' badge — Deep Streak, 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 is a correct range check that accepts marks from 0 to 100?
A. =AND(B2>=0,B2<=100) ✓
B. =OR(B2>=0,B2<=100)
AND needs BOTH conditions to be true. OR would accept any number at all, because every number is either at least 0 or at most 100.
2. B2 contains 100. What does =AND(B2>=0,B2<=100) return?
A. TRUE ✓
B. FALSE
100 is a boundary value. Because the formula uses <=, the maximum itself is allowed, so the check passes.
3. B2 contains 101. What does =AND(B2>=0,B2<=100) return?
A. FALSE ✓
B. TRUE
101 is just above the maximum of 100, so B2<=100 is FALSE and AND returns FALSE.
4. Which formula wrongly rejects a mark of exactly 0 when 0 is allowed?
A. =AND(B2>=0,B2<=100)
B. =AND(B2>0,B2<=100) ✓
Using > instead of >= leaves out the minimum boundary value. 0 is not greater than 0, so the check fails.
5. Which formula flags a mark as out of range for a range of 0 to 100?
A. =IF(OR(B2>=0,B2<=100),"OK","Out of range")
B. =IF(AND(B2>=0,B2<=100),"OK","Out of range") ✓
IF uses the AND range check as its test. Using OR means nothing is ever flagged, because every number meets at least one condition.

Match the Pairs

=AND(B2>=0,B2<=100) → Returns TRUE only when the value in B2 is between 0 and 100 inclusive, so it is a range check
=IF(AND(B2>=1,B2<=10),"OK","Out of range") → Flags a value in B2 as OK if it is from 1 to 10, otherwise shows the message Out of range
Boundary values are allowed in a range check, so the test uses >= and <= rather than > and < → =AND(B2>=0,B2<=100)
A range check can flag out-of-range data by wrapping the test in IF with a warning message → =IF(AND(B2>=0,B2<=100),"Valid","Check mark")
=AND(B2>=0,B2>=100) → The second test uses >= instead of <=, so the maximum is never enforced and a value such as 150 passes while 50 fails.
=AND(B2>0,B2<100) → Using > and < excludes the boundary values 0 and 100, so valid marks of 0 or 100 are wrongly rejected.

Key Term

=AND(B2>=0,B2<=100)
A range check formula. It returns TRUE only if the value in B2 is at least the minimum (0) and at most the maximum (100), so boundary values pass and anything outside is rejected.

Fill in the Blanks

1. =AND(B2>=0,B2<=100)
Hint: Type the function that returns TRUE only when both conditions are true.
2. =AND(B2>=0,B2<=100)
Hint: Type the operator that checks the value is at least the minimum, so the minimum itself passes.
3. =AND(B2>=0,B2<=100)
Hint: Type the operator that checks the value is at most the maximum, so the maximum itself passes.
4. =IF(AND(B2>=0,B2<=100),"OK","Check")
Hint: Type the function that shows one message when the range check is TRUE and another when it is FALSE.
5. =IF(AND(B2>=0,B2<=100),"OK","Check")
Hint: Type the lowest percentage mark that should be accepted.

Spot the Error

Question 1
A =AND(B2>=0,B2>=100)
B =AND(B3>=0,B3<=100)
C =AND(B4>=0,B4<=100)
Line A — The upper limit uses >= instead of <=. Only values of 100 or more would pass the maximum test. It should be =AND(B2>=0,B2<=100).
Question 2
A =AND(C2>=1,C2<=10)
B =AND(C3>=1,C3<=10)
C =AND(C4>=1,C4<=10)
D =AND(C5>1,C5<=10)
Line D — C5>1 excludes the boundary value 1, so a valid rating of 1 would be rejected. The minimum test should use >=: =AND(C5>=1,C5<=10).
Question 3
A ' Y means score is 0 to 10
B =IF(OR(A2>=0,A2<=10),"Y","N")
C =IF(AND(A3>=0,A3<=10),"Y","N")
Line B — OR returns TRUE if either test passes, so every number is accepted (for example -5 or 50). A range check needs AND so both the minimum and maximum tests must pass: =IF(AND(A2>=0,A2<=10),"Y","N").
Question 4
A =AND(B2>=0,B2<=100)
B ' B2=0 gives TRUE
C ' B2=100 gives TRUE
D ' B2=101 gives TRUE
Line D — 101 is above the maximum of 100, so the range check returns FALSE. Only 0 to 100 (including the boundaries 0 and 100) give TRUE.
Question 5
A ' Age must be 5 to 18
B =IF(AND(B2>=5,B2<=18),"Y","N")
C =IF(AND(B3>=5,B3<=18),"N","Y")
Line C — The results are swapped. When AND is TRUE the age is in range, so the IF should return "Y" first and "N" second: =IF(AND(B3>=5,B3<=18),"Y","N").

Student with a class code? Join your class →