IFERROR and IFNA in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of IFERROR with division by zero

What is the output of the formula =IFERROR(10/0, "Error") in Google Sheets?

Google Sheets

=IFERROR(10/0, "Error")

A Error

B #DIV/0!

C 0

D 10

💡 Hint

IFERROR returns the second value if the first causes an error.

The formula tries to divide 10 by 0, which causes a division error. IFERROR catches this and returns the second argument, "Error".

📊 Formula Result

intermediate

Output of IFNA with VLOOKUP missing value

Given a table where A2:A4 contains names and B2:B4 contains ages, what is the output of =IFNA(VLOOKUP("John", A2:B4, 2, FALSE), "Not found") if "John" is not in the list?

Google Sheets

=IFNA(VLOOKUP("John", A2:B4, 2, FALSE), "Not found")

A #REF!

B #N/A

C 0

D Not found

💡 Hint

IFNA returns the second value only if the first returns #N/A error.

VLOOKUP returns #N/A when it can't find "John". IFNA catches this and returns "Not found".

❓ Function Choice

advanced

Choosing between IFERROR and IFNA

You want to catch only #N/A errors from a formula but let other errors show normally. Which function should you use?

A ISNA

B IFNA

C ISERROR

D IFERROR

💡 Hint

IFNA only catches #N/A errors, IFERROR catches all errors.

IFNA catches only #N/A errors and leaves other errors visible. IFERROR catches all errors.

🎯 Scenario

advanced

Handling errors in nested formulas

You have a formula =A1/B1 that sometimes causes division errors. You want to show "Divide by zero" if B1 is zero, and "Error" for any other error. Which formula achieves this?

A =IFERROR(A1/B1, IF(B1=0, "Divide by zero", "Error"))

B =IFERROR(IF(B1=0, "Divide by zero", A1/B1), "Error")

C =IF(B1=0, "Divide by zero", IFERROR(A1/B1, "Error"))

D =IFNA(A1/B1, "Divide by zero")

💡 Hint

Check the condition for zero first, then handle other errors.

Option C first checks if B1 is zero and returns "Divide by zero". Otherwise, it tries A1/B1 and catches other errors with IFERROR returning "Error".

❓ data_analysis

expert

Counting errors handled by IFERROR

Given a column C2:C10 with formulas that may produce errors, which formula counts how many cells return an error that IFERROR would catch?

A =SUMPRODUCT(--ISERROR(C2:C10))

B =COUNTIF(C2:C10, "#N/A")

C =COUNTIF(C2:C10, "#DIV/0!")

D =SUMPRODUCT(--ISNA(C2:C10))

💡 Hint

IFERROR catches all errors, not just #N/A.

ISERROR detects all error types. SUMPRODUCT with double unary counts how many cells have errors. COUNTIF with specific error text counts only that error type.

Practice

1. What does the IFERROR function do in Google Sheets?

easy

A. It highlights cells that contain errors.

B. It only replaces #N/A errors with a custom value.

C. It counts the number of errors in a range.

D. It replaces any error in a formula with a custom value you choose.

Solution

1. Step 1: Understand the purpose of IFERROR

   The IFERROR function checks if a formula results in any error, such as #DIV/0!, #VALUE!, or #N/A.

2. Step 2: Know what IFERROR does with errors

   When an error is found, IFERROR replaces it with a value you specify, keeping your sheet clean and readable.

3. Final Answer:

   It replaces any error in a formula with a custom value you choose. -> Option D

4. Quick Check:

   IFERROR replaces all errors, not just #N/A [OK]

Hint: IFERROR handles all errors, IFNA only #N/A errors [OK]

Common Mistakes:

• Confusing IFERROR with IFNA
• Thinking IFERROR counts errors
• Assuming IFERROR highlights errors

2. Which of the following is the correct syntax for using IFNA in Google Sheets?

easy

A. =IFNA(value, value_if_na)

B. =IFNA(value, value_if_false)

C. =IFNA(value, value_if_blank)

D. =IFNA(value, value_if_error)

Solution

1. Step 1: Recall IFNA syntax

   The IFNA function syntax is =IFNA(value, value_if_na), where it returns value unless it is #N/A, then it returns value_if_na.

2. Step 2: Match the correct parameter name

   The second parameter must be the value to return if the error is specifically #N/A, so value_if_na is correct.

3. Final Answer:

   =IFNA(value, value_if_na) -> Option A

4. Quick Check:

   IFNA syntax uses value_if_na for #N/A errors [OK]

Hint: IFNA replaces only #N/A errors, syntax uses value_if_na [OK]

Common Mistakes:

• Using value_if_error instead of value_if_na
• Confusing IFNA with IFERROR syntax
• Using wrong parameter names

3. Given the formula =IFERROR(A1/B1, "Error") where A1=10 and B1=0, what will be the result?

medium

A. 0

B. #DIV/0!

C. "Error"

D. 10

Solution

1. Step 1: Calculate the division A1/B1

   Since B1 is 0, dividing 10 by 0 causes a #DIV/0! error.

2. Step 2: Apply IFERROR to handle the error

   IFERROR detects the error and returns the custom text "Error" instead of the error message.

3. Final Answer:

   "Error" -> Option C

4. Quick Check:

   Division by zero triggers IFERROR to return "Error" [OK]

Hint: IFERROR returns custom value on any error like division by zero [OK]

Common Mistakes:

• Expecting #DIV/0! to show instead of custom text
• Confusing IFERROR with IFNA behavior
• Thinking result is 0 or 10

4. You have the formula =IFNA(VLOOKUP("Apple", A2:B5, 2, FALSE), "Not found") but it always returns "Not found" even when "Apple" is in the list. What is the likely problem?

medium

A. IFNA syntax is wrong; it should be IFERROR.

B. The VLOOKUP range is incorrect or missing the lookup value.

C. The formula should use TRUE for approximate match instead of FALSE.

D. The lookup value "Apple" is misspelled in the formula.

Solution

1. Step 1: Check the VLOOKUP range and lookup value

   If "Apple" is in the list but still returns "Not found", the range A2:B5 might not include the cell containing "Apple" or the lookup value is not exactly matching.

2. Step 2: Understand IFNA behavior

   IFNA only replaces #N/A errors, so if VLOOKUP can't find "Apple" in the specified range, it returns #N/A, triggering IFNA to show "Not found".

3. Final Answer:

   The VLOOKUP range is incorrect or missing the lookup value. -> Option B

4. Quick Check:

   Incorrect range causes #N/A triggering IFNA [OK]

Hint: Check VLOOKUP range includes lookup value to avoid #N/A [OK]

Common Mistakes:

• Using IFERROR instead of IFNA incorrectly
• Using approximate match (TRUE) when exact needed
• Misspelling lookup value in formula

5. You want to create a formula that divides A1 by B1, but if B1 is zero or empty, it should show "Invalid". Also, if the division results in #N/A, it should show "Missing data". Which formula correctly does this?

hard

A. =IF(B1=0, "Invalid", IFNA(A1/B1, "Missing data"))

B. =IFNA(IF(B1=0, "Invalid", A1/B1), "Missing data")

C. =IFERROR(IF(B1=0, "Invalid", A1/B1), "Missing data")

D. =IF(B1=0, "Invalid", IFERROR(A1/B1, "Missing data"))

Solution

1. Step 1: Handle zero or empty divisor first

   Use IF(B1=0, "Invalid", ...) to check if B1 is zero or empty and return "Invalid" immediately.

2. Step 2: Use IFNA to catch #N/A errors in division

   Wrap the division A1/B1 inside IFNA(..., "Missing data") to replace only #N/A errors with "Missing data".

3. Step 3: Combine both checks correctly

   The formula =IF(B1=0, "Invalid", IFNA(A1/B1, "Missing data")) first checks divisor, then handles #N/A errors properly.

4. Final Answer:

   =IF(B1=0, "Invalid", IFNA(A1/B1, "Missing data")) -> Option A

5. Quick Check:

   Check zero first, then IFNA for #N/A errors [OK]

Hint: Check zero divisor first, then use IFNA for #N/A errors [OK]

Common Mistakes:

• Using IFERROR instead of IFNA for #N/A errors
• Not checking zero divisor before division
• Swapping IFNA and IFERROR order incorrectly