What Is ERROR.TYPE Function In Google Sheets?
The ERROR.TYPE function in Google Sheets is a built-in Info function. It takes an error value as the input. It then returns a numeric code corresponding to the input value as the error type. However, when the input value is not an error value or an error value that is not of a supported type, the function output is the #N/A error.
Users can use the ERROR.TYPE() in Google Sheets for identifying error types, error handling, and error reporting, when working with financial data.
For example, we have two sets of values.
We must perform the actions according to the cited descriptions using the corresponding input values and display the output in column D. However, the results in column D are errors.
The aim is to find the numeric code corresponding to each error value in column D and display the required error codes in column F.
Then, we can apply the ERROR.TYPE() in the target cells, considering the meaning of the ERROR.TYPE function in Google Sheets explained previously.
The ERROR.TYPE() in every column F target cell takes the reference to the error value in the respective column D’s row as the input. Then, the ERROR.TYPE function in Google Sheets returns the numeric code corresponding to the cited error value as the required error code.
Table of contents
Key Takeaways
- The ERROR.TYPE function in Google Sheets accepts an input value. It checks if the input value is an error value to return its corresponding index as the error type.
- Users can use the ERROR.TYPE Google Sheets function to determine the type of the error value an expression returns. So, the function helps in error handling.
- The ERROR.TYPE Google Sheets function takes one mandatory argument value, reference.
- We can utilize the ERROR.TYPE Google Sheets function as an individual function. However, implementing it with other inbuilt functions, such as IF and IFERROR, yields practical outcomes.
Syntax
The syntax to adhere to while using ERROR.TYPE function in Google Sheets is the following:
Where,
- reference: The value for which we want to find the error number or code. While the argument value can be a reference, we can also supply the concerned error value, without double quotations, directly to the function as the input. However, if the directly supplied error value is within double quotes, the function considers the input as a text and returns the #N/A error value.
Please ensure to provide the mandatory argument value mentioned above when using ERROR.TYPE function in Google Sheets.
Errors And Codes
The table below shows the errors we often get in Google Sheets and their codes, which we the ERROR.TYPE() returns as the output.
| Error Value | ERROR.TYPE() Output |
|---|---|
| #NULL! | 1 |
| #DIV/0! | 2 |
| #VALUE! | 3 |
| #REF! | 4 |
| #NAME? | 5 |
| #NUM! | 6 |
| #N/A | 7 |
| Other errors | 8 |
How To Use ERROR.TYPE Function In Google Sheets?
We can apply the Google Sheets function ERROR.TYPE in the following ways:
- Access the function from the ribbon.
- Enter the function into the sheet manually.
Method #1 – Access The Function From The Ribbon
Choose a target cell for showcasing the output – The Insert tab – The Function option right arrow – The Info function group right arrow – The ERROR.TYPE function.
The function ERROR.TYPE will display in the target cell, with the cursor inside the function brackets. We can now update the ERROR.TYPE function in Google Sheets argument within the brackets.
Also, we can click the ‘?’ symbol before the function name to check the function syntax.
Next, click the down arrow in the syntax pane to view the meaning of the ERROR.TYPE function in Google Sheets explained with a simple example.
Finally, press Enter to secure the output the ERROR.TYPE function in Google Sheets returns.
Method #2 – Enter The Function Into The Sheet Manually
- Choose the target cell where we want to display the result.
- Type =ERROR.TYPE( in the cell. [ Alternatively, type =E or =ER and click the function name ERROR.TYPE in the listed suggestions to select the function.]
- Supply the argument value and close the brackets.
- Press Enter to secure the ERROR.TYPE function return value in Google Sheets.
Examples
The illustrations below describe the practical uses of the function ERROR.TYPE in Google Sheets.
Example #1 – Identifying A #DIV/0! Error
The source dataset shows the total inventory costs and the corresponding inventory levels in cartons.
We must find the cost per carton for each pair of total inventory cost and inventory level and display the output in column C. However, if the determined cost per carton value is an error value, the output must show the error type index instead of the error value.
Step 1: Select cell C2. Next, enter the ERROR.TYPE().
=ERROR.TYPE(
[Alternatively, select the required cell and then Insert – Function – Info – ERROR.TYPE function.
The ERROR.TYPE() will show in the chosen cell, as depicted above.]
Next, enter the ERROR.TYPE function in Google Sheets argument inside the brackets according to the function syntax.
=ERROR.TYPE(A2/B2)
Step 2: Press Enter to view the function return value.
Step 3: Use the option fill handle, that works like the Excel fill handle, to implement the formula in the remaining target cells.
The expression determining the cost per carton value returns the #DIV/0! error value, which is the ERROR.TYPE()’s argument value. So, the function returns the #DIV/0! error index, 2, in each case, helping us in identifying the #DIV/0! error value. Please note that this error is the same as the #DIV/0! Excel error.
Example #2 – Identifying A #NAME? Error
The source dataset shows the monthly sales data of a list of sales representatives at a firm.
Assume we use the Google Sheets function INDEX MATCH and AVERAGE(), similar to Excel INDEX MATCH function and Excel AVERAGE function, to determine the February month sales figure and the average sales value of the representative cited in cell F2, in cells G2 and G4.
Step 1: Select cell G2, enter the INDEX-MATCH(), and press Enter.
Step 2: Select cell G4, enter the AVERAGE(), and press Enter.
Though we used the applicable functions, the output in both cases is the #NAME? error. The reason in the first case is that the MATCH() name is misspelled, and in the second scenario, Google Sheets did not recognize the function inputs.
So, we shall now see the steps to identify the #NAME? error value and show its code in cells H2 and H4.
Step 3: Choose cell H2. Next, enter the ERROR.TYPE function and press Enter.
=ERROR.TYPE(G2)
Step 4: Choose cell H4. Enter the ERROR.TYPE function and press Enter.
=ERROR.TYPE(G4)
So, we provide the reference to the cells G2 and G4, containing the INDEX-MATCH and AVERAGE function, as the inputs to the ERROR.TYPE() in the target cells H2 and H4. The function then returns the corresponding error code, 5, in each target cell, which helps in identifying the #NAME? error in the cited cells.
Example #3 – Using ERROR.TYPE With IFERROR
The source dataset lists a set of teams, their total working days and the tickets they closed each day.
The aim is to determine the total tickets each team closed and showcase the output in the column D cells.
Step 1: Choose cell D2, enter the PRODUCT(), and press Enter.
=PRDUCT(B2:C2)
Step 2: Choose cell D3, enter the PRODUCT(), and press Enter.
=PRODUCT(B3 C3)
Step 3: Choose cell D4, enter the following expression, and press Enter.
=B4^C4
The output in each target cell is an error value. In the case of the cell D2 formula, the function name appears misspelled. In cell D3, the argument supplied to the PRODUCT(), that works like the Excel PRODUCT function, is not valid. In contrast, the formula in cell D4 uses an incorrect operator instead of the ‘*’ operator. Furthermore, when we use the ‘^‘ operator with the input values, the formula returns an error. The reason is that the resulting number is massive, which Google Sheets cannot manage.
We can show the error codes as valid comments to handle the error values using the ERROR.TYPE()-based IFERROR in Google Sheets, as explained below. Assume cells E2:E4 are the target cells.
Step 4: Choose cell E2. Next, enter the ERROR.TYPE()-based IFERROR(), and press Enter.
=IFERROR(D2,”Error Type: “&ERROR.TYPE(D2))
Next, utilize the option of fill handle to populate the remaining target cells with the ERROR.TYPE()-based IFERROR() formula.
Firstly, the IFERROR(), that works like the Excel IFERROR function, tests whether the reference to the cell, provided as the first argument value, holds an error value. Since the cell referred holds an error value, the ERROR.TYPE() accepts the same reference to return the corresponding error index.
Next, the formula combines the provided text with the ERROR.TYPE() output value to secure the desired comment on the error type as the IFERROR() output in every target cell.
Example #4 – Using ERROR.TYPE Function With IF Function
The source dataset shows a tech company’s details input in column B.
The aim is to display the valid details in column C. However, the output must show the comment “Data not available” in a column C cell if the corresponding cell in column B holds the #N/A error value.
Then, we can apply the ERROR.TYPE() with the IF() and ISNA() in each target cell to secure the desired result. Please note that the IF() and ISNA() in Google Sheets work like the Excel IF function and ISNA Excel function.
Step 1: Select cell C2, enter the following formula, and press Enter.
=IF(ISNA(ERROR.TYPE(B2)),B2,IF(ERROR.TYPE(B2)=7,”Data not available”,””))
Step 2: Utilizing the fill handle, implement the formula in the remaining target cells.
Let us check the cell C11 formula to determine the logic.
Firstly, the outer IF() condition gets checked. So, the ERROR.TYPE function in the ISNA() takes the reference to cell B11, holding the input data, the #N/A error value, and returns the corresponding error code, 7.
Next, the ISNA() accepts the ERROR.TYPE() return value, 7, as its input. Now, the ISNA() argument value is not the #N/A error value. So, the function returns FALSE as the output.
So, the FALSE value of the outer IF() formula, which is again an IF(), gets implemented.
Next, the ERROR.TYPE function gets executed as the inner IF() condition. The function accepts the reference to cell B11, containing the #N/A error value, and returns the corresponding error code, 7.
So, the ERROR.TYPE() output of 7 is equal to the cited value of 7. Thus, the IF condition is true.
So, the IF() output is the TRUE value. Thus, it returns the comment “Data not available” as the nested IF formula final output.
Thus, we can use the IF() with the ERROR.TYPE() to show customized comments in a way to identify and manage error values.
Important Things To Note
- The error value or the expression returning an error value, we supply as the argument value to the ERROR.TYPE function in Google Sheets, should be supported by the function. Otherwise, the function output value will be the error #N/A.
- The ERROR.TYPE Google Sheets function can return error codes 1-8 according to the input error value or the expression returning an error value.
Frequently Asked Question (FAQs)
We can use ERROR.TYPE function with IF & LOOKUP functions in Google Sheets, as described below with an illustration.
The source dataset lists two sets of data values. We use them to perform the actions mentioned in the descriptions and display the output in column D. Moreover, column E shows formulas applied in the column D cells.
However, the output values in the column D cells are error values, and the aim is to update the corresponding error type descriptions in column F. Assume we have a lookup table listing error codes and the respective error type descriptions.
Then, we can use the ERROR.TYPE function with IF and LOOKUP functions in the column F cells to fetch the required outcome.
Step 1: Select cell F2, enter the following expression, and press Enter.
=IF(ISERROR(D2),LOOKUP(ERROR.TYPE(D2),$H$2:$H$9,$I$2:$I$9),D2)
Step 2: Use the option fill handle to feed in the formula in the remaining target cells.
Firstly, the ISERROR() executes as the IF() condition. Since the cell reference supplied as the input to the ISERROR() holds an error value, the function output is TRUE. So, the IF() condition is true.
Thus, the LOOKUP() gets executed. The ERROR.TYPE() in the LOOKUP() returns the code of the error in the specified cell. Next, the LOOKUP() searches for the number the ERROR.TYPE() returns as the error code in the range H2:H9. Next, once the function finds the match in a cell in the range H2:H9, it returns the value in the corresponding row of the range I2:I9, which is the required error type description.
The ERROR.TYPE function fixes the #VALUE! error in Google Sheets using the following formula:
=IF(ERROR.TYPE(cell_reference)=3,”Check the formula’s data type.”, cell_reference)
Where,
• cell_reference: The reference to the cell holding the data we aim to check for the #VALUE! error.
Firstly, the IF() condition executes.
So, the ERROR.TYPE() accepts the reference to the cell containing the data we aim to check for the #VALUE! error. If the data in the specific cell is the #VALUE! error, the function output is 3. In such a case, the IF() condition is true. So, the IF() returns the TRUE value, which is the customized comment mentioned in the formula.
The common mistakes we make when applying ERROR.TYPE formula in Google Sheets are the following:
• We may not utilize the ERROR.TYPE function with an error value.
• We may apply the ERROR.TYPE function with an error type that the specific function does not recognize.
Download Template
This article must be helpful to understand the ERROR.TYPE Function In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is ERROR.TYPE Function In Google Sheets. Here we learn how to use ERROR.TYPE function in Google Sheets with examples. You can learn more from the following articles –
Leave a Reply