Formula error values and how to fix them

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A formula divides by an empty cell. Which error value appears?

A #REF!

B #DIV/0!

2. =XLOOKUP("Zara",A2:A9,B2:B9) and Zara is not in the list. What does the cell show?

A #N/A

B #VALUE!

3. =B2+C2 where C2 contains the text "absent". Which error value is most likely?

A #DIV/0!

B #VALUE!

4. You delete a column that a formula refers to. Which error value appears?

A #REF!

B #N/A

5. Which statement about error values is true?

A An error value is a clue that helps you find what went wrong.

B An error value means the spreadsheet file is damaged and must be deleted.

Match the Pairs

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

Left column		Right column
1#DIV/0!		ACOUNTIF returns 0, so the formula divides by zero and shows #DIV/0!. Wrap it in IFERROR or check the divisor first.
2#N/A		BA lookup could not find the value it was searching for
3IFERROR shows a friendly result instead of an error value		CThe formula tried to divide by zero or by an empty cell
4#REF! appears when a formula refers to a cell that has been deleted		D=IFERROR(B2/C2,"Check data")
5=B2+C2 where C2 contains the text "n/a"		E=SUM(B2:B5) after deleting column B
6=AVERAGE(B2:B9)/COUNTIF(B2:B9,">100") when no value is over 100		FAdding a text value with + gives #VALUE! because the formula expects a number. Replace the text with a number or clean the data.

Definitions

error value

Fill in the Blanks

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

=_______(B2/C2,"Check C2")

=XLOOKUP("Zed",A2:A9,B2:B9) Zed is not in the list, so it shows ____

=B3*2 Row 3 was deleted, so it shows _____

=B2/C2 C2 holds 0, so the cell shows _______

=A2+B2 A2 holds text, so the cell shows _______

Spot the Error

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

Question 1

A Find a price by item name

B Items A2:A9, prices C2:C9

C F2 holds the item to find

D G2: =VLOOKUP(F2,A2:C9,4,FALSE)

Question 2

A =IFERROR(0,MATCH(A2,G:G,0))

B Shows 0 if A2 is not in G:G

C No #N/A will be displayed

Question 3

A B2: 40 C2: 0

B D2: =IFERROR(B2/C2,none)

C Cell shows "none", not #DIV/0!

Question 4

A B2: 88

B C2: n/a

C D2: =IF(ISTEXT(C2),B2+C2,0)

D Shows 0 instead of #VALUE!

Question 5

A E2: =C2*D2

B Delete column D

C E2 becomes =C2*#REF!

D Fix: add a check for zero

Answer Key

Formula error values and how to fix them

Section A: Quick Questions

B — #DIV/0!

A — #N/A

B — #VALUE!

A — #REF!

A — An error value is a clue that helps you find what went wrong.

Section B: Match the Pairs

#DIV/0! → The formula tried to divide by zero or by an empty cell

#N/A → A lookup could not find the value it was searching for

IFERROR shows a friendly result instead of an error value → =IFERROR(B2/C2,"Check data")

#REF! appears when a formula refers to a cell that has been deleted → =SUM(B2:B5) after deleting column B

=B2+C2 where C2 contains the text "n/a" → Adding a text value with + gives #VALUE! because the formula expects a number. Replace the text with a number or clean the data.

=AVERAGE(B2:B9)/COUNTIF(B2:B9,">100") when no value is over 100 → COUNTIF returns 0, so the formula divides by zero and shows #DIV/0!. Wrap it in IFERROR or check the divisor first.

Section C: Definitions

error value — A message starting with # (such as #DIV/0!, #N/A, #VALUE! or #REF!) that a cell shows when a formula fails. It hints at what went wrong so you can fix the formula or data.

Section D: Fill in the Blanks

=IFERROR(B2/C2,"Check C2")

=XLOOKUP("Zed",A2:A9,B2:B9)
Zed is not in the list, so it shows #N/A

=B3*2
Row 3 was deleted, so it shows #REF!

=B2/C2
C2 holds 0, so the cell shows #DIV/0!

=A2+B2
A2 holds text, so the cell shows #VALUE!

Section E: Spot the Error

D — The lookup range A2:C9 has only 3 columns, so column index 4 is outside it and the formula returns #REF!. The prices are in column 3 of the range, so the index should be 3.

A — IFERROR takes the formula to test first and the fallback value second. With the arguments swapped it always returns 0 and never shows the MATCH result. It should be =IFERROR(MATCH(A2,G:G,0),0).

B — Text used as the fallback value in IFERROR must be in double quotes. Without quotes, Excel reads none as a name it does not know and gives #NAME?. It should be "none".

C — C2 holds text, so ISTEXT(C2) is TRUE and B2+C2 is calculated, which gives #VALUE!. The check should be ISNUMBER(C2), so that only numbers are added.

D — #REF! means a referenced cell was deleted. Checking for zero fixes #DIV/0!, not #REF!. The fix is to undo the delete or re-enter the correct cell reference.