Managing Errors in Excel and Google Sheets: A Comprehensive Guide with Examples Using the IFERROR Function

Hello, friends! Welcome! Today, I’ll be discussing the IFERROR function, which comes in handy when dealing with errors that occur while using formulas. You might have come across errors like “#N/A” or “#VALUE!”, but fear not! The IFERROR function can help us handle these errors effectively. So, let’s dive into what the IFERROR function actually does.

What does the IFERROR Function do? (Purpose)

The IFERROR function provides a sleek solution for managing the display of errors when they are detected. So, what does it return? Well, if there is no error, the function will return the calculated result as expected.

What does the IFERROR Function return? (Output)

If there is an error, the IFERROR function will return the standard result of the formula. However, if an error is detected, you can specify a value to be returned instead. Now, let’s explore how to use the IFERROR function. We need to instruct Google Sheets or Excel on how to handle errors using this function.

Parameters in the IFERROR Function

There are two things to consider when using the IFERROR function. First, you need to specify what you want to check for errors, which can be a formula, reference, or value. Second, you need to determine the value to return if an error is detected. These two parameters are essential in the IFERROR function. In Google Sheets, the function will look like this:

Google Sheets versus Excel

In Google Sheets, the syntax of the IFERROR function is “IFERROR(value, value_if_error)”, where “value” represents the value or formula to check for errors, and “value_if_error” is the value to return if an error is detected.

In Excel, the syntax is the same: “IFERROR(value, value_if_error)”. Excel users will also see this structure when working with the IFERROR function.

Example of IFERROR Function

In this table, we have various companies listed in the first column, their corresponding sales amounts in the second column, the number of employees in the third column, and a calculated field in the fourth column representing sales per employee (sales divided by the number of employees).

To find the sales per employee, you can enter the formula “=C7/D7” in a cell, where C7 represents the sales amount for a specific company and D7 represents the number of employees for that company. When you autofill or drag the formula down, it will populate the subsequent rows accordingly.

However, in some rows, you may encounter an error, such as “#VALUE!” In the ninth row, for example, there is a comment indicating that the error is caused by the denominator “20s2” being a text value instead of a number. To manage this error, you can use the IFERROR function.

To implement the IFERROR function, type “=IFERROR(formula, value_if_error)” in a cell. In the formula parameter, specify the formula you want to check for errors, which in this case is “C7/D7” for the sales per employee calculation. The value_if_error parameter allows you to define the value or message to return if an error is detected.

For instance, you can leave the second parameter empty (“”) to display a blank cell when an error occurs. Dragging the formula down will populate the subsequent cells with the calculated values, while any rows with errors will remain blank.

Alternatively, you can specify a custom message, such as “Not found” or “Yes,” by entering it as the second parameter of the IFERROR function.

By employing the IFERROR function, you can effectively manage errors and display desired values or messages in cells where errors occur.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top