The ERROR.TYPE function in Excel is a diagnostic tool that returns a numerical code corresponding to a specific error value, or #N/A if no error is present. It serves as a crucial function for advanced error handling within spreadsheets.
What is the ERROR.TYPE Function?
As described by Microsoft Support, the ERROR.TYPE function "returns a number corresponding to one of the error values in Microsoft Excel or returns the #N/A error if no error exists." Its primary purpose is to identify the type of error in a cell programmatically, allowing for more specific and tailored responses to different error conditions.
Syntax
The syntax for the ERROR.TYPE function is straightforward:
ERROR.TYPE(error_val)
error_val: This is the error value you want to test. It can be a cell reference containing an error, a formula that evaluates to an error, or an error constant itself (e.g.,#DIV/0!).
Understanding Error Codes
When ERROR.TYPE encounters an error, it returns a unique numerical code. If the error_val argument does not contain an error, the function returns #N/A.
Here's a table of common Excel error types and their corresponding ERROR.TYPE values:
ERROR.TYPE Value |
Error Type | Description |
|---|---|---|
| 1 | #NULL! |
Intersection of two areas that do not intersect. |
| 2 | #DIV/0! |
Attempt to divide by zero. |
| 3 | #VALUE! |
Wrong type of argument or operand. |
| 4 | #REF! |
Invalid cell reference. |
| 5 | #NAME? |
Unrecognized name in a formula (e.g., misspelled function). |
| 6 | #NUM! |
Invalid numeric values in a function or formula. |
| 7 | #N/A |
Value not available (often from VLOOKUP when no match found). |
| 8 | #GETTING_DATA |
Indicates that an asynchronous function is waiting for data. |
Practical Applications of ERROR.TYPE
The true power of ERROR.TYPE comes when it's combined with other Excel functions, particularly the IF function. As mentioned in the Microsoft Support documentation, you can "use ERROR.TYPE in an IF function to test for an error value and return a text string, such as a message, instead of the error value."
Here are some key practical insights:
- Custom Error Messages: Instead of displaying Excel's default cryptic error messages, you can provide user-friendly explanations.
- Example: Suppose cell
B1contains the formula=A1/A2. IfA2is 0,B1will show#DIV/0!. To provide a more specific message:
=IF(ERROR.TYPE(B1)=2, "Cannot divide by zero! Please check cell A2.", B1)
This formula checks if the error inB1is a#DIV/0!error (type 2). If it is, it displays a custom message; otherwise, it shows the original content ofB1.
- Example: Suppose cell
- Conditional Logic Based on Error Type: You can perform different actions based on different error types.
- For instance, if it's a
#VALUE!error, you might prompt the user to enter a number, while for a#REF!error, you might suggest checking deleted cells.
- For instance, if it's a
- Error Logging and Analysis: In complex spreadsheets, you can use
ERROR.TYPEin a hidden column to log the specific type of error that occurred, which can be invaluable for debugging and maintaining your workbook. - Enhancing Data Validation: Combine it with
ISERRORfor robust data validation. WhileISERRORtells you if there's an error,ERROR.TYPEtells you what kind of error it is.
ERROR.TYPE vs. IFERROR
While ERROR.TYPE is powerful for specific error identification, Excel also offers the IFERROR function (introduced in Excel 2007). IFERROR is simpler for general error trapping: it allows you to specify a value to return if a formula results in any error, without needing to distinguish between error types.
IFERROR(value, value_if_error)
Choose ERROR.TYPE when you need to apply different logic or display different messages for different error types. Use IFERROR when you want a single, catch-all response for any error.
By leveraging ERROR.TYPE, you can transform potentially confusing Excel errors into clear, actionable feedback for users, making your spreadsheets more robust and user-friendly.
