Types of validation check
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. A form will not let you submit if the Surname box is empty. Which validation check is this?
2. A school canteen app only accepts a quantity between 1 and 20. Which check is this?
3. Which entry would fail a type check on an Age field that must be a whole number?
4. A Year Level field must be a number from 7 to 12. A student types 14. Which check rejects it?
5. An Australian postcode must be exactly 4 digits. Which entry fails a length check?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1=IF(ISBLANK(A2),"Missing","OK") | AA presence check: tests that the cell is not left empty | |
| 2=IF(AND(B2>=0,B2<=100),"OK","Error") | B=IF(ISNUMBER(D2),"OK","Error") | |
| 3A length check tests that text has the right number of characters, such as a 4-digit postcode | C=IF(LEN(C2)=4,"OK","Error") | |
| 4A type check tests that an entry is the right kind of data, such as a number rather than text | DA range check: tests that a number is between 0 and 100 | |
| 5Range check on Age: allow only the whole numbers 0 and 120 | EA range check allows every value between a lowest and a highest limit, not just the two end values. Age should be allowed from 0 to 120 inclusive. | |
| 6List check on Year level: use a presence check to accept only 9 or 10 | FA presence check only tests that something was entered. To accept only 9 or 10, you need a list check. |
C
Definitions
validation check
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=NOT(_______(A2))
2.
=AND(B2>=0,B2__100)
3.
=________(C2)
4.
=AND(LEN(D2)=10,____(D2,2)="04")
5.
=_______(G2:G5,E2)>0
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
INPUT name
B
valid ← TRUE
C
IF name <> "" THEN
D
valid ← FALSE
E
ENDIF
Question 2
A
INPUT age
B
IF age >= 0 OR age <= 99 THEN
C
OUTPUT "Accepted"
D
ELSE
E
OUTPUT "Out of range"
F
ENDIF
Question 3
A
INPUT quantity
B
IF ISTEXT(quantity) THEN
C
OUTPUT "Valid number"
D
ELSE
E
OUTPUT "Numbers only"
Question 4
A
INPUT postcode
B
size ← LENGTH(postcode)
C
IF size > 4 THEN
D
OUTPUT "Length OK"
E
ELSE
F
OUTPUT "Must be 4 digits"
Question 5
A
INPUT team
B
teams ← ["Red","Blue","Gold"]
C
valid ← FALSE
D
IF team = teams THEN
E
valid ← TRUE
F
ENDIF
Answer Key
Types of validation check
Section A: Quick Questions
Q1
A — Presence check
Q2
A — Range check
Q3
A — fifteen
Q4
B — Range check
Q5
B — 305
Section B: Match the Pairs
Q1
=IF(ISBLANK(A2),"Missing","OK") → A presence check: tests that the cell is not left empty
Q2
=IF(AND(B2>=0,B2<=100),"OK","Error") → A range check: tests that a number is between 0 and 100
Q3
A length check tests that text has the right number of characters, such as a 4-digit postcode → =IF(LEN(C2)=4,"OK","Error")
Q4
A type check tests that an entry is the right kind of data, such as a number rather than text → =IF(ISNUMBER(D2),"OK","Error")
Q5
Range check on Age: allow only the whole numbers 0 and 120 → A range check allows every value between a lowest and a highest limit, not just the two end values. Age should be allowed from 0 to 120 inclusive.
Q6
List check on Year level: use a presence check to accept only 9 or 10 → A presence check only tests that something was entered. To accept only 9 or 10, you need a list check.
Section C: Definitions
Q1
validation check — A test that an entry is sensible. Types include presence, data type, range, length, format and list. Each one tests a different property of the entry.Section D: Fill in the Blanks
Q1
=NOT(ISBLANK(A2))Q2
=AND(B2>=0,B2<=100)Q3
=ISNUMBER(C2)Q4
=AND(LEN(D2)=10,LEFT(D2,2)="04")Q5
=COUNTIF(G2:G5,E2)>0Section E: Spot the Error
Q1
C — This presence check is reversed. It marks the entry invalid when a name IS entered. It should be IF name = "" THEN, so only a blank entry is rejected.
Q2
B — A range check needs AND so the age is both at least 0 and at most 99. With OR, every number passes, so the check never rejects anything.
Q3
B — A quantity must be a number, so the type check should use ISNUMBER(quantity). ISTEXT would accept words and reject real quantities.
Q4
C — An Australian postcode must be exactly 4 characters long. The length check should be IF size = 4 THEN. Testing size > 4 would accept only longer entries and reject valid postcodes.
Q5
D — A list check must test whether the entry is a member of the list, using IN. Comparing team = teams compares one value with the whole list, so it is never true.