30 Excel Functions in 30 Days: 17 – ERROR.TYPE

Icon30DayYesterday, in the 30XL30D challenge, we found values with the LOOKUP function, and we’ll use that function again today, when working with error results.

For day 17 in the challenge, we’ll examine the ERROR.TYPE function. It can identify specific types of errors, and you can use that information to help with troubleshooting.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

So, let’s take a look at the ERROR.TYPE information and examples, and if you have other tips or examples, please share them in the comments.

Function 17: ERROR.TYPE

The ERROR.TYPE function identifies an error type by number, or returns #N/A if no error is found.

ErrorType00

How Could You Use ERROR.TYPE?

With the ERROR.TYPE function, you can:

  • identify an error type
  • help users troubleshoot error results

ERROR.TYPE Syntax

The ERROR.TYPE function has the following syntax:

  • ERROR.TYPE(error_val)
    • error_val is the error that you want to identify
    • ERROR.TYPE codes:
      • 1…..#NULL!
      • 2…..#DIV/0!
      • 3…..#VALUE!
      • 4…..#REF!
      • 5…..#NAME?
      • 6…..#NUM!
      • 7…..#N/A
      • #N/A..Other

ERROR.TYPE Traps

If the error_val is not an error, the result of the ERROR.TYPE function is an #N/A error. You can avoid this, by using ISERROR to test for an error, as shown in Example 2.

Example 1: Identify the Error Type

With the ERROR.TYPE function you can check a cell, to identify which error it contains. If there isn’t an error in the cell, the result is #N/A, instead of an error type code number.

=ERROR.TYPE(B3)

ErrorType01a

In this example, cell B3 contains #VALUE!, so the error type is 3.

ErrorType01b

Example 2: Help Users Troubleshoot Errors

By combining the ERROR.TYPE function with other functions, you can help users troubleshoot error results in a cell. In this example, numbers should be entered in cells B3 and C3. If text is entered, the result in D3 is a #VALUE! error. If a zero is entered in cell C3, the result is a #DIV/0! error.

In cell D4, ISERROR checks for an error, and the ERROR.TYPE function returns a number for the error. The LOOKUP function finds the applicable troubleshooting message from a table of error type codes, and displays it.

=IF(ISERROR(D3), LOOKUP(ERROR.TYPE(D3),$B$9:$B$15,$D$9:$D$15),””)

ErrorType02a

Here’s the lookup table, with Error Type numbers, and messages.

ErrorType02b

Download the ERROR.TYPE Function File

To see the formulas used in today’s examples, you can download the ERROR.TYPE function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the ERROR.TYPE Video

To see a demonstration of the examples in the ERROR.TYPE function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Troubleshoot Excel Errors with ERROR.TYPE Function

_____________

You may also like...

1 Response

  1. Luke Wisbey says:

    This can be a useful technique in determining whether or not a given cell resides within a Named Range, eg:

    =ISNA(ERROR.TYPE(_test A1))

    where _test is a named range and which may be non-contiguous

    (the above is susceptible to error pending existence of underlying errors within A1)

Leave a Reply

Your email address will not be published. Required fields are marked *