IFERROR Function in Excel Tutorial

Excel Tutorial

Introduction

The IFERROR function in Excel helps you handle formula errors and display a meaningful result instead of error messages such as #DIV/0!, #VALUE!, or #N/A. It is useful when working with sales reports, employee records, calculations, and business data. In this tutorial, you will learn how to use IFERROR through simple, practical examples.

What is the IFERROR Function in Excel?

The IFERROR function checks whether a formula produces an error.

  • If the formula returns a valid result, Excel displays that result.
  • If the formula returns an error, Excel displays the value you specify.

IFERROR Function Syntax

=IFERROR(value, value_if_error)

Arguments:

  • value: The formula or calculation you want Excel to check.
  • value_if_error: The result Excel should display if the formula produces an error.

Practical Examples of IFERROR Function in Excel

Question 1: Handle Division by Zero Error

Problem Statement

A company wants to calculate the cost per employee. Sometimes, the employee count may be zero, which causes a #DIV/0! error.

DepartmentTotal CostEmployeesCost Per Employee
HR500005
Sales800000
IT600004

Excel Formula

Enter this formula in D2:

=IFERROR(B2/C2,"Not Available")

Expected Result

DepartmentCost Per Employee
HR10000
SalesNot Available
IT15000

Explanation

Excel divides Total Cost by Employees. If the employee count is zero, the calculation produces an error. IFERROR displays Not Available instead.

Concept Covered: Handling division errors.

Question 2: Handle Errors in Product Price Calculation

Problem Statement

A store wants to calculate the price per unit for different products.

ProductTotal AmountQuantityPrice Per Unit
Keyboard30005
Mouse12000
Monitor150003

Excel Formula

Enter this formula in D2:

=IFERROR(B2/C2,0)

Expected Result

ProductPrice Per Unit
Keyboard600
Mouse0
Monitor5000

Explanation

The formula calculates the price per unit. If the calculation produces an error, Excel displays 0 instead of an error message.

Concept Covered: Replacing errors with zero.

Question 3: Handle Errors in Sales Commission Calculation

Problem Statement

A company calculates commission as a percentage of sales. The commission rate is entered as a percentage.

EmployeeSalesCommission RateCommission
Amit500005%
Neha4000010%
Rahul300000%

Excel Formula

Enter this formula in D2:

=IFERROR(B2*C2,"Calculation Error")

Expected Result

EmployeeCommission
Amit2500
Neha4000
Rahul0

Explanation

Excel multiplies Sales by Commission Rate. All three calculations are valid, so IFERROR displays the calculated commission amounts.

Concept Covered: Using IFERROR with multiplication.

Question 4: Handle Errors in Average Calculation

Problem Statement

A training institute wants to calculate the average marks of students. Some students may not have a valid score entered.

StudentTotal MarksSubjectsAverage Marks
Ankit4005
Priya3500
Rohit4206

Excel Formula

Enter this formula in D2:

=IFERROR(B2/C2,"Average Not Available")

Expected Result

StudentAverage Marks
Ankit80
PriyaAverage Not Available
Rohit70

Explanation

Excel divides total marks by the number of subjects. When the subject count is zero, the calculation produces an error, so IFERROR displays the specified message.

Concept Covered: Handling errors in average calculations.

Question 5: Handle Errors in Percentage Calculation

Problem Statement

An HR department wants to calculate employee attendance percentages.

EmployeePresent DaysWorking DaysAttendance %
Aman2430
Priya250
Karan2730

Excel Formula

Enter this formula in D2:

=IFERROR(B2/C2*100,"Invalid Data")

Expected Result

EmployeeAttendance %
Aman80
PriyaInvalid Data
Karan90

Explanation

Excel calculates attendance by dividing Present Days by Working Days and multiplying the result by 100. If Working Days is zero, IFERROR displays Invalid Data.

Concept Covered: Handling errors in percentage calculations.

Student Practice Tasks

Try solving these tasks using only the IFERROR function.

  1. Calculate the price per item when Total Price is divided by Quantity. Display Check Quantity if an error occurs.
  2. Calculate the cost per student by dividing Total Fees by Student Count. Display Not Available if an error occurs.
  3. Calculate profit per unit by dividing Total Profit by Units Sold. Display 0 if an error occurs.

Final Student Challenge: Build an Employee Cost Report

Create the following report and calculate the cost per employee using IFERROR.

DepartmentTotal ExpenseEmployee CountCost Per Employee
HR750005
Sales1200008
IT900000
Marketing600004
Finance500000

Challenge Requirements:

  • Calculate Cost Per Employee.
  • Display No Employee Data whenever the calculation produces an error.
  • Apply the formula to all departments.

Key Takeaways

  • IFERROR checks whether a formula produces an error.
  • It displays the calculated result when no error occurs.
  • It displays your specified value when an error occurs.
  • It can handle errors such as #DIV/0!, #VALUE!, and #N/A.
  • It helps make Excel reports cleaner and easier to understand.

Frequently Asked Questions (FAQs)

1. What is the IFERROR function in Excel?

IFERROR is an Excel function that checks a formula for errors and displays a specified result when an error occurs.

2. What is the syntax of IFERROR?

The syntax is =IFERROR(value, value_if_error).

3. What happens when a formula has no error?

Excel displays the original calculated result.

4. Can IFERROR handle the #DIV/0! error?

Yes. IFERROR can replace a division-by-zero error with a value or message you specify.

5. Can IFERROR display text instead of an error?

Yes. You can enter text such as "Not Available" or "Invalid Data" as the second argument.

6. Can IFERROR display zero when an error occurs?

Yes. Use =IFERROR(B2/C2,0) to display zero whenever the calculation produces an error.

7. Does IFERROR change the original formula?

No. It wraps the formula and controls what Excel displays when that formula produces an error.

8. When should we use IFERROR in Excel?

Use IFERROR when you want to prevent error messages from appearing in reports, calculations, and business worksheets.