IFERROR allows you to handle errors that occur while performing calculations or formulas. It provides a way to replace these errors with specified values or even perform alternative calculations when an error is encountered. This ensures that your spreadsheet remains error-free and your data remains accurate.
The IFERROR function checks if an error occurs in a formula or calculation and provides an alternative value if true. It can handle various types of errors such as #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, and #N/A. By using this function, you can prevent error messages from cluttering your sheet and display meaningful values instead.
Simply put, IFERROR helps you turn this:
The syntax of the IFERROR function is very simple:
It only takes two parameters: value and value_if_error
value: This is the value or formula that you want to evaluate for any errors.
value_if_error: Optional. This is the value you want to display if there is an error in
So, for example, the following formula will try to calculate 1/0 (which will lead to a division-by-0 error) and instead display "I'm afraid I can't do this."
=IFERROR(1/0, "I'm afraid I can't do this.")
The second parameter is optional, so if you just want to handle the error without displaying any specific message or value, you can omit the second parameter in the IFERROR function. This way, the function will simply return a blank cell when an error occurs. For example:
Now the IFERROR function will return a blank cell instead of displaying an error message. This can be useful when you don't need to explicitly notify the user of the error, but rather want to handle it silently. But reading the function now becomes a bit confusing. As now the function will actually output results if there is no error.
Let's look at three more complex, real-life examples.
Let's say you have a worksheet that calculates the average revenue per customer. However, sometimes there may be zero customers, resulting in a division by zero error.
To handle this situation gracefully, you can use the IFERROR function.
In this example, B contains the number of months somebody has been a customer, and C contains the cummrevenue from that customer. The IFERROR function calculates the average revenue per month for each customer by dividing the total revenue by the number of months.
If there is a division by zero error, the IFERROR function replaces it with a zero. This ensures that your sum and average revenue per customer calculation doesn't break due to new customers who have 0 months of revenue recorded.
Let's simplify our table. Did you know you can use
ARRAYFORMULA to fill a hole
column with the results from a calculation?
Simply enter into E2 the following formula:
This will fill the remaining cells with the results from that calculation.
But of course, the #DIV/0! error has returned. You might have guessed it, by wrapping our division in the IFERROR function, we can catch those errors and simply ignore that row in our calculations.
But now the average has changed. What has happened? Because we didn't define a default value in the IFERROR function, it now simply returns an empty cell. The AVERAGE function ignores empty cells.
If we want to have the IFERROR function output 0 as before, we need to define 0 as the default value if there is an error.
Now the average calculation will incorporate the 0 and produce a lower average.
Lookup functions like VLOOKUP or INDEX/MATCH return errors when the specified lookup value is not found. You can use the IFERROR function to display a custom message or value instead of an error message. For instance:
=IFERROR(VLOOKUP(A1, C1:H25, 2, FALSE), "Value not found")
In this example, the VLOOKUP function looks for the value in cell A1 within the specified range. If the value is not found, the IFERROR function will display "Value not found" instead of an error.
If we add a new sheet with a VLOOKUP function to our example from above, and use an ARRAYFORMULA, it would quickly produce lots of errors:
But again, if we surround the VLOOKUP with IFERROR and simply don't specify a default value, we get empty rows for customers that aren't in our list or for empty fields.
These are just a few examples of how the IFERROR function can be used to handle errors in Google Sheets. By incorporating IFERROR into your formulas, you can ensure that your spreadsheet calculations are more robust and user-friendly, providing accurate results even in the presence of potential errors or missing data.
The IFERROR function in Google Sheets can catch various types of errors that commonly occur in spreadsheet calculations. Here are some of the types of errors that IFERROR can handle:
While the IFERROR function is a powerful tool for error handling in Google Sheets, there are alternatives available for handling errors in spreadsheet calculations. Here are a few alternatives you can consider:
The IF statement allows you to perform different actions based on a specified condition. Instead of using IFERROR to handle errors, you can use an IF statement to check for errors explicitly and provide customized responses or alternative calculations based on the condition. However, this approach can make your formulas more complex and lengthy compared to using IFERROR.
Conditional formatting is a feature in Google Sheets that allows you to format cells based on specific rules or conditions. While it doesn't directly handle errors, you can use conditional formatting to highlight or visually identify cells with errors. This can help you quickly identify and rectify the errors manually. However, it doesn't provide an automated way to replace error values with custom messages or alternative calculations.
The ISERROR and ISERR functions can be used to check whether a cell or formula returns an error. These functions return either TRUE or FALSE based on whether an error is present. You can combine these functions with IF statements to handle errors and provide custom responses. However, this approach requires more complex formulas and manual customization for each error type.
Another alternative way to handle errors in Google Sheets is to use the ISNA function. ISNA stands for "is not available" and is specifically designed to check if a cell or formula returns the #N/A error.
Here's how you can use the ISNA function as an alternative to IFERROR:
Use ISNA within an IF statement:
=IF(ISNA(A1), "Custom message or value", A1)
This formula checks if cell A1 returns the #N/A error. If it does, it displays a custom message or value. Otherwise, it simply returns the value in cell A1. You can replace "Custom message or value" with any message or value of your choice.
Combine ISNA with other error-checking functions:
You can combine ISNA with other functions like IF, VLOOKUP, or INDEX/MATCH to specifically handle the #N/A error in those formulas. Here's an example using the VLOOKUP function:
=IF(ISNA(VLOOKUP(A1, C1:H25, 2, FALSE)), "Value not found", VLOOKUP(A1, C1:H25, 2, FALSE))
In this formula, ISNA checks if the VLOOKUP function returns the #N/A error. If it does, it displays "Value not found". Otherwise, it returns the result of the VLOOKUP function.
The ISNA function is particularly useful when you want to focus on the #N/A error and handle it differently from other error types. It gives you more control over how you want to handle the #N/A error specifically, while other error-handling techniques like IFERROR handle all error types uniformly.
Consider your specific error-handling needs and choose the technique that best suits the nature of the errors you encounter in your spreadsheet calculations.