What Is ISERR Function in Google Sheets?
The ISERR function in Google Sheets checks if a cell contains an error, returning TRUE if it has and FALSE otherwise. The only error which is excluded is the #N/A error. In short, the Google Sheets ISERR function checks if a given value is an error, except for the #N/A error. The return value of the function is TRUE for any error apart from #N/A, and FALSE if it is not an error. The function is beneficial in identifying errors in your spreadsheet. We get TRUE for the following errors: Except #N/A, i.e., #DIV/0!, #NAME?, #VALUE!, #REF!, #NULL!, #NUM!.We get FALSE if the value is not an error or for the #N/A error.
For example,
- =ISERR(1/0) returns TRUE because 1 divided by 0 results in a #DIV/0! Error.
- =ISERR(A2) returns FALSE if cell A2 contains a value or text, or if it contains the error #N/A.
Key Takeaways
- The ISERR function in Google Sheets checks if a value is an error and returns TRUE for errors such as #DIV/0!, #VALUE!, #NAME!, etc. but excludes the #N/A error.
- The syntax of the ISERR function is:
- =ISERR(value), where value is the value or formula you want to check for errors.
- ISERR is generally used to handle errors in formulas but ignores the #N/A error we commonly get especially when using LOOKUP functions. It can show custom error messages in place of typical error codes.
- It is similar to the ISERROR, which catches all types of errors including #N/A. But ISERR only detects errors other than #N/A.
- One can combine ISERR with functions like IF and SUM to manage errors in formulas.
Syntax
The syntax for the ISERR function is as follows:
=ISERR(value)
Here, value can be a cell reference, a formula, or a static value. The function evaluates what’s inside the parentheses and tells you if there’s an error worth noting.
How To Use ISERR Function in Google Sheets?
Using ISERR in your sheets can improve the overall efficiency and performance as by managing errors, you can improve the functionality without wasting time on troubleshooting.
There are two ways you can enter the ISERR function.
- Entering ISERR manually
- Through the Google menu bar
Entering ISERR Manually
Let us look at how to enter the function in a spreadsheet with a simple example. We have a few values in cells A1 to A3.
Step 1: To find if we have an error in cell A1, enter the following function in cell B1. Press Enter.
=ISERR(A1)
As A1 contains an error #DIV/0!, it will return TRUE. If A1 contains a valid value, we get FALSE.
Step 2: Drag the formula to cell B3. You can observe that cell A2 contains a valid value and hence you get FALSE. As cell A3 contains the #N/A error, it shows FALSE.
Through The Menu Bar
- Another method of using the ISERR is through the menu bar.
- Go to Insert > Function > Info.
- Scroll down to ISERR and click on it.
- Enter the required arguments and press Enter.
Examples
We can combine ISERR with other functions and use them in many different ways to tackle complex calculations. Let’s check how ISERR can be used with some ISERR function in Google Sheets examples, which will help you understand the function used in real-world scenarios.
Example #1
Let us look at an example of how to use the ISERR function in Google Sheets to understand how to use this error handling function in a data calculation scenario.
Here, we have some numbers in two columns.
Let’s say you’re working with a dataset where you’re calculating the result of dividing two columns. Some of the data contains errors such as #DIV/0! or #VALUE!, and you want to handle these errors using the ISERR function.
Step 1: Enter the dataset where you have two columns of numbers where we will divide the values of one column by the other.
Step 2: Now, divide the values in Column A by the values in Column B to calculate the result in Column C. The formula will be
=A2 / B2
However, since B2 has a 0 in it, you’ll encounter a #DIV/0! error for the second row, and B4 has #VALUE!, which will cause a #VALUE! error. Here, we try to print a custom message.
Hence, we modify the formula to use ISERR and handle errors.
=IF(ISERR(A2 / B2), “Error present”, A2/ B2)
Here,
ISERR(A2 / B2) checks if the division results in an error (but not #N/A).
The IF function checks if there’s an error, if so, it will return the text “Error present.” In the absence of error, it will show the result of the division.
Enter the function in cell C2 and press Enter.
Step 3: After entering the formula in C2, drag it down to apply it to the entire range. This will automatically check each row for errors in the division.
Example #2 – ISERR Function with IF Function
In this example, we see how to use ISERR with the IF function in Google Sheets in a practical scenario. We have a sheet with a budget. To find the spending ratio, we will be dividing the expenses by income.
Step 1: Enter the data as shown below.
Step 2: Enter the following formula in cell C2.
=IF(ISERR(A2/B2), “N/A”, A2/B2)
Step 3: If income is zero, you get a #DIV/0! Error. This will be handled by printing the custom message “N/A.”
Drag the formula for all the values and observe the outcome.
Example #3 – ISERR Function with the SUM Function
Here, we have a set of values in a column, with some cells containing errors (other than #N/A). We use ISERR to check for errors when adding the values and give solutions accordingly.
Step 1: Enter the data in the cells as shown below.
Step 2: Let us combine ISERR with the SUM function to check for errors in the range before summing the values. Enter the following formula in a cell where you want the result.
=IF(ISERR(SUM(A1:A6)), “Error in values provided”, SUM(A1:A6))
Here,
SUM(A1:A6) adds all the values in the range A1:A5. If any of the cells contain an error, the SUM function will return an error.
The ISERR function checks if the SUM result is an error. If any error occurs except #N/A, it returns TRUE; else, it returns FALSE.
This IF function checks whether there’s an error in the sum. If ISERR returns TRUE, the formula will display “Error in data”. If no error is found, it will return the actual sum.
In the case of this data (A1:A6), because the sum contains errors (#DIV/0! and #VALUE!), the ISERR function will return TRUE, and the formula will display “Error in values provided”.
If all values in the range were valid, it would return the actual sum. We can check that by replacing the #VALUE and the #DIV/0 errors.
Important Things to Note
- ISERR does not handle #N/A errors. If the cell contains #N/A, ISERR will return FALSE.
- Using ISERR ensures that your data remains clean and devoid of errors.
- One of the scenarios where ISERR is commonly used is in formulas where we must handle all types of errors but ignore #N/A errors. This scenario arises when we use LOOKUP functions.
Frequently Asked Questions (FAQs)
1. We use ISERR along with the IF function to create formulas that handle errors and display a custom message. We can also perform a different calculation when an error is encountered.
2. The ISERR function helps identify and address errors in the data, thereby ensuring the reliability of your calculations.
3. When we divide numbers, we might encounter #DIV/0! errors and to avoid them we use this function.
4. When using complex formulas which could result in errors, we use the ISERR function.
ISERR is a function that checks for all errors except #N/A. It is used to catch errors such as #DIV/0!, #VALUE!, and #REF! and will return a FALSE only if the error is #N/A.
ISERROR is used to check for all types of errors, including #N/A. Hence, if a cell contains any error, ISERROR will return TRUE.
We can combine ISERR in Google Sheets with many functions to use them effectively to handle errors. But the most used one if we must handle errors in a calculation is using ISERR with an IF statement to provide a custom message or value.
For example, =IF(ISERR(A1), “Error”, A1)
This formula checks if the value in A1 is an error and returns “Error” if it is, otherwise, it returns the value itself.
Download Template
This article must help understand ISERR Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is ISERR Function in Google Sheets. We learn its syntax & how to use it to find if a cell contains an error with examples. You can learn more from the following articles. –
Leave a Reply