This guide covers everything you need to know about the Google Sheets IFERROR function, including its definition, syntax, use cases, and how to use it.
What is the IFERROR Function? How Does It Work?
The IFERROR function in Google Sheets returns the first argument if it is not an error value. Otherwise, it returns the second argument if present or a blank if the second argument is absent.
This function operates as a sort of safety net for your data, which can be incredibly beneficial when dealing with large datasets or complex calculations. It works by assessing the first value in the function; if this value is not an error, then the function will simply return this first value. However, if the first value is an error, the function will return the second value if one is present. In the event that the second argument is absent, the function will return a blank.
The purpose of the IFERROR function is to ensure that your data remains clean and readable, even when errors occur. For instance, if you’re calculating the unit price and the quantity is null, the IFERROR function returns the value “0.” If you’re searching for a student’s grade where the student ID doesn’t exist, the function returns the specified error message.
Essentially, the IFERROR function helps you control the output of your data in the event of an error rather than allowing an error to potentially throw off your entire data set or impede the readability of your results.
It’s important to note that the IFERROR function is logically equivalent to IF(NOT(ISERROR(value)),value,value_if_error). This means that the function first checks if the value is not an error. If true, it returns the value. Otherwise, it returns the value_if_error. It’s crucial to ensure this is the desired behavior before using the function in your data analysis work.
IFERROR Syntax
The syntax and arguments for the function are as follows:
IFERROR(value, [value_if_error])
Here, the function has two arguments:
- value: This is the value that will be checked for an error. It can be a cell reference, a formula, or a specific value you want to check. This argument is required.
- value_if_error: This is an optional argument. If provided, the function will return this value if the ‘value’ argument is an error. If not provided, and the ‘value’ argument is an error, the function will return a blank.
Usage notes related to the syntax and arguments:
- The IFERROR function is a logical function. It checks whether the ‘value’ argument is an error and returns the ‘value_if_error’ argument if it is. If the ‘value’ argument is not an error, the function simply returns that value.
- The ‘value_if_error’ argument is optional. If it is not provided and the ‘value’ argument is an error, the function will return a blank cell if the first argument results in an error. This could be confusing, as you might not realize an error has occurred.
- The value returned by the function if the ‘value’ argument is an error and the ‘value_if_error’ argument is not provided will be a blank, not a zero. This is because the default ‘value_if_error’ is a blank, not a zero. If you want the function to return a zero in case of an error, you need to specify zero as the ‘value_if_error’ argument.
Examples of How to Use the IFERROR Function
Here are some practical examples of how to use the IFERROR function in Google Sheets.
Example #1: Simple Calculation Error Handling
Let’s say you have a dataset where you need to divide some values, but there are zeros in the denominator, which would normally cause an error. You can use the IFERROR function to handle these errors gracefully. Here’s how:
=IFERROR(A2/B2, “Error in calculation”)
In this example, if the division of the value in cell A2 by the value in cell B2 results in an error (for example, if B2 is zero), the formula will return the text “Error in calculation”.
Example #2: Calculating Averages with IFERROR
You might want to calculate the average of a range of cells, but some might not contain numbers, causing an error. You can use IFERROR to ignore these cells. Here’s how:
=IFERROR(AVERAGE(A2:A10), “Non-numeric values present”)
In this example, if there are any non-numeric values in the range A2:A10, the formula will return “Non-numeric values present” instead of an error.
Example #4: Using IFERROR with ARRAYFORMULA
ARRAYFORMULA allows you to apply a function to an entire range of cells. But if any of these cells cause an error, the whole function will fail. You can use IFERROR to handle these errors. Here’s an example:
=ARRAYFORMULA(IFERROR(A2:A10 / B2:B10, “Error”))
In this example, if any of the divisions of the values in the range A2:A10 by the corresponding values in the range B2:B10 result in an error, the formula will return “Error” for that particular cell.
IFERROR: Common Mistakes & Problems
When using the IFERROR function in Google Sheets, users often encounter several common mistakes and problems. Understanding these can help you better use the function and troubleshoot any issues you may encounter.
- Incorrect order of arguments: The IFERROR function requires two arguments. The first is the expression you want Google Sheets to evaluate, and the second is the value that should be returned if the first argument results in an error. If you switch the order of these arguments, the function will not work correctly.
- Using the wrong type of argument: The first argument in the IFERROR function must be a cell reference or a formula. The function will not work as expected if you use a text string or a number.
- Not providing a second argument: While the second argument in the IFERROR function is optional if you don’t provide it, Google Sheets will return a blank cell if the first argument results in an error. This could be confusing, as you might not realize that an error has occurred.
- Misunderstanding the types of errors that IFERROR catches: The IFERROR function will catch all types of errors, including #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. You might need to use a different function if you only want to catch specific errors.
- Overusing IFERROR: While the IFERROR function can be useful for handling errors, overusing it can make your spreadsheet difficult to debug. If you use IFERROR to catch all errors, you might not realize when a formula isn’t working as expected. Use IFERROR sparingly, and always try to understand and correct the underlying cause of any errors.
Why Is IFERROR Not Working? Troubleshooting Common Errors
If you’re working with the IFERROR function in Google Sheets and find that it’s not behaving as expected, don’t panic. It’s possible that you may be encountering some common errors. To help you troubleshoot, let’s explore some of these errors, their causes, and possible solutions.
#VALUE! Error
Cause: This error often arises when using the wrong data type for the function. For instance, if you’re trying to perform a mathematical operation on a text string, Google Sheets won’t be able to execute it and will display a #VALUE! error.
Solution: Check the data types you’re feeding into your IFERROR function. Make sure numerical operations are performed on numbers and not text strings. Use functions like TO_TEXT or TO_NUMBER to convert your data into the correct type if necessary.
#REF! Error
Cause: The #REF! error usually occurs when your formula refers to a cell that doesn’t exist. This can happen when you’ve deleted a row or column your formula references.
Solution: Review your formula to ensure all referenced cells exist. If you’ve deleted a row or column, you may need to adjust your formula or undo the deletion.
#NAME? Error
Cause: This error is caused when Google Sheets doesn’t recognize the name of the function you’re trying to use. This can happen if you’ve misspelled the function’s name or tried to use a function that doesn’t exist in Google Sheets.
Solution: Double-check your formula for any spelling mistakes. Ensure that you’re using the correct function name, and remember that function names are case-sensitive.
#DIV/0! Error
Cause: The #DIV/0! error is displayed when trying to divide a number by zero, which is mathematically impossible.
Solution: Check your formula to ensure you’re not dividing by zero. If your formula involves variables that could potentially equal zero, consider using an IF function to prevent division by zero.
#N/A Error
Cause: This error indicates that a value is unavailable to a formula or function. It often occurs when a function like VLOOKUP or HLOOKUP can’t find the value it’s supposed to search for.
Solution: Review your formula to ensure the value you’re looking for actually exists in your data range. If you’re using a lookup function, ensure the lookup value is in the first column or row of the range.
#NULL! Error
Cause: The #NULL! error typically appears when you’re trying to reference an intersection of two ranges that don’t intersect.
Solution: Check your formula to ensure the ranges you’re referencing actually intersect. You’ll need to adjust your ranges or formula if they don’t.
Using IFERROR With Other Google Sheets Functions
Combining IFERROR with other Google Sheets functions can be extremely beneficial as it allows you to manage and control errors more effectively, ensuring that your Sheets remain clean and easy to understand. Let’s take a look at how IFERROR can be used with some of the most commonly used Google Sheets functions.
With VLOOKUP
VLOOKUP is a powerful function that allows you to look for a specific value in a column of a table and then return a value from the same row of a different column. However, if the VLOOKUP function doesn’t find the specified value, it returns an #N/A error. By using IFERROR with VLOOKUP, you can replace this error with a much more user-friendly message.
Example:
Assume you have a list of product IDs in column A and their corresponding prices in column B. You want to look up the price for a specific product ID entered in cell D1. The formula would be:
=IFERROR(VLOOKUP(D1, A:B, 2, FALSE), “Product not found”)
In this case, if the product ID in D1 is not found in column A, instead of an #N/A error, “Product not found” will be displayed.
With DIVIDE
DIVIDE is a function that performs division. If the denominator is zero, DIVIDE returns a #DIV/0! error. Using IFERROR with DIVIDE allows you to replace this error with a custom message or value.
Example:
Assume you have numbers in cells A1 and B1, and you want to divide the number in A1 by the number in B1. The formula would be:
=IFERROR(DIVIDE(A1, B1), “Division by zero is not allowed”)
This formula will perform the division if B1 is not zero. If B1 is zero, instead of the #DIV/0! error, “Division by zero is not allowed” will be displayed.
With QUERY
QUERY is a function that allows you to perform a query over an array of values. If the query can’t be executed for some reason, QUERY returns an error. Combining IFERROR with QUERY can replace these errors with a custom message.
Example:
Assume you have a list of products and their prices in columns A and B, and you want to perform a query to find all products with a price greater than $100. The formula would be:
=IFERROR(QUERY(A:B, “select A where B > 100”), “No products found”)
In this case, if no products with a price greater than $100 are found, “No products found” will be displayed instead of an error.
For more details on the IFERROR function, check out the official documentation at the Google Docs Editors Help Center.
