The IFERROR function

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

The IFERROR function

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which formula shows 0 instead of an error when C2 is 0?
A =IFERROR(B2/C2)
B =IFERROR(B2/C2,0)
2. True or false: IFERROR shows your chosen value only when the formula gives an error.
A False
B True
3. Which formula shows "Not found" when a name is missing from a lookup?
A =IFERROR(XLOOKUP(A2,F:F,G:G),"Not found")
B =XLOOKUP(A2,F:F,G:G,"Not found",IFERROR)
4. What does =IFERROR(10/2,0) show?
A 5
B 0
5. A formula shows #REF! because a column was deleted by mistake. Which is the better fix?
A Find out why the error happens and repair the formula
B Wrap it in IFERROR so the error disappears
B

Match the Pairs

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

Left columnRight column
1=IFERROR(B2/C2,0) ALooks up A2 and shows the words Not found if no match exists
2=IFERROR(XLOOKUP(A2,F:F,G:G),"Not found") BIFERROR hides real mistakes. Wrapping a formula to cover up a wrong range means the problem is never found or fixed.
3IFERROR takes the formula to try first, then the value to show if that formula gives an error CDivides B2 by C2 and shows 0 if the division gives an error, such as dividing by zero
4Show a blank cell instead of an error when a percentage formula fails D=IFERROR(B2/C2,"")
5=IFERROR(B2/C2) E=IFERROR(value, value_if_error)
6=IFERROR(SUM(B2:B9),0) to hide a wrong cell range that points at the wrong column FThe value_if_error argument is missing. IFERROR needs two arguments: the formula and what to show if it fails.
C

Definitions

=IFERROR(value, value_if_error)
D

Fill in the Blanks

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

1.
=_______(B2/C2,0)
2.
=IFERROR(B2/C2,_)
3.
=IFERROR(_______(E2,A2:A9,B2:B9),"Not found")
4.
=IFERROR(D2_E2,0)
5.
=IFERROR(_____,0)
E

Spot the Error

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

Question 1
A =IFERROR(B2/C2,0)
B =IFERROR(B2/C2)
C =IFERROR(B4/C4,0)
Question 2
A Price per item in column D
B D2: =IFERROR(B2/C2,0)
C D3: =IFERROR(B3/C3,0)
D D4: =IFERROR(B4/C4,0
Question 3
A D2: =IFERROR(C2,Not found)
B D3: =IFERROR(C3,"Not found")
C D4: =IFERROR(C4,"Not found")
Question 4
A Average mark, no errors shown:
B =IFERROR(0,AVERAGE(B2:B9))
C Empty range gives 0, no error
Question 5
A Total in B2, students in C2
B D2: IFERROR(B2/C2,0)
C D2 shows 0 if C2 is empty

Answer Key

The IFERROR function
Section A: Quick Questions
Q1
B — =IFERROR(B2/C2,0)
Q2
B — True
Q3
A — =IFERROR(XLOOKUP(A2,F:F,G:G),"Not found")
Q4
A — 5
Q5
A — Find out why the error happens and repair the formula
Section B: Match the Pairs
Q1
=IFERROR(B2/C2,0) → Divides B2 by C2 and shows 0 if the division gives an error, such as dividing by zero
Q2
=IFERROR(XLOOKUP(A2,F:F,G:G),"Not found") → Looks up A2 and shows the words Not found if no match exists
Q3
IFERROR takes the formula to try first, then the value to show if that formula gives an error → =IFERROR(value, value_if_error)
Q4
Show a blank cell instead of an error when a percentage formula fails → =IFERROR(B2/C2,"")
Q5
=IFERROR(B2/C2) → The value_if_error argument is missing. IFERROR needs two arguments: the formula and what to show if it fails.
Q6
=IFERROR(SUM(B2:B9),0) to hide a wrong cell range that points at the wrong column → IFERROR hides real mistakes. Wrapping a formula to cover up a wrong range means the problem is never found or fixed.
Section C: Definitions
Q1
=IFERROR(value, value_if_error) — Runs a formula and shows your chosen value, like 0 or "Not found", if it gives an error. Use it carefully, because it can also hide real mistakes.
Section D: Fill in the Blanks
Q1
=IFERROR(B2/C2,0)
Q2
=IFERROR(B2/C2,0)
Q3
=IFERROR(XLOOKUP(E2,A2:A9,B2:B9),"Not found")
Q4
=IFERROR(D2/E2,0)
Q5
=IFERROR(B2/C2,0)
Section E: Spot the Error
Q1
B — IFERROR needs two arguments: the formula and the value to show if it fails. The value_if_error is missing. It should be =IFERROR(B2/C2,0).
Q2
D — The closing bracket is missing, so the function is not finished. It should be D4: =IFERROR(B4/C4,0).
Q3
A — Text in a formula must be inside double quotes. Without them Not found is read as a name and causes an error. It should be =IFERROR(C2,"Not found").
Q4
B — The arguments are the wrong way round. The formula to test comes first and the replacement value comes second. It should be =IFERROR(AVERAGE(B2:B9),0).
Q5
B — Every formula must start with =. Without it the cell shows plain text instead of calculating. It should be D2: =IFERROR(B2/C2,0).