Existence checking in a spreadsheet

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

Existence checking in a spreadsheet

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which function checks whether a single cell is empty?
A =ISBLANK(B2)
B =ISNUMBER(B2)
2. Cell B2 contains the number 75. What does =ISBLANK(B2) return?
A TRUE
B FALSE
3. Which formula counts how many empty cells are in B2:B9?
A =COUNTBLANK(B2:B9)
B =COUNT(B2:B9)
4. What is the purpose of an existence check?
A To make sure a value is within a set range
B To make sure a required value has been entered and is not missing
5. Which formula flags a missing mark in B2 by showing the word Missing?
A =IF(ISBLANK(B2),"Missing","OK")
B =IF(B2>0,"Missing","OK")
B

Match the Pairs

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

Left columnRight column
1=ISBLANK(B2) AISBLANK is meant to test one cell. Giving it a range does not return a single count of empty cells. Use COUNTBLANK(B2:B9) to count blanks in a range.
2=COUNTBLANK(B2:B9) B=IF(ISBLANK(C2),"Missing","OK")
3Flag a missing value by showing the word Missing when cell C2 is empty CThe results are the wrong way round. ISBLANK is TRUE when the cell is empty, so the TRUE result should be Missing and the FALSE result should be OK.
4Check that every cell in a range has been filled in by testing that the count of empty cells is zero DCounts how many cells in B2:B9 are empty
5=ISBLANK(B2:B9) EReturns TRUE if cell B2 is empty and FALSE if it contains data
6=IF(ISBLANK(B2),"OK","Missing") F=COUNTBLANK(B2:B9)=0
C

Definitions

ISBLANK
D

Fill in the Blanks

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

1.
=_______(B2)
2.
=__________(B2:B9)
3.
=__(ISBLANK(C2),"Missing","OK")
4.
=___(ISBLANK(B2))
5.
=IF(COUNTBLANK(B2:D2)_0,"Incomplete","OK")
E

Spot the Error

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

Question 1
A Goal: flag missing marks
B Marks are in B2:B9
C =IF(ISBLANK(B2),"OK","Gap")
Question 2
A Count missing marks in B2:B9
B Place the result in F1
C =COUNT(B2:B9)
Question 3
A Is the Name in A2 missing?
B ISBLANK(A2)
C TRUE means the cell is empty
Question 4
A Marks in B2:B9, some blank
B F1: =COUNTBLANK(B2:B9)
C F2: =COUNTA(B2:B9)
D F3: =F1-F2 gives all 8 cells
Question 5
A Marks are in B2:B9
B F1 counts the missing marks
C F1: =COUNTBLANK(B2:B9)
D F2: =IF(F1<0,"Fix data","OK")

Answer Key

Existence checking in a spreadsheet
Section A: Quick Questions
Q1
A — =ISBLANK(B2)
Q2
B — FALSE
Q3
A — =COUNTBLANK(B2:B9)
Q4
B — To make sure a required value has been entered and is not missing
Q5
A — =IF(ISBLANK(B2),"Missing","OK")
Section B: Match the Pairs
Q1
=ISBLANK(B2) → Returns TRUE if cell B2 is empty and FALSE if it contains data
Q2
=COUNTBLANK(B2:B9) → Counts how many cells in B2:B9 are empty
Q3
Flag a missing value by showing the word Missing when cell C2 is empty → =IF(ISBLANK(C2),"Missing","OK")
Q4
Check that every cell in a range has been filled in by testing that the count of empty cells is zero → =COUNTBLANK(B2:B9)=0
Q5
=ISBLANK(B2:B9) → ISBLANK is meant to test one cell. Giving it a range does not return a single count of empty cells. Use COUNTBLANK(B2:B9) to count blanks in a range.
Q6
=IF(ISBLANK(B2),"OK","Missing") → The results are the wrong way round. ISBLANK is TRUE when the cell is empty, so the TRUE result should be Missing and the FALSE result should be OK.
Section C: Definitions
Q1
ISBLANK — A spreadsheet function that checks a cell. It returns TRUE if the cell is empty and FALSE if it holds data, so it works as an existence check. COUNTBLANK counts empty cells in a range.
Section D: Fill in the Blanks
Q1
=ISBLANK(B2)
Q2
=COUNTBLANK(B2:B9)
Q3
=IF(ISBLANK(C2),"Missing","OK")
Q4
=NOT(ISBLANK(B2))
Q5
=IF(COUNTBLANK(B2:D2)>0,"Incomplete","OK")
Section E: Spot the Error
Q1
C — ISBLANK returns TRUE when the cell is empty, so the first result must be "Gap". The results are swapped. It should be =IF(ISBLANK(B2),"Gap","OK").
Q2
C — COUNT counts cells that hold numbers, so it ignores the blanks. To count empty cells use =COUNTBLANK(B2:B9).
Q3
B — A spreadsheet formula must start with =. Without it the cell shows text, not TRUE or FALSE. It should be =ISBLANK(A2).
Q4
D — Blank cells plus filled cells make up all the cells in the range, so you add them. It should be =F1+F2, not subtract.
Q5
D — COUNTBLANK can never return a number below 0, so F1<0 is never true and the flag would never show. The test should be F1>0.