Blanks, missing values, & errors

Intermediate Google Sheets

Richie Cotton

Data Evangelist at DataCamp

	A	B	C
1	Value	Command	Result
2	1	=ISBLANK(A2)	FALSE
3	3	=ISBLANK(A3)	FALSE
4	6	=ISBLANK(A4)	FALSE
5		=ISBLANK(A5)	TRUE
6	15	=ISBLANK(A6)	FALSE
7		=ISBLANK(A7)	TRUE
8	28	=ISBLANK(A8)	FALSE
9	X	=COUNTBLANK(A2:A8)	2

Calculating with blanks: DANGER!

	A	B	C
1	Value	Command	Result
2	1	=A2 + 1	2
3	3	=A3 + 1	4
4	6	=A4 + 1	7
5		=A5 + 1	1
6	15	=A6 + 1	16
7		=A7 + 1	1
8	28	=A8 + 1	29

Calculating with blanks: make them missing

	A	B	C
1	Value	Command	Result
2	1	=IF(ISBLANK(A2), NA(), A2)	2
3	3	=IF(ISBLANK(A3), NA(), A3)	4
4	6	=IF(ISBLANK(A4), NA(), A4)	7
5		=IF(ISBLANK(A5), NA(), A5)	#N/A
6	15	=IF(ISBLANK(A6), NA(), A6)	16
7		=IF(ISBLANK(A7), NA(), A7)	#N/A
8	28	=IF(ISBLANK(A8), NA(), A8)	29

Errors

	A	B	C	D	E
1	Value	Command1	Result1	Command2	Result2
2		=ISERROR(A2)	FALSE	=ISERR(A2)	FALSE
3	#N/A	=ISERROR(A3)	TRUE	=ISERR(A3)	FALSE

Types of error

Error	Cause
#DIV/0!	Dividing by zero.
#VALUE!	Nonsense data in a calculation.
#REF!	Referencing a cell that has been deleted.
#NAME?	Forgetting to quote a string.
#NUM!	Numbers being out of range.
#N/A	Missing value.
#ERROR!	Syntax problem in a formula.

Summary

• Cells with nothing in are called "blank".
• Calculating with blank cells will give you the wrong answer.
• Instead, use NA() to create missing values.
• Missing values are a type of error.

Let's practice!

Intermediate Google Sheets