Type checking in a spreadsheet
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which function checks that a cell holds a number?
2. What does =ISNUMBER(A2) return if A2 contains the word Ava?
3. What does =ISTEXT(A2) return if A2 contains the word Ava?
4. A type check tests which of these?
5. A cell shows 85 but is stored as text, left-aligned. What does =ISNUMBER(B2) return?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1=ISNUMBER(B2) | A=ISTEXT(A5) | |
| 2=ISTEXT(A2) | BA number typed inside quotation marks is stored as text, so ISNUMBER returns FALSE. Numbers stored as text fail the type check. | |
| 3Check that a mark entered in cell C5 is a real number before it is used in calculations | CReturns TRUE if B2 holds a number and FALSE if it holds text or is empty | |
| 4Check that a student name in cell A5 has been entered as text | DReturns TRUE if A2 holds text and FALSE if it holds a number | |
| 5=ISNUMBER("75") returns TRUE | E=ISNUMBER(C5) | |
| 6=ISTEXT(B2) to check that B2 holds a number | FISTEXT tests for text, not numbers. To check for a number the function should be ISNUMBER, so this type check tests the wrong data type. |
C
Definitions
ISNUMBER
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=________(B2)
2.
=______(A2)
3.
=__(ISNUMBER(B2),"OK","Not a number")
4.
=___(ISNUMBER(B2))
5.
=IF(ISNUMBER(B2)=_____,"Fix","OK")
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
E2: =ISNUMBER(B2)
B
E3: =ISNUMBER(B3)
C
E4: =ISNUMBER(B4
Question 2
A
Check names in column A are text
B
F2: =ISTEXT(A2)
C
F3: =ISNUMBER(A3)
Question 3
A
Check Maths marks are numbers
B
=IF(ISNUMBER(B2) "OK","Fix")
C
Copy the formula down column E
Question 4
A
Goal: count text values in B2:B9
B
Column B should only hold marks
C
Any text there is a bad entry
D
=SUMPRODUCT(--ISNUMBER(B2:B9))
Question 5
A
B2 holds the text "75"
B
=ISNUMBER(B2) gives TRUE
C
So B2 is not yet a number
Answer Key
Type checking in a spreadsheet
Section A: Quick Questions
Q1
A — ISNUMBER(B2)
Q2
A — FALSE
Q3
B — TRUE
Q4
A — That a value is the expected data type
Q5
B — FALSE
Section B: Match the Pairs
Q1
=ISNUMBER(B2) → Returns TRUE if B2 holds a number and FALSE if it holds text or is empty
Q2
=ISTEXT(A2) → Returns TRUE if A2 holds text and FALSE if it holds a number
Q3
Check that a mark entered in cell C5 is a real number before it is used in calculations → =ISNUMBER(C5)
Q4
Check that a student name in cell A5 has been entered as text → =ISTEXT(A5)
Q5
=ISNUMBER("75") returns TRUE → A number typed inside quotation marks is stored as text, so ISNUMBER returns FALSE. Numbers stored as text fail the type check.
Q6
=ISTEXT(B2) to check that B2 holds a number → ISTEXT tests for text, not numbers. To check for a number the function should be ISNUMBER, so this type check tests the wrong data type.
Section C: Definitions
Q1
ISNUMBER — A function that tests whether a cell value is a number. It returns TRUE for a number and FALSE for text, so it works as a type check. ISTEXT does the same job for text.Section D: Fill in the Blanks
Q1
=ISNUMBER(B2)Q2
=ISTEXT(A2)Q3
=IF(ISNUMBER(B2),"OK","Not a number")Q4
=NOT(ISNUMBER(B2))Q5
=IF(ISNUMBER(B2)=FALSE,"Fix","OK")Section E: Spot the Error
Q1
C — The closing bracket is missing, so the formula is incomplete. It should be =ISNUMBER(B4).
Q2
C — Names are text, so the type check should use ISTEXT. ISNUMBER would return FALSE for every valid name. It should be =ISTEXT(A3).
Q3
B — A comma is missing after ISNUMBER(B2). The IF arguments must be separated by commas: =IF(ISNUMBER(B2),"OK","Fix").
Q4
D — The goal is to count text values, so the check must be ISTEXT. ISNUMBER would count the valid marks instead. It should be =SUMPRODUCT(--ISTEXT(B2:B9)).
Q5
B — A number stored as text is still text, so ISNUMBER returns FALSE. The line should say =ISNUMBER(B2) gives FALSE.