What Is ERROR.TYPE Function in Excel?
The “ERROR.TYPE” function in Excel is a powerful tool that helps determine the type of error present in a specific cell. This function returns a number that corresponds to a specific error type, such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, or #NAME? allowing users to identify and rectify errors within their data quickly. By utilizing this function, professionals can efficiently troubleshoot issues and correct errors in their worksheets, enhancing the overall accuracy and reliability of their analyses and reports.
The following example serves to illustrate the values, and we will proceed with calculations to determine the type of Excel Error.
The complete formula to be entered in cell B2 is =Error.Type(#REF!).
The resulting value in cell B2 will be 4, indicating the #REF! Error, as depicted in the image below.
Table of contents
Key Takeaways
- The purpose of the ERROR.TYPE function is to determine the type of error present in a specific cell.
- The function will produce a numerical value, that indicates the specific error encountered. It allows for easy error correction and troubleshooting.
- Each error in Excel has a specific numerical value which is the output of this function.
- With the various types of errors, this function simplifies the task of error handling and ultimately improves productivity for professionals who heavily rely on Excel for data analysis and calculations.
Syntax
Error_value – (required argument) This is the error value for which we seek to determine the identifying number. While error_val can represent the actual error value, it is typically a reference to a cell containing a formula that we intend to evaluate.
Errors and codes
| Errors | Codes |
|---|---|
| #NULL! | 1 |
| #DIV/0! | 2 |
| #VALUE! | 3 |
| #REF! | 4 |
| #NAME? | 5 |
| #NUM! | 6 |
| #N/A | 7 |
| #GETTING_DATA | 8 |
How To Use ERROR.TYPE Function in Excel?
How To Use ERROR.TYPE Function in Excel?
To effectively utilize the ERROR.TYPE function in Excel, follow these steps.
#1 – Access from the Excel ribbon
- Step 1: Choose the empty cell which will contain the result. Go to the “Formulas” tab and click it.
- Step 2: Select the “More Function” option from the menu.
- Step 3: Select the “Information” option from the drop-down list.
- Step 4: Select “ERROR.TYPE” from the drop-down menu.
- Step 5: A window called “Function Arguments” appears. Enter a value in the “error_val.” It can either be an actual error or a reference to a cell containing an error. Select OK.
#2 – Enter the worksheet manually
Step 1: Select an empty cell for the output.
Step 2: Type “=ERROR.TYPE()” in the selected cell. Alternatively, type “=E” and double-click the ERROR.TYPE function from the list of suggestions shown by Excel.
Examples
Example #1 – Identifying a #DIV/0! Error
The following example illustrates the values, and we will perform calculations to identify the type of Excel Error.
In the table provided, the data is displayed as follows:
- Column A contains the Values.
- Column B contains the Formula.
- Column C contains the Output.
- Column D contains the Error type.
To calculate the Error type, follow these steps:
Step 1: Select the cell where the formula will be entered, and the result will be calculated. In this case, we will choose cell D2.
Step 2: Enter the Formula in cell D2. The formula entered is =Error.Type(C2).
Select the cell where the formula will be entered, and the result will be calculated. In this case, let us choose cell C2.
Step 3: After entering each value in the previous step, press the “Enter” key. The resulting value in cell D2 will be the 2 for #DIV/0! Error, as shown in the image below.
Example #2 – Identifying a #NAME? error
Let’s dive into an example that will help us understand the values and determine the type of Excel Error through some calculations.
In the table provided, the data is neatly organized as follows:
- Column A is where we find the Formula.
- Column B is where the Output is located.
- Column C is where we can identify the Error type.
To figure out the Error type, follow these simple steps:
Step 1: Choose the cell where you want to enter the formula and calculate the result. For this example, let’s go with cell C2.
Step 2: Enter the Formula in cell C2.
The complete formula to be entered in cell C2 is =Error.Type(B2).
Step 3: Once you’ve entered the formula, hit the Enter key. The resulting value in cell C2 will be 5, indicating the #NAME? Error. You can see this clearly in the image below.
Example #3 – Using ERROR.TYPE with IFERROR
The following example is here to show you the values, and we will then do some calculations to figure out the type of Excel Error using the IFERROR excel function.
In the table we have, the data is organized like this:
- Column A has the Value 1.
- Column B has the Value 2.
- Column C has the Value.
- Column D has the Output.
We are currently calculating the sum of value 1 and value 2 in column C. However, due to the input, the result is displaying an error message of #VALUE! and 0. To further evaluate this issue, we will now utilize the Error.Type function in column D.
To find out the Error type, just follow these steps:
Step 1: Choose the cell where you want to enter the formula and get the result. Let’s go with cell D2.
Step 2: Enter the Formula in cell D2.
Step 3: The complete formula to enter in cell D2 is
=IFERROR(ERROR.TYPE(C2), “Missing value.”)
Step 4: After entering each value in the previous step, hit Enter. The value in cell D2 will be 3, which means there’s a #VALUE! Error, as you can see in the image below.
Example #4 – Using ERROR.TYPE function with IF function
The following example contains some values. We can find out the kind of Excel error we’re dealing with using the IF function.
In the table we have, we have two sets of values in two rows. Let us find the sum of these two values and if any error is present, their types as well.
We are currently calculating the sum of value 1 and value 2 in column C. However, because of an error in the formula input, the result is showing an error message of #NULL!
The next cell shows the #DIV/0! Error as it refers to an empty cell in the formula, where we have typed B4(empty cell) instead of B3.
To figure out the Error type, just follow these steps:
Step 1: Pick the cell where you want to enter the formula and get the result. Let’s say we choose cell D2. Enter the following formula in cell D2.
=IF(Error.Type(C2)=1, “Null value error”,C2).
Step 2: Hit the Enter key. The value you’ll see in cell D2 will be a Null value error, which means there’s a #NULL! Error, just like you can see in the image below.
Step 3: Similarly, you can use the formula:
=IF(Error.Type(C3)=3, “An #VALUE! error”,”Divide by Zero Error”)\
Since the error code for DIV/0! Error is 2, the string “Divide by Zero Error” is printed in cell D3.
Important Things To Note
- The ERROR.TYPE function supports logical formulas that further aid in identifying errors across multiple cells or ranges.
- When no error is detected, the function will return an #N/A error.
- The function can be effectively utilized in conjunction with the IF function to generate a customized text result.
Frequently Asked Questions (FAQs)
The purpose of the ERROR.TYPE function in Excel is to identify the type of error that has occurred within a specific cell. This function helps users pinpoint and troubleshoot errors in formulas or calculations. When applied to a cell, the function returns an integer that corresponds to a specific error type.
For example, if a cell contains #DIV/0! Error, ERROR.TYPE will return two since this error signifies division by zero. The following example serves to illustrate the values, and we will proceed with calculations to determine the type of Excel Error.
Select the cell B2 and find the square root of -3 using the formula =SQRT(-3). In cell B3, enter the f0ormula = ERROR.TYPE(B2). The resulting value in cell B2 will be 6, indicating the #NUM! Error, as depicted in the image below.
The ERROR.TYPE function in Excel does have some limitations and restrictions.
• Firstly, the ERROR.TYPE function can only identify errors that Excel itself generates.
• Additionally, this function works exclusively with error codes recognized by Excel, limiting its effectiveness if you are working with a different software or programming language.
• Moreover, the ERROR.TYPE function can only evaluate one cell at a time, which can be time-consuming.
• Lastly, it does not provide solutions or remedies to fix errors, thus making manual intervention necessary for error correction.
One can activate the ERROR.TYPE function in Excel using the following steps:
• Choose the empty cell which will contain the result.
• Go to the “Formulas” tab and click it.
• Select the “Information” group.
• Select “ERROR.TYPE” from the drop-down menu.
• A window called “Function Arguments” appears.
• As the number of arguments, enter the value in the “error_val.”
Select OK.
Download Template
This article must help understand the ERROR.TYPE Excel Function’s formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to ERROR.TYPE Function in Excel. Here we learn how to use ERROR.TYPE Function in excel with step by step examples and a downloadable template. You can learn more from the following articles –
Leave a Reply