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.
| Department | Total Cost | Employees | Cost Per Employee |
|---|---|---|---|
| HR | 50000 | 5 | |
| Sales | 80000 | 0 | |
| IT | 60000 | 4 |
Excel Formula
Enter this formula in D2:
=IFERROR(B2/C2,"Not Available")
Expected Result
| Department | Cost Per Employee |
|---|---|
| HR | 10000 |
| Sales | Not Available |
| IT | 15000 |
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.
| Product | Total Amount | Quantity | Price Per Unit |
|---|---|---|---|
| Keyboard | 3000 | 5 | |
| Mouse | 1200 | 0 | |
| Monitor | 15000 | 3 |
Excel Formula
Enter this formula in D2:
=IFERROR(B2/C2,0)
Expected Result
| Product | Price Per Unit |
|---|---|
| Keyboard | 600 |
| Mouse | 0 |
| Monitor | 5000 |
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.
| Employee | Sales | Commission Rate | Commission |
|---|---|---|---|
| Amit | 50000 | 5% | |
| Neha | 40000 | 10% | |
| Rahul | 30000 | 0% |
Excel Formula
Enter this formula in D2:
=IFERROR(B2*C2,"Calculation Error")
Expected Result
| Employee | Commission |
|---|---|
| Amit | 2500 |
| Neha | 4000 |
| Rahul | 0 |
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.
| Student | Total Marks | Subjects | Average Marks |
|---|---|---|---|
| Ankit | 400 | 5 | |
| Priya | 350 | 0 | |
| Rohit | 420 | 6 |
Excel Formula
Enter this formula in D2:
=IFERROR(B2/C2,"Average Not Available")
Expected Result
| Student | Average Marks |
|---|---|
| Ankit | 80 |
| Priya | Average Not Available |
| Rohit | 70 |
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.
| Employee | Present Days | Working Days | Attendance % |
|---|---|---|---|
| Aman | 24 | 30 | |
| Priya | 25 | 0 | |
| Karan | 27 | 30 |
Excel Formula
Enter this formula in D2:
=IFERROR(B2/C2*100,"Invalid Data")
Expected Result
| Employee | Attendance % |
|---|---|
| Aman | 80 |
| Priya | Invalid Data |
| Karan | 90 |
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.
- Calculate the price per item when Total Price is divided by Quantity. Display
Check Quantityif an error occurs. - Calculate the cost per student by dividing Total Fees by Student Count. Display
Not Availableif an error occurs. - Calculate profit per unit by dividing Total Profit by Units Sold. Display
0if an error occurs.
Final Student Challenge: Build an Employee Cost Report
Create the following report and calculate the cost per employee using IFERROR.
| Department | Total Expense | Employee Count | Cost Per Employee |
|---|---|---|---|
| HR | 75000 | 5 | |
| Sales | 120000 | 8 | |
| IT | 90000 | 0 | |
| Marketing | 60000 | 4 | |
| Finance | 50000 | 0 |
Challenge Requirements:
- Calculate Cost Per Employee.
- Display
No Employee Datawhenever 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.

