Making use of errors

OK, so logic says that you don’t want to have any errors in your spreadsheet & broadly speaking, that’s correct… however, there can be occasions when an “error” can be helpful.

As we saw in last month’s tip, the #N/A error in a VLOOKUP Function means that the item that you’re looking for can’t be found in the list that you’re checking. If you’re using Excel to help with a bank, supplier or other sort of reconciliation, then that can be actually quite useful as it shows the items that are in one list & not the other. We can then use an IF or IFERROR Function to decide what to do with the result; if you can’t remember, then check here: https://www.us4b.co.uk/2018/08/dealing-with-errors-in-lookups/

Error types

#NAME? error

The #NAME? error occurs when Excel does not recognise text in a formula.

  • Facebook
  • Twitter
  • Pinterest

Simply correct SU to SUM.

  • Facebook
  • Twitter
  • Pinterest

#VALUE! error

Excel displays the #VALUE! error when a formula has the wrong type of argument.

  • Facebook
  • Twitter
  • Pinterest

Change the value of cell A3 to a number or use a function to ignore cells that contain text.

  • Facebook
  • Twitter
  • Pinterest

#DIV/0! error

Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.

  • Facebook
  • Twitter
  • Pinterest

Change the value of cell A2 to a value that is not equal to 0 or prevent the error from being displayed by using the logical function IF.

  • Facebook
  • Twitter
  • Pinterest

Explanation: if cell A2 equals 0, an empty string (“”) is displayed. If not, the result of the formula A1/A2 is displayed.

#REF! error

Excel displays the #REF! error when a formula refers to a cell that is not valid.

For example, cell C1 references cell A1 and cell B1.

  • Facebook
  • Twitter
  • Pinterest

If column B is deleted (to achieve this, right click the column B header and click Delete.)

  • Facebook
  • Twitter
  • Pinterest

In the “new” cell B1 (which was C1 before we deleted column B) the reference to cell B1 is not valid anymore.

  • Facebook
  • Twitter
  • Pinterest

To fix this error, you can either delete +#REF! in the formula of cell B1 or you can undo your action by pressing CTRL + z

Using the error type in a formula

Here’s a short table with the “odd” calculation or two

  • Facebook
  • Twitter
  • Pinterest

As you can see, I’ve used the ERROR.TYPE Function to display which error is showing. We can then use this, perhaps with a VLOOKUP to generate an informative answer…

  • Facebook
  • Twitter
  • Pinterest

When is an error not an error?

Sometimes, when entering data (especially if it’s a date) into a cell, you’ll see ########

There are two reasons for this. The first is that the cell column is too narrow to display the value. The fix is simple: just expand the column to fit.

The second reason you could see ######## is if a date-formatted cell becomes negative. Usually this happens when dates or times are subtracted from each other (for example if you’re working out the difference between two dates. Excel will usually initially format the calculation cell to match the date formats; you’ll just have to change it back to something more useful.

Pin It on Pinterest