Excel Practice
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

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.

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

In D2, work out the North route's cost per parcel: cost divided by parcels.

To begin, type it exactly:

=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

    In D2, work out the North route's cost per parcel: cost divided by parcels. To begin, type it exactly: =B2/C2.

    Hint. B2 over C2.

  2. 2

    D3 holds the same division for the South route, which carried no parcels. So it shows #DIV/0!, and the total below it will too. Wrap the division in IFERROR so the row shows 0 instead.

    Hint. The division goes inside IFERROR, and 0 after the comma.

  3. 3

    In D4, write the East route's cost per parcel with the same IFERROR wrapper, ready to fill down. It has parcels, so the division should come through untouched.

    Hint. Division inside, 0 after the comma.

  4. 4

    A zero looks like a free delivery. In D6, give the Central route an IFERROR whose fallback is the text No parcels. Then the sheet says what happened.

    Hint. Quotation marks around the two words.

  5. 5

    In B8, total the per-parcel column with SUM. Before the IFERRORs, one #DIV/0! in the column would have made this total an error too.

    Hint. D2 to D6.

  6. 6

    The trap. Somebody retyped the East row and mistyped the reference: =IFERROR(B4/C99,0). C99 is empty. What does D4 show?

    Hint. The error never reaches the screen.