The `ERROR.TYPE` function in Excel is used to identify the type of error that occurs in a cell. It returns a number that corresponds to a specific error value. This is particularly useful when you want to customize error handling in your spreadsheet, such as displaying a specific message for different types of errors.
Syntax
ERROR.TYPE(error_val)
- `error_val`: The error value or reference to the cell containing the error you want to check.
Return Values
The function returns a number corresponding to the type of error:
- `#NULL!` error returns `1`
- `#DIV/0!` error returns `2`
- `#VALUE!` error returns `3`
- `#REF!` error returns `4`
- `#NAME?` error returns `5`
- `#NUM!` error returns `6`
- `#N/A` error returns `7`
- `#GETTING_DATA` error returns `8` (specific to Excel 2010 and later for data connections)
If there is no error, `ERROR.TYPE` will return `#N/A`.
Example
Suppose you have a formula in cell A1 that results in an error, like `=5/0` which gives `#DIV/0!`. You can use `ERROR.TYPE` to check the type of error:
=ERROR.TYPE(A1)
This will return `2`, indicating a `#DIV/0!` error.
Practical Use Case
If you want to display different messages based on error types, you can use `ERROR.TYPE` in combination with the `IF` function:
=IF(ISERROR(A1),
IF(ERROR.TYPE(A1) = 2, "Cannot divide by zero",
IF(ERROR.TYPE(A1) = 3, "Invalid value entered",
"Other Error")),
"No Error")
This formula checks if there’s an error in cell A1 and then displays a specific message based on the error type. If no error is present, it simply states “No Error”.
Unlock Your Potential
Excel
Access
Power BI
