Range checking in a spreadsheet

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

Range checking in a spreadsheet

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

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)
2. B2 contains 100. What does =AND(B2>=0,B2<=100) return?
A TRUE
B FALSE
3. B2 contains 101. What does =AND(B2>=0,B2<=100) return?
A FALSE
B TRUE
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)
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")
B

Match the Pairs

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

Left columnRight column
1=AND(B2>=0,B2<=100) AUsing > and < excludes the boundary values 0 and 100, so valid marks of 0 or 100 are wrongly rejected.
2=IF(AND(B2>=1,B2<=10),"OK","Out of range") BFlags a value in B2 as OK if it is from 1 to 10, otherwise shows the message Out of range
3Boundary values are allowed in a range check, so the test uses >= and <= rather than > and < CReturns TRUE only when the value in B2 is between 0 and 100 inclusive, so it is a range check
4A range check can flag out-of-range data by wrapping the test in IF with a warning message DThe second test uses >= instead of <=, so the maximum is never enforced and a value such as 150 passes while 50 fails.
5=AND(B2>=0,B2>=100) E=IF(AND(B2>=0,B2<=100),"Valid","Check mark")
6=AND(B2>0,B2<100) F=AND(B2>=0,B2<=100)
C

Definitions

=AND(B2>=0,B2<=100)
D

Fill in the Blanks

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

1.
=___(B2>=0,B2<=100)
2.
=AND(B2__0,B2<=100)
3.
=AND(B2>=0,B2__100)
4.
=__(AND(B2>=0,B2<=100),"OK","Check")
5.
=IF(AND(B2>=_,B2<=100),"OK","Check")
E

Spot the Error

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

Question 1
A =AND(B2>=0,B2>=100)
B =AND(B3>=0,B3<=100)
C =AND(B4>=0,B4<=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)
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")
Question 4
A =AND(B2>=0,B2<=100)
B ' B2=0 gives TRUE
C ' B2=100 gives TRUE
D ' B2=101 gives 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")

Answer Key

Range checking in a spreadsheet
Section A: Quick Questions
Q1
A — =AND(B2>=0,B2<=100)
Q2
A — TRUE
Q3
A — FALSE
Q4
B — =AND(B2>0,B2<=100)
Q5
B — =IF(AND(B2>=0,B2<=100),"OK","Out of range")
Section B: Match the Pairs
Q1
=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
Q2
=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
Q3
Boundary values are allowed in a range check, so the test uses >= and <= rather than > and < → =AND(B2>=0,B2<=100)
Q4
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")
Q5
=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.
Q6
=AND(B2>0,B2<100) → Using > and < excludes the boundary values 0 and 100, so valid marks of 0 or 100 are wrongly rejected.
Section C: Definitions
Q1
=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.
Section D: Fill in the Blanks
Q1
=AND(B2>=0,B2<=100)
Q2
=AND(B2>=0,B2<=100)
Q3
=AND(B2>=0,B2<=100)
Q4
=IF(AND(B2>=0,B2<=100),"OK","Check")
Q5
=IF(AND(B2>=0,B2<=100),"OK","Check")
Section E: Spot the Error
Q1
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).
Q2
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).
Q3
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").
Q4
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.
Q5
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").