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

By Ali, Founder, Excel Lessons

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.

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

Line total is quantity times unit cost.

In D2, type:

=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

    Line total is quantity times unit cost. In D2, type: =B2*C2.

    Hint. B2 times C2.

  2. 2

    B3 holds the bracket's quantity as text. Read =B3*C3 and say what it shows.

    Hint. Treat the 25 as a number.

  3. 3

    In B7, total B2:B5 with SUM. Then compare the answer with the quantities.

    Hint. B2 to B5.

  4. 4

    F2 shows #REF! instead of the hinge supplier's name. The mistake is in the formula, not the data. Fix F2. Remember: a mistake in the formula is fixed, not wrapped.

    Hint. How many columns does H2:I4 have?

  5. 5

    In F4, look up the supplier for E4 with IFNA around the lookup. S9 is not on the list, so show Unknown instead.

    Hint. Put the lookup inside IFNA.

  6. 6

    In D4, write an IF whose test is ISBLANK on B4. Shape: =IF(test, if_true, if_false). Show Missing qty when B4 is empty, and the line total otherwise.

    Hint. IF, with ISBLANK as the test.