Excel Practice
Lessons Lesson 53

Errors: Putting It Together

About this lesson

Every Excel error is one of three things. A mistake in the formula to fix. A fact about the data to trap with IFNA or IFERROR. Or a fault to flag with an IS function. The skill is telling which.

By the end you can

  • tell a formula mistake from a data fact and treat each one differently.
  • fix a #REF!, trap a #N/A and flag a blank on the same sheet.

Practises IFERROR IFNA ISNUMBER ISBLANK VLOOKUP SUM

The idea

One sheet, four faults, three responses. The #REF! in a lookup is the formula's fault. Fix the column number and never wrap it. The #N/A for a supplier who is not on the list is the data's fault. Trap it with IFNA and say Unknown. The number stored as text and the empty quantity produce no error at all, which is worse. The IS functions are how a formula notices them.

The habit to take away: when a cell shows an error, ask which of the three it is before you use IFERROR. Wrapping first and asking never is how sheets fill up with quiet zeros.

The mistake to watch for

Wrapping first and asking never. An error is one of three things. A mistake in the formula to fix. A fact about the data to trap. Or a fault that produces no error at all and has to be flagged. IFERROR treats all three the same. It turns a broken sheet into one that looks fine. Ask which it is before you reach for the wrapper.

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, the hinge line total: quantity times unit cost.

To begin, type it exactly:

=B2*C2

D2
Row A B C D E F G H I
1 Item Qty Unit cost Total Supplier Name Code Supplier
2 Hinge 40 1.2 S1 S1 Ironmonger
3 Bracket 25 3.4 S2 S2 Fixings Ltd
4 Bolt 0.15 S9 S3 BoltCo
5 Washer 500 0.02 S3
6
7 Total qty
8

Every step

  1. 1

    In D2, the hinge line total: quantity times unit cost. To begin, type it exactly: =B2*C2.

    Hint. B2 times C2.

  2. 2

    The bracket's quantity in B3 arrived as text. Read =B3*C3 and say what it shows.

    Hint. Treat the 25 as a number.

  3. 3

    In B7, total the quantity column with SUM. Then look at the answer.

    Hint. B2 to B5.

  4. 4

    F2 should show the hinge supplier's name from the list in H1:I4, and it shows #REF!. This is a mistake in the formula, not in the data. Fix it. Do not wrap it.

    Hint. How many columns does H2:I4 have?

  5. 5

    In F4, look up the bolt's supplier. S9 is not on the list, so wrap the lookup in IFNA with the fallback Unknown.

    Hint. IFNA, lookup inside.

  6. 6

    In D4, write a formula using IF and ISBLANK. Show Missing qty when the bolt's quantity in B4 is empty, and the line total otherwise.

    Hint. IF, with ISBLANK as the test.