ASSIGNMENT – 46

USE OF IFERROR – ERROR HANDLING IN EXCEL

Objective

In this assignment, you will learn how to handle Excel errors using:

  • IFERROR

  • VLOOKUP

  • Division by Zero

  • Missing Data

  • Error Messages

  • Clean and User-Friendly Results

PART A – DIVISION ERROR

Student Total Marks Subjects
Aarav 450 5
Meera 380 4
Rahul 0 0
Pooja 475 5
Karan 320 4
Simran 0 0
Mohit 420 5
Neha 360 4
Arjun 280 0
Priya 455 5

Q1.

Calculate Average Marks using:

= B2/C2

Observe what happens when Subjects = 0.

Q2.

Now calculate Average Marks using IFERROR.

If an error occurs, display:

“Not Available”

Use:

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

Q3.

How many students have “Not Available” as their Average Marks?

Use:

COUNTIF

Q4.

Modify the formula so that instead of "Not Available", it displays:

“No Subjects”

Use:

IFERROR

PART B – VLOOKUP WITH IFERROR

Employee Salary Table

Emp ID Employee Name Department Salary
101 Aarav Sales 35000
102 Meera HR 42000
103 Rahul IT 50000
104 Pooja Sales 46000
105 Karan HR 38000
106 Simran IT 55000
107 Mohit Sales 62000
108 Neha HR 44000
109 Arjun IT 48000
110 Priya Sales 52000

Employee IDs to Search

Search ID
103
106
110
115
120
108
125
101

Q5.

Use VLOOKUP to find the Employee Name for each Search ID.

Use:

=VLOOKUP(A2,$F$2:$I$11,2,FALSE)

Observe what happens for IDs that do not exist.

Q6.

Modify the VLOOKUP formula using IFERROR.

If the Employee ID is not found, display:

“Employee Not Found”

Use:

=IFERROR(VLOOKUP(A2,$F$2:$I$11,2,FALSE),"Employee Not Found")

Q7.

Use IFERROR + VLOOKUP to find the Department for each Search ID.

If the ID does not exist, display:

“No Department”

Q8.

Use IFERROR + VLOOKUP to find the Salary for each Search ID.

If the ID does not exist, display:

“Salary Not Available”

PART C – PRACTICAL ERROR HANDLING

Q9.

Create a formula to calculate:

Salary ÷ 12

If an error occurs, display:

“Calculation Error”

Use:

IFERROR

Q10.

Create a Final Employee Information column.

For each Search ID, display:

Employee Name – Department – Salary

Example:

Rahul – IT – ₹50,000

If the Employee ID does not exist, display:

“Employee Record Not Found”

Use:

IFERROR + VLOOKUP + &

BONUS CHALLENGE ⭐

Q11.

Find the total number of valid Employee IDs and invalid Employee IDs.

Q12.

How many Search IDs are not found?

Q13.

Create a formula that displays:

  • Salary ≥ ₹50,000 → “High Salary”

  • Salary < ₹50,000 → “Normal Salary”

  • Employee not found → “No Record”

Use:

IF + IFERROR + VLOOKUP

Q14.

Create a formula that displays:

“Found” if the Employee ID exists and
“Not Found” if it does not exist.

Q15. FINAL CHALLENGE ⭐

Create one Employee Search Report with:

Search ID Employee Name Department Salary Status
103        
106        
110        
115        
120        
108        
125        
101        

For missing IDs, display:

Employee Not Found

IMPORTANT CONCEPT

What does IFERROR do?

IFERROR checks a formula.

If the formula gives a valid result → it returns the result.

If the formula gives an error → it returns the value you specify.

Syntax

=IFERROR(value,value_if_error)

Example

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

Common Excel Errors

Error Meaning
#DIV/0! Division by zero
#N/A Value not found
#VALUE! Wrong type of value
#REF! Invalid cell reference
#NAME? Excel does not recognize the formula/name

Key Difference

Without IFERROR

=VLOOKUP(A2,$F$2:$I$11,2,FALSE)

Missing ID → #N/A

With IFERROR

=IFERROR(VLOOKUP(A2,$F$2:$I$11,2,FALSE),"Employee Not Found")

Missing ID → Employee Not Found

STUDENT TASK

Complete all 15 questions using formulas.

Do not manually replace errors.

Use IFERROR to create clean, professional and user-friendly Excel reports.

PreviousNext