The ERROR.TYPE formula with example in Excel

The ERROR.TYPE function in Excel is used to identify and return a number corresponding to a specific error type in a cell. This can be helpful for diagnosing issues in your formulas and for creating custom error messages or handling errors programmatically.

Syntax

excel
ERROR.TYPE(error_val)

• error_val: The error value you want to check. This can be a direct reference to a cell containing an error or a formula that results in an error.

Error Types and Their Corresponding Numbers

The ERROR.TYPE function returns the following numbers for different types of errors:

• #NULL! - 1
• #DIV/0! - 2
• #VALUE! - 3
• #REF! - 4
• #NAME? - 5
• #NUM! - 6
• #N/A - 7
• All other errors - #N/A

Example

Let’s go through an example to see how the ERROR.TYPE function works.

Example 1: Identifying an Error Type

Assume you have the following data in cells A1 and A2:

• A1: 5
• A2: 0

You have a division operation in cell B1 that results in an error:

1. In cell B1, enter the formula:

   excel
   =A1/A2
   

   Since division by zero is not possible, this results in a #DIV/0! error.

To identify this error type, use the ERROR.TYPE function:

2. In cell C1, enter the formula:

   excel
   =ERROR.TYPE(B1)
   

   This will output 2, indicating a #DIV/0! error.

Example 2: Using ERROR.TYPE to Create Custom Error Messages

Suppose you want to create a custom error message based on the error type. You can use the IF and ERROR.TYPE functions together:

1. In cell D1, enter the formula:

   excel
   =IF(ISERROR(B1), IF(ERROR.TYPE(B1)=2, "Division by zero error", "Other error"), "No error")
   

This formula checks if there is an error in cell B1:

• If there is a #DIV/0! error, it returns "Division by zero error".
• If there is any other error, it returns "Other error".
• If there is no error, it returns "No error".

Given that B1 contains a #DIV/0! error, cell D1 will display "Division by zero error".

Practical Use Cases

1. Error Handling in Large Sheets: Quickly identify and handle different types of errors in large datasets.
2. Custom Error Messages: Provide user-friendly error messages instead of default error codes.
3. Error Logging: Keep track of errors in complex formulas and take appropriate actions based on the type of error.

Summary

The ERROR.TYPE function is a useful tool for diagnosing and managing errors in Excel. By identifying the specific type of error, you can create more robust and user-friendly spreadsheets that handle errors gracefully and provide meaningful feedback to users.