ERROR.TYPE

In this comprehensive guide, we will explore the ERROR.TYPE formula in Microsoft Excel. The ERROR.TYPE function is a useful tool for identifying and handling different types of errors that may occur in your spreadsheet. By understanding the syntax, examples, tips and tricks, common mistakes, and related formulae, you will be able to effectively use the ERROR.TYPE function to improve your Excel experience.

ERROR.TYPE Syntax

The syntax for the ERROR.TYPE function is as follows:

ERROR.TYPE(error_val)

Where:

• error_val is the error value for which you want to identify the type. This can be a direct reference to a cell containing an error or a formula that results in an error.

The ERROR.TYPE function returns a number corresponding to the type of error encountered. The following are the error codes returned by the function:

1. #NULL!
2. #DIV/0!
3. #VALUE!
4. #REF!
5. #NAME?
6. #NUM!
7. #N/A
8. #GETTING_DATA (a seldom-used error code)

ERROR.TYPE Examples

Let’s look at some examples of how to use the ERROR.TYPE function in Excel.

Example 1: Identifying a #DIV/0! error

Suppose you have a formula in cell A1 that divides a number by zero, resulting in a #DIV/0! error. To identify the error type, you can use the following formula:

=ERROR.TYPE(A1)

This formula will return the number 2, indicating that the error is a #DIV/0! error.

Example 2: Identifying a #NAME? error

Imagine you have a formula in cell B1 that refers to a non-existent function, resulting in a #NAME? error. To identify the error type, you can use the following formula:

=ERROR.TYPE(B1)

This formula will return the number 5, indicating that the error is a #NAME? error.

Example 3: Using ERROR.TYPE with IFERROR

You can use the ERROR.TYPE function in combination with the IFERROR function to display a custom message based on the type of error encountered. For example, if you have a formula in cell C1 that results in an error, you can use the following formula to display a custom message:

=IFERROR(C1, “Error Type: ” & ERROR.TYPE(C1))

This formula will display the result of the formula in cell C1 if there is no error, and if there is an error, it will display a message indicating the error type.

ERROR.TYPE Tips & Tricks

Here are some tips and tricks to help you get the most out of the ERROR.TYPE function:

• Use the ERROR.TYPE function in combination with other error-handling functions like IFERROR, ISERROR, or IFNA to create more robust and informative error messages.
• Remember that the ERROR.TYPE function only works with error values. If the referenced cell or formula does not result in an error, the function will return a #N/A error.
• Use the VLOOKUP or INDEX/MATCH functions to create a lookup table that translates the error codes returned by ERROR.TYPE into more descriptive error messages.

Common Mistakes When Using ERROR.TYPE

Here are some common mistakes to avoid when using the ERROR.TYPE function:

• Not using the function with an error value: As mentioned earlier, the ERROR.TYPE function only works with error values. If the referenced cell or formula does not result in an error, the function will return a #N/A error.
• Using the function with an unsupported error type: The ERROR.TYPE function only supports the error types listed in the syntax section. If you encounter an unsupported error type, the function will return a #N/A error.

Why Isn’t My ERROR.TYPE Working?

If your ERROR.TYPE function is not working as expected, consider the following troubleshooting steps:

1. Ensure that the referenced cell or formula contains an error value. If it does not, the function will return a #N/A error.
2. Check that the error value is one of the supported error types listed in the syntax section. If it is not, the function will return a #N/A error.
3. Verify that your formula is correctly entered and that there are no typos or syntax errors.

ERROR.TYPE: Related Formulae

Here are some related formulae that can be used in conjunction with the ERROR.TYPE function to enhance your error-handling capabilities in Excel:

1. IFERROR: This function allows you to return a custom value or message if a formula results in an error. Example: =IFERROR(A1/B1, “Error occurred”)
2. ISERROR: This function checks if a value is an error and returns TRUE if it is, or FALSE if it is not. Example: =ISERROR(A1)
3. IFNA: This function returns a custom value or message if a formula results in an #N/A error. Example: =IFNA(VLOOKUP(A1, B1:C10, 2, FALSE), “Not found”)
4. ISERR: This function checks if a value is an error other than #N/A and returns TRUE if it is, or FALSE if it is not. Example: =ISERR(A1)
5. ISNA: This function checks if a value is an #N/A error and returns TRUE if it is, or FALSE if it is not. Example: =ISNA(A1)

By mastering the ERROR.TYPE function and its related formulae, you can create more robust and informative spreadsheets that handle errors effectively and provide valuable insights into the causes of those errors.