ERROR.TYPE Function

Today, I want to talk to you about a really useful formula in Google Sheets called ERROR.TYPE. This formula is perfect for catching and handling errors in your spreadsheet formulas. It’s a great tool to have in your toolbox if you work with large, complex spreadsheets.

ERROR.TYPE is a simple formula that takes a single argument: a value or formula that you want to test for an error. If the argument returns an error, ERROR.TYPE will return a number that represents the type of error that was encountered. This number can then be used in other formulas to handle the error in a way that makes sense for your spreadsheet. For example, you could use the IF formula to test for a specific error type and show a custom error message to the user. Overall, ERROR.TYPE is a versatile and powerful formula that can help you create more robust and reliable spreadsheets.

Definition of ERROR.TYPE Function

The ERROR.TYPE function in Google Sheets is a formula that returns a number representing the type of error that occurred in a given formula. It takes a single argument, which can be a formula or value that you want to test for an error. The returned error type can then be used in other formulas to handle the error in a way that makes sense for your spreadsheet. This function is useful for creating more robust and reliable spreadsheets by allowing you to catch and handle errors in your formulas.

Syntax of ERROR.TYPE Function

The syntax for the ERROR.TYPE function in Google Sheets is =

=ERROR.TYPE(value)

where value is the formula or value that you want to test for an error. This function returns a number representing the type of error that occurred in the given formula. For example, if the value argument returns the #DIV/0! error, ERROR.TYPE would return the number 2. This number can then be used in other formulas to handle the error in a specific way.

Examples of ERROR.TYPE Function

Here are three examples of how you can use the ERROR.TYPE function in Google Sheets:

1. To handle a #DIV/0! error, you can use the IF formula with ERROR.TYPE to check for the error type and show a custom error message instead of the default error. For example:

   =IF(ERROR.TYPE(A2/B2)=2, "Cannot divide by zero", A2/B2)

   This formula will return the result of A2/B2 if it doesn’t result in a #DIV/0! error, and will show the message “Cannot divide by zero” if it does.

2. To create a more user-friendly spreadsheet, you can use ERROR.TYPE to show a custom error message for any error that occurs in a specific cell. For example:

   =IF(ERROR.TYPE(A2)>0, "An error occurred in this cell. Please check your input and try again.", A2)

   This formula will return the value of A2 if it doesn’t result in any errors, and will show the message “An error occurred in this cell. Please check your input and try again.” if it does.

3. To create a more robust spreadsheet, you can use ERROR.TYPE in combination with other formulas to handle different types of errors in different ways. For example:

   =IF(ERROR.TYPE(A2) = 2, "Cannot divide by zero", IF(ERROR.TYPE(A2) = 3, "Invalid value", A2))

   This formula will return the result of A2 if it doesn’t result in any errors, will show the message “Cannot divide by zero” if it results in a #DIV/0! error, and will show the message “Invalid value” if it results in any other error type.

Use Case of ERROR.TYPE Function

Here are some real-life examples of using the ERROR.TYPE function in Google Sheets:

1. A finance analyst uses ERROR.TYPE to handle errors in a spreadsheet that tracks the company’s budget. The analyst uses the formula to show a custom error message for any cells that result in a #REF! error, indicating that the cell contains a broken formula or invalid reference.
2. A teacher uses ERROR.TYPE to check for errors in a spreadsheet that calculates students’ grades. The teacher uses the formula to show a custom error message for any cells that result in a #VALUE! error, indicating that the cell contains an invalid value or data type.
3. A data scientist uses ERROR.TYPE to handle errors in a spreadsheet that analyzes survey results. The scientist uses the formula to show a custom error message for any cells that result in a #N/A error, indicating that the cell contains a value that is not available or cannot be calculated.

Limitations of ERROR.TYPE Function

The ERROR.TYPE function in Google Sheets has a few limitations that you should be aware of:

1. ERROR.TYPE only works with a limited set of error types. It can’t handle all possible errors that might occur in a formula, such as the #NAME? error or the #NULL! error.
2. ERROR.TYPE only returns the error type as a number. It doesn’t return the error message itself, so you’ll need to use another formula to show the error message to the user.
3. ERROR.TYPE doesn’t work with custom error messages that you create using the ISERROR and IFERROR functions. It only works with the default error messages that are built into Google Sheets.

Overall, while ERROR.TYPE is a useful formula for catching and handling errors in your spreadsheets, it’s not a perfect solution and you may need to use other formulas in combination with it to create a truly robust and reliable spreadsheet.

Commonly Used Functions Along With ERROR.TYPE

Here is a list of commonly used functions in Google Sheets that you can use along with the ERROR.TYPE function:

1. SUM: This function adds the numbers in a range of cells. For example, to add the numbers in cells A1 to A5, you would use the following formula: =SUM(A1:A5).
2. AVERAGE: This function calculates the average of the numbers in a range of cells. For example, to calculate the average of the numbers in cells A1 to A5, you would use the following formula: =AVERAGE(A1:A5).
3. MIN: This function finds the minimum value in a range of cells. For example, to find the minimum value in cells A1 to A5, you would use the following formula: =MIN(A1:A5).
4. MAX: This function finds the maximum value in a range of cells. For example, to find the maximum value in cells A1 to A5, you would use the following formula: =MAX(A1:A5).
5. IF: This function allows you to perform a logical test and return one value if the test is true and another value if the test is false. For example, to check if the value in cell A1 is greater than 10 and return “YES” if it is and “NO” if it isn’t, you would use the following formula: =IF(A1>10,”YES”,”NO”).

To use the ERROR.TYPE function with these other functions, you would simply include it in the formula like any other function. For example, you could use the ERROR.TYPE function to check if the result of a SUM function is an error and return the error type, like this: =ERROR.TYPE(SUM(A1:A5)). This formula would return the error type if the SUM function returns an error, or #N/A if it doesn’t.

Summary

The ERROR.TYPE function in Google Sheets is a useful tool for identifying and troubleshooting errors in formulas. This function takes a value as input and returns a number that represents the type of error, if the input value is an error.

There are several different types of errors that the ERROR.TYPE function can identify, including:

• #N/A: This error is returned when a value is not available or not applicable.
• #VALUE!: This error is returned when a value is not of the expected type, such as when a text value is used in a mathematical formula.
• #REF!: This error is returned when a reference to a cell or range is not valid.
• #DIV/0!: This error is returned when a formula attempts to divide by zero.
• #NAME?: This error is returned when a formula contains an unrecognized function or syntax.

Using the ERROR.TYPE function is simple. You just include it in your formula, along with the value that you want to check for errors. For example, if you have a SUM formula that adds the numbers in cells A1 to A5 and you want to check for errors, you could use the following formula: =ERROR.TYPE(SUM(A1:A5)). This would return the error type if the SUM formula returns an error, or #N/A if it doesn’t.

We encourage you to try using the ERROR.TYPE function in your own Google Sheets to help identify and troubleshoot errors in your formulas. It’s a useful tool that can save you time and frustration.