Number range validation in a spreadsheet

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A spreadsheet cell for student age uses 'Whole number between' with minimum 11 and maximum 14. Which entry is accepted?

A 13

B 15

2. Which validation setting should you choose for a cell that holds a height like 1.52 metres?

A Decimal between

B Whole number between

3. A cell uses 'Whole number between' 11 and 14. What happens when someone types 9?

A It is accepted because it is a whole number

B It is rejected because it is below the minimum

4. Which is the correct way to set up a rule so only ages 11 to 14 are allowed?

A Minimum: 14, Maximum: 11

B Minimum: 11, Maximum: 14

5. True or false: a 'Whole number between' 11 and 14 rule accepts 12.5.

A False

B True

Match the Pairs

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

Left column		Right column
1Data > Data validation > Whole number between 11 and 14		ADecimal between 1 and 2
2Data > Data validation > Decimal between 0 and 100		BWhole number between 11 and 14
3Use this rule when a cell must hold only a whole number age from 11 to 14		COnly lets the cell accept whole numbers from 11 to 14, such as an age
4Use this rule when a cell must hold a height in metres, like 1.55, from 1 to 2		DLets the cell accept numbers with parts, like 72.5, from 0 to 100
5Whole number between 14 and 11		EThe minimum and maximum are the wrong way around. The minimum (11) must come first and the maximum (14) second.
6Whole number between 1 and 100 (used for a test score like 85.5)		FWhole number rejects values with decimal parts, so 85.5 would be refused. Decimal should be used instead.

Definitions

Data > Data validation > Whole number between

Fill in the Blanks

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

Data > Data validation > ____________ between

Allow: Whole number Data: _______ Minimum: 11

Data: between Minimum: __ Maximum: 14

Allow: _______ Minimum: 1.2 Maximum: 2.0

Allow: Whole number Minimum: 11 Maximum: __

Spot the Error

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

Question 1

A Select cells B2:B30

B Data > Data validation

C Allow: Whole number

D Data: not between

E Minimum: 11

F Maximum: 14

Question 2

A Allow: Decimal

B Data: between

C Minimum: 11

D Maximum: 14

Question 3

A Select cells C2:C30

B Allow: Whole number

C Data: between

D Minimum: 1.2

E Maximum: 2.0

Question 4

A Select cells B2:B30

B Allow: Whole number

C Data: between

D Minimum: 14

E Maximum: 14

Question 5

A Select cells D2:D30

B Allow: Text length

C Data: between

D Minimum: 0

E Maximum: 100

Answer Key

Number range validation in a spreadsheet

Section A: Quick Questions

A — 13

A — Decimal between

B — It is rejected because it is below the minimum

B — Minimum: 11, Maximum: 14

A — False

Section B: Match the Pairs

Data > Data validation > Whole number between 11 and 14 → Only lets the cell accept whole numbers from 11 to 14, such as an age

Data > Data validation > Decimal between 0 and 100 → Lets the cell accept numbers with parts, like 72.5, from 0 to 100

Use this rule when a cell must hold only a whole number age from 11 to 14 → Whole number between 11 and 14

Use this rule when a cell must hold a height in metres, like 1.55, from 1 to 2 → Decimal between 1 and 2

Whole number between 14 and 11 → The minimum and maximum are the wrong way around. The minimum (11) must come first and the maximum (14) second.

Whole number between 1 and 100 (used for a test score like 85.5) → Whole number rejects values with decimal parts, so 85.5 would be refused. Decimal should be used instead.

Section C: Definitions

Data > Data validation > Whole number between — A rule that only lets a cell accept a number between a minimum and maximum, like age 11 to 14. Numbers outside the range are rejected. Use Decimal for numbers with parts.

Section D: Fill in the Blanks

Data > Data validation > Whole number between

Allow: Whole number
Data: between
Minimum: 11

Data: between
Minimum: 11
Maximum: 14

Allow: Decimal
Minimum: 1.2
Maximum: 2.0

Allow: Whole number
Minimum: 11
Maximum: 14

Section E: Spot the Error

D — 'not between' would reject ages 11 to 14 and accept everything else. It should be 'Data: between' so only numbers from the minimum to the maximum are allowed.

A — Age in years is a whole number, so the rule should be 'Allow: Whole number'. Decimal would let in values like 12.5.

B — Heights like 1.5 m have decimal parts. A Whole number rule would reject them, so the line should be 'Allow: Decimal'.

D — The minimum can't be the same as or bigger than the maximum, or no number could pass. It should be 'Minimum: 11'.

B — Text length checks how many characters are typed, not the size of a number. For test scores use 'Allow: Whole number'.