← VCE Data Analytics U3O1

Type checking in a spreadsheet

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.

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

Understanding Type checking in a spreadsheet in VCE Data Analytics U3O1

What it does: A type check for a number uses ISNUMBER on the cell, giving TRUE only when C5 holds a number. Ava is text, not a number, so ISNUMBER returns FALSE. ISTEXT returns TRUE for any text value, including names. SUM skips text cells in a range, so the total comes out too low. ISNUMBER helps you find the faulty cells.

Common mistakes: The closing bracket is missing, so the formula is incomplete. It should be =ISNUMBER(B4). Names are text, so the type check should use ISTEXT. ISNUMBER would return FALSE for every valid name. It should be =ISTEXT(A3).

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 Type 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 function checks that a cell holds a number?
ISNUMBER(B2)ISTEXT(B2)
Matches
=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.
Applying
Meanings
Term
ISNUMBER
Meaning
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.
Practice
=AND(ISNUMBER(B2),ISNUMBER(C2),ISNUMBER(D2))
Testing
Gaps
=(B2)
Check Answer
Spots
2E3: =ISNUMBER(B3)
3E4: =ISNUMBER(B4

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

The 'Ermine' badge — Tundra 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 function checks that a cell holds a number?
A. ISNUMBER(B2) ✓
B. ISTEXT(B2)
ISNUMBER returns TRUE when the cell holds a number. ISTEXT tests for text instead.
2. What does =ISNUMBER(A2) return if A2 contains the word Ava?
A. FALSE ✓
B. TRUE
Ava is text, not a number, so ISNUMBER returns FALSE.
3. What does =ISTEXT(A2) return if A2 contains the word Ava?
A. FALSE
B. TRUE ✓
ISTEXT returns TRUE for any text value, including names.
4. A type check tests which of these?
A. That a value is the expected data type ✓
B. That a value falls between a minimum and maximum
Testing a value against a minimum and maximum is a range check. A type check tests the data type, such as number or text.
5. A cell shows 85 but is stored as text, left-aligned. What does =ISNUMBER(B2) return?
A. TRUE
B. FALSE ✓
A number stored as text is still text. ISNUMBER returns FALSE, so the type check catches it.

Match the Pairs

=ISNUMBER(B2) → Returns TRUE if B2 holds a number and FALSE if it holds text or is empty
=ISTEXT(A2) → Returns TRUE if A2 holds text and FALSE if it holds a number
Check that a mark entered in cell C5 is a real number before it is used in calculations → =ISNUMBER(C5)
Check that a student name in cell A5 has been entered as text → =ISTEXT(A5)
=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.
=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.

Key Term

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.

Fill in the Blanks

1. =ISNUMBER(B2)
Hint: Type the function that tests whether a cell holds a number.
2. =ISTEXT(A2)
Hint: Type the function that tests whether a cell holds text.
3. =IF(ISNUMBER(B2),"OK","Not a number")
Hint: Type the function that chooses between two results based on the type check.
4. =NOT(ISNUMBER(B2))
Hint: Type the function that reverses TRUE and FALSE, so a number stored as text gives TRUE.
5. =IF(ISNUMBER(B2)=FALSE,"Fix","OK")
Hint: Type the Boolean value that the type check returns when a cell holds text.

Spot the Error

Question 1
A E2: =ISNUMBER(B2)
B E3: =ISNUMBER(B3)
C E4: =ISNUMBER(B4
Line C — The closing bracket is missing, so the formula is incomplete. It should be =ISNUMBER(B4).
Question 2
A Check names in column A are text
B F2: =ISTEXT(A2)
C F3: =ISNUMBER(A3)
Line C — Names are text, so the type check should use ISTEXT. ISNUMBER would return FALSE for every valid name. It should be =ISTEXT(A3).
Question 3
A Check Maths marks are numbers
B =IF(ISNUMBER(B2) "OK","Fix")
C Copy the formula down column E
Line B — A comma is missing after ISNUMBER(B2). The IF arguments must be separated by commas: =IF(ISNUMBER(B2),"OK","Fix").
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))
Line 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)).
Question 5
A B2 holds the text "75"
B =ISNUMBER(B2) gives TRUE
C So B2 is not yet a number
Line B — A number stored as text is still text, so ISNUMBER returns FALSE. The line should say =ISNUMBER(B2) gives FALSE.

Student with a class code? Join your class →