ASSIGNMENT – 46

ANSWER KEY – IFERROR & ERROR HANDLING

PART A – DIVISION ERROR

Q1. Calculate Average Marks

Formula:

=B2/C2

Results

Student Total Marks Subjects Average Marks
Aarav 450 5 90
Meera 380 4 95
Rahul 0 0 #DIV/0!
Pooja 475 5 95
Karan 320 4 80
Simran 0 0 #DIV/0!
Mohit 420 5 84
Neha 360 4 90
Arjun 280 0 #DIV/0!
Priya 455 5 91

Important:

The error occurs because Excel cannot divide a number by 0.

Q2. Calculate Average Marks Using IFERROR

Formula

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

Results

Student Average Marks
Aarav 90
Meera 95
Rahul Not Available
Pooja 95
Karan 80
Simran Not Available
Mohit 84
Neha 90
Arjun Not Available
Priya 91

Answer:

3 Students have "Not Available".

Q3. Count “Not Available”

Formula

=COUNTIF(D2:D11,"Not Available")

Answer:

3 Students

They are:

  • Rahul

  • Simran

  • Arjun

Q4. Display “No Subjects” Instead of “Not Available”

Formula

=IFERROR(B2/C2,"No Subjects")

Result:

Student Result
Rahul No Subjects
Simran No Subjects
Arjun No Subjects

All other students receive their calculated average.

PART B – VLOOKUP WITH IFERROR

Q5. Find Employee Name Using VLOOKUP

Formula

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

Results

Search ID Employee Name
103 Rahul
106 Simran
110 Priya
115 #N/A
120 #N/A
108 Neha
125 #N/A
101 Aarav

IDs Not Found:

115, 120 and 125

Q6. VLOOKUP + IFERROR for Employee Name

Formula

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

Results

Search ID Employee Name
103 Rahul
106 Simran
110 Priya
115 Employee Not Found
120 Employee Not Found
108 Neha
125 Employee Not Found
101 Aarav

Q7. Find Department

Formula

=IFERROR(VLOOKUP(A2,$F$2:$I$11,3,FALSE),"No Department")

Results

Search ID Department
103 IT
106 IT
110 Sales
115 No Department
120 No Department
108 HR
125 No Department
101 Sales

Q8. Find Salary

Formula

=IFERROR(VLOOKUP(A2,$F$2:$I$11,4,FALSE),"Salary Not Available")

Results

Search ID Salary
103 ₹50,000
106 ₹55,000
110 ₹52,000
115 Salary Not Available
120 Salary Not Available
108 ₹44,000
125 Salary Not Available
101 ₹35,000

PART C – PRACTICAL ERROR HANDLING

Q9. Salary ÷ 12

Formula

=IFERROR(D2/12,"Calculation Error")

For valid employees:

Search ID Salary Salary ÷ 12
103 ₹50,000 ₹4,166.67
106 ₹55,000 ₹4,583.33
110 ₹52,000 ₹4,333.33
115 Not Found Calculation Error
120 Not Found Calculation Error
108 ₹44,000 ₹3,666.67
125 Not Found Calculation Error
101 ₹35,000 ₹2,916.67

Q10. Final Employee Information

Format:

Employee Name – Department – Salary

Formula

=IFERROR(VLOOKUP(A2,$F$2:$I$11,2,FALSE)&" – "&VLOOKUP(A2,$F$2:$I$11,3,FALSE)&" – ₹"&TEXT(VLOOKUP(A2,$F$2:$I$11,4,FALSE),"#,##0"),"Employee Record Not Found")

Results

Search ID Employee Information
103 Rahul – IT – ₹50,000
106 Simran – IT – ₹55,000
110 Priya – Sales – ₹52,000
115 Employee Record Not Found
120 Employee Record Not Found
108 Neha – HR – ₹44,000
125 Employee Record Not Found
101 Aarav – Sales – ₹35,000

BONUS ANSWERS ⭐

Q11. Number of Valid and Invalid Employee IDs

Valid IDs:

103, 106, 110, 108, 101

Answer:

5 Valid Employee IDs

Invalid IDs:

115, 120, 125

Answer:

3 Invalid Employee IDs

Q12. How Many Search IDs Are Not Found?

Formula

=COUNTIF(B2:B9,"Employee Not Found")

Answer:

3 Search IDs

They are:

115, 120 and 125

Q13. Salary Category

Rules:

  • Salary ≥ ₹50,000 → High Salary

  • Salary < ₹50,000 → Normal Salary

  • Employee not found → No Record

Formula

=IFERROR(IF(VLOOKUP(A2,$F$2:$I$11,4,FALSE)>=50000,"High Salary","Normal Salary"),"No Record")

Results

Search ID Category
103 High Salary
106 High Salary
110 High Salary
115 No Record
120 No Record
108 Normal Salary
125 No Record
101 Normal Salary

Summary:

3 High Salary
2 Normal Salary
3 No Record

Q14. Found or Not Found

Formula

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

Results

Search ID Status
103 Found
106 Found
110 Found
115 Not Found
120 Not Found
108 Found
125 Not Found
101 Found

Answer:

5 Found

3 Not Found

Q15. FINAL EMPLOYEE SEARCH REPORT ⭐

Search ID Employee Name Department Salary Status
103 Rahul IT ₹50,000 Found
106 Simran IT ₹55,000 Found
110 Priya Sales ₹52,000 Found
115 Employee Not Found — — Not Found
120 Employee Not Found — — Not Found
108 Neha HR ₹44,000 Found
125 Employee Not Found — — Not Found
101 Aarav Sales ₹35,000 Found

FINAL ANSWER SUMMARY

Question Answer
Q1 3 #DIV/0! errors
Q2 3 Not Available
Q3 3 Students
Q4 3 No Subjects
Q5 3 IDs return #N/A
Q6 3 Employee Not Found
Q7 3 No Department
Q8 3 Salary Not Available
Q9 3 Calculation Error
Q10 5 valid records, 3 not found
Q11 5 Valid, 3 Invalid IDs
Q12 3 Not Found
Q13 3 High, 2 Normal, 3 No Record
Q14 5 Found, 3 Not Found
Q15 Complete Search Report prepared

QUICK REVISION

IFERROR Syntax

=IFERROR(value,value_if_error)

Example

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

VLOOKUP + IFERROR

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

Important Concept

Without IFERROR:

#N/A, #DIV/0!, #VALUE! etc. दिखाई दे सकते हैं।

With IFERROR:

आप error की जगह अपना meaningful message दिखा सकते हैं।

For example:

#N/A → Employee Not Found

#DIV/0! → No Subjects

#VALUE! → Calculation Error

This makes an Excel report much more professional and user-friendly.

PreviousNext