Spreadsheet Practice

Lesson 42 of 45, Errors & checks

Handling errors on purpose

Quick answer

Decide which errors to catch, and say what you want shown instead.

Key formula
=IFNA(VLOOKUP(A2,$F$2:$G$4,2,FALSE),"Not listed") (Catch only "not found")
Pro10 minutes, 3 exercisesIFNAIFERRORISNUMBER

Watch it done

2 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

An error is information. #N/A means a lookup found nothing. #DIV/0! means a divisor was zero or blank. #VALUE! means text is being used as a number. Hiding all of them the same way throws that information away.

IFNA(formula, text) catches only #N/A, the "not found" case. A typo in a range or a broken reference still shows up. IFERROR catches everything, so use it when any error genuinely has the same fix, such as a margin on zero revenue.

Sometimes the best guard is a test before the calculation: IF(ISNUMBER(C2), B2*C2, 0) only multiplies when there is a price to multiply. It makes the rule readable and never hides a surprise.

Syntax

=IFNA(VLOOKUP(A2,$F$2:$G$4,2,FALSE),"Not listed")Catch only "not found"
=IFERROR((B2-C2)/B2,"n/a")Any error becomes n/a
=IF(ISNUMBER(C2),B2*C2,0)Calculate only when it is a number
=ISERROR(D2)TRUE if D2 is any error

Watch out for

  • IFERROR can hide a wrong range or a deleted column and still look fine. Prefer IFNA for lookups.
  • Returning "" or text from a formula makes that cell text. Anything that does arithmetic on it will error again.
  • Show a message a colleague understands: "Not listed" beats 0 or a blank when it is a data problem.

The exercises

  1. Not listed. Fill C2:C7 with each SKU's price from the list in F2:G4. When a SKU is not in the list, show Not listed, and catch only that case.
  2. Guard the total. In D2, calculate quantity × unit price, but only when the price is a number. Otherwise show 0. Fill down to D7.
  3. Margin on zero revenue. In D2, calculate margin as (revenue − cost) ÷ revenue. Where revenue is 0 the division fails, so show n/a instead. Fill down to D7.

Questions people ask about this

Updated Checked by running: every exercise answer on this page was graded by our formula engine on .

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.

  1. Not listed
  2. Guard the total
  3. Margin on zero revenue