Lessons Lesson 48

IFERROR

About this lesson

IFERROR runs a calculation and returns its result. If the result is an error, it returns the second argument instead.

By the end you can

  • trap a #DIV/0! with IFERROR and choose what shows instead.
  • say why one error in a range breaks every total that reads it.
  • decide what to wrap in IFERROR and what to leave bare.

Practises IFERROR IFNA ISERROR

By Ali, Founder, Excel Lessons

The idea

Two arguments: the thing to try, and what to show if it fails. On a good row, IFERROR is invisible. On a bad row, it swaps the error for your fallback. One error in a range turns every total that touches it into an error. So trapping errors where they start is what keeps the rest of the sheet working.

The mistake is wrapping everything. IFERROR hides every kind of error, including a mistyped reference or a lookup pointing at the wrong column. A sheet full of quiet zeros is harder to debug than one clear #REF!. Wrap the part that can fail for a good reason. A division that might meet a zero, or a lookup that might miss. Leave the rest bare.

The mistake to watch for

Wrapping everything. IFERROR hides every kind of error, including a mistyped reference and a lookup pointing at the wrong column. A sheet full of quiet zeros is far harder to debug than one clear #REF!. Wrap the part that can fail for a good reason. A division that may meet a zero, or a lookup that may miss. Leave the rest bare.

Read more about this

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

Cost per parcel is cost divided by parcels.

In D2, type:

=B2/C2

D2
Row A B C D E F G
1 Route Cost Parcels Per parcel
2 North 340 17
3 South 210 0
4 East 455 35
5 West 120 8
6 Central 0 0
7
8 Total per parcel

Every step

  1. 1

    Cost per parcel is cost divided by parcels. In D2, type: =B2/C2.

    Hint. B2 over C2.

  2. 2

    D3 shows #DIV/0! because C3 is 0. The total below it will show the error too. Fix D3. Wrap the division in IFERROR so the row shows 0.

    Hint. The division goes inside IFERROR, then a comma, then 0.

  3. 3

    In D4, write the same IFERROR for the East route. Shape: =IFERROR(value, value_if_error). East has parcels, so the division comes through untouched.

    Hint. Same shape as D3.

  4. 4

    A 0 looks like a free delivery. In D6, write the IFERROR with the fallback text No parcels. Remember: text goes in quotes, numbers do not.

    Hint. Quotation marks around the two words.

  5. 5

    In B8, total D2:D6 with SUM. One #DIV/0! in D2:D6 would have made this total an error too.

    Hint. D2 to D6.

  6. 6

    Somebody retyped D4 and mistyped the reference. C99 is empty. Read =IFERROR(B4/C99,0) and say what D4 shows.

    Hint. The error never reaches the screen.