Blog

Ever had one of those moments in Excel where you enter a formula, and all you see is an ugly error message like #DIV/0! or #VALUE!? That’s where the IFERROR function comes in to save the day! Instead of showing an error, IFERROR allows you to replace it with a custom message or value of your choice. It’s perfect for cleaning up your spreadsheets and making them more user-friendly, especially when errors are expected but not desired.

Syntax

Here’s the syntax for the IFERROR function. It’s straightforward and super easy to implement:

• value: The formula or expression you want to evaluate.
• value_if_error: The result to return if an error is found (instead of the error message).

Example

Imagine you're dividing sales by the number of transactions to calculate the average sales per transaction. But in some cases, the number of transactions is zero, which would normally result in a #DIV/0! error. Let’s use IFERROR to handle this smoothly.

In this example, if the number of transactions is zero, instead of showing a #DIV/0! error, the formula returns "No Transactions".

Practice Exercise 1

Task: You have a dataset of prices and quantities. You need to calculate the total cost for each item by multiplying the price by the quantity. However, some quantities may be missing, which would result in an error. Use IFERROR to handle the errors and return "Missing Data" instead.

Hint: Multiply the price by the quantity and use IFERROR to return "Missing Data" if there’s an issue with the calculation (e.g., missing quantity).

Practice Exercise 2

Task: You are given a list of students and their test scores. Some scores are blank or contain errors. You need to calculate the average score for each student, but if there’s an error or missing score, use IFERROR to return "Incomplete Data".

Hint: Use the AVERAGE function to calculate the average score, but apply IFERROR to return "Incomplete Data" if there are missing or erroneous scores.

Solution to Exercise 1

Handling missing data in total cost calculations:

=IFERROR(A2*B2, "Missing Data")
=IFERROR(A3*B3, "Missing Data")
=IFERROR(A4*B4, "Missing Data")

Solution to Exercise 2

Calculating average scores and handling missing or erroneous data:

=IFERROR(AVERAGE(B2:D2), "Incomplete Data")
=IFERROR(AVERAGE(B3:D3), "Incomplete Data")
=IFERROR(AVERAGE(B4:D4), "Incomplete Data")

With IFERROR, you can clean up your data and keep your formulas from showing those annoying error messages. Whether you’re calculating costs, averaging scores, or anything else, IFERROR ensures that your Excel sheets stay tidy and informative!