ERROR.TYPE Function in Excel – Error Codes,Examples

No matter how much of an expert we become, there are errors or bugs in every field we are working in. While using Formulas and Functions in Microsoft Excel, we end up facing errors as formula results. ERROR.TYPE Function in excel is used when we are having errors or we want to check for any error.

Let us see how the function works!

When to use ERROR.TYPE Function in Excel?

The ERROR.TYPE Function in excel is particularly used to check the type of error resulting from any function or formula in excel. The function detects the type of error and returns the corresponding error code for that type of error.

Here we have the following list of errors and the corresponding ERROR.TYPE Function results when we pass that error as the ERROR.TYPE function argument.

error =ERROR.TYPE(error) //result
#NULL!1
#DIV/0!2
#VALUE!3
#REF!4
#NAME?5
#NUM!6
#N/A!7
#GETTING_DATA!8
#SPILL!9
#CALC!14

Syntax and Arguments

=ERROR.TYPE(error_val)

The ERROR.TYPE requires to be passed only one following function argument.

  • error_val – This is the reference to a cell. The cell reference would contain a formula for which we want to check the type of error returned by the formula.
ERROR.TYPE Function infographics excel

Points to Consider About ERROR.TYPE Function

The following points about ERROR.TYPE Function summarizes the complete usage of the function.

  • The ERROR.TYPE function checks the presence and type of error.
  • ERROR.TYPE Function returns the number code for the error if it exists.
  • However, the Function returns a #N/A! error if there is no error.
  • We can use the function to customize the error messages for different types of errors.

Examples to Learn the ERROR.TYPE Formula

In this section of the blog, we would perform some practical experimentation with the ERROR.TYPE Formula.

Example 1 – Basic usage of ERROR.TYPE Function

Let us suppose we have used the following formulas in the range A2:A9. The range B2:B9 is where we have applied those formulas and got the Formula Result in Column B.

usage of ERROR.TYPE Function in Excel

We would use the following formula in cell C2 to get the corresponding error code for the error returned by the formula in cell B2.

=ERROR.TYPE(B2)

Select the range B2:B9 and press the Ctrl D key to copy the formula for the remaining cells.

usage of ERROR.TYPE Function in Excel result

As a result, the formula returns the corresponding error codes.

Explanation – We have passed the error_val argument as cell B2. B2 contains the formula result. When B2 is passed as the ERROR.TYPE input, the function returns the corresponding error code for #NULL! error as 1. This works the same for the range B3:B8.

In the last formula, no error is returned by the formula =SUM(2,2). When a non-error value is passed, the ERROR.TYPE Function returned a #N/A! in cell C9.

Example 2 – Customizing Error Messages for Different Errors

Let us suppose we are using the SQRT function to get the square root of the number in cell B1. Moreover, cell B3 contains the result.

example of ERROR.TYPE Funcrion

When we enter a negative value or a text string in cell B1, the formula in cell B3 returns a #NUM! error or a #VALUE! error respectively.

example of ERROR.TYPE Funcrion part 2
example of ERROR.TYPE Funcrion part 3

In order to understand these errors’ meaning, we can add the custom error message box.

Use the following formula in the message box.

=IFERROR(B3,IF(ERROR.TYPE(B3)=6,"Do not Enter Negative Number",IF(ERROR.TYPE(B3)=3,"Enter a Numerical Value")))

Here we have used the IF and IFERROR functions in Excel.

Firstly, we would check if B3 returns an error by the IFERROR formula. Cell B3 is returned if there is no error. In case of error, there are two conditions

  • We will start by checking for the #NUM! error ( code=6 ). However, It returns “Do not Enter Negative Number” if a #NUM! error is returned.
  • Otherwise, another condition is that if B3 returns a #VALUE! error ( code =3 ) is checked. Consequently, It returns “Enter a Numerical Value” if a ##VALUE!! error is returned.
example of ERROR.TYPE Funcrion part 4

Consequently, this brings us to the end of the blog.

Thanks for reading.

Leave a Comment