ASSIGNMENT 49

NESTED IF + MULTIPLE CONDITIONS – EMPLOYEE PERFORMANCE ANALYSIS

Objective

In this assignment, you will learn how to use:

  • IF

  • Nested IF

  • AND

  • OR

  • Multiple Conditions

  • Performance Categories

  • Salary/Performance-based Decision Making

Employee Performance Data

ID Employee Name Department Experience Sales Attendance % Training Rating
101 Aarav Sales 3 45000 92 Completed 4.2
102 Meera HR 5 38000 88 Completed 4.5
103 Rahul Sales 7 52000 95 Completed 4.8
104 Pooja IT 4 60000 96 Completed 4.9
105 Karan HR 2 30000 75 Pending 3.2
106 Simran Sales 6 48000 90 Completed 4.5
107 Mohit IT 8 72000 94 Completed 4.9
108 Neha HR 4 42000 91 Completed 4.6
109 Arjun Sales 1 28000 68 Pending 2.9
110 Priya IT 5 55000 89 Completed 4.4
111 Rohan Sales 9 65000 97 Completed 4.9
112 Ananya HR 6 47000 93 Completed 4.7
113 Vikas IT 3 40000 82 Pending 3.5
114 Riya Sales 4 51000 94 Completed 4.7
115 Nitin HR 7 35000 80 Pending 3.4

QUESTIONS

Q1. Performance Category using Nested IF

Create a new column Performance Category using these rules:

  • Rating >= 4.8 → Outstanding

  • Rating >= 4.5 → Excellent

  • Rating >= 4.0 → Good

  • Rating >= 3.0 → Average

  • Rating < 3.0 → Needs Improvement

Use a Nested IF formula.

Q2. Calculate Bonus Percentage

Use the following conditions:

  • Sales >= ₹60,000 → 15% Bonus

  • Sales >= ₹50,000 → 10% Bonus

  • Sales >= ₹40,000 → 7% Bonus

  • Sales >= ₹30,000 → 5% Bonus

  • Sales < ₹30,000 → No Bonus

Use Nested IF.

Q3. Training Status

Using IF:

  • Completed → Eligible

  • Pending → Training Required

Q4. Attendance Status

Create an Attendance Status column:

  • Attendance >= 90 → Excellent Attendance

  • Attendance >= 80 → Good Attendance

  • Attendance < 80 → Poor Attendance

Use Nested IF.

Q5. Employee Eligibility

An employee is Eligible for Promotion if:

  • Experience >= 5 years AND

  • Sales >= ₹50,000 AND

  • Attendance >= 90%

Otherwise, show Not Eligible.

Use IF + AND.

Q6. High Performer

An employee is a High Performer if:

  • Rating >= 4.8 OR

  • Sales >= ₹60,000

Otherwise, show Regular Performer.

Use IF + OR.

Q7. Incentive Category

Create an Incentive Category:

  • Sales >= ₹60,000 AND Rating >= 4.8 → Platinum

  • Sales >= ₹50,000 AND Rating >= 4.5 → Gold

  • Sales >= ₹40,000 AND Rating >= 4.0 → Silver

  • Otherwise → Bronze

Use Nested IF + AND.

Q8. Employee Grade

Create a Grade using Attendance and Rating:

  • Attendance >= 90 AND Rating >= 4.8 → A+

  • Attendance >= 90 AND Rating >= 4.5 → A

  • Attendance >= 80 AND Rating >= 4.0 → B

  • Otherwise → C

Q9. Final Performance Status

Create a Final Status using these rules:

  • Sales >= ₹60,000 AND Attendance >= 90 AND Rating >= 4.8
    → Star Performer

  • Sales >= ₹50,000 AND Attendance >= 90 AND Rating >= 4.5
    → Excellent Performer

  • Sales >= ₹40,000 AND Attendance >= 80 AND Rating >= 4.0
    → Good Performer

  • Otherwise
    → Needs Improvement

Use Nested IF + AND.

Q10. Promotion Decision

An employee will receive Promotion Recommended if:

  • Experience >= 5 years

  • Sales >= ₹50,000

  • Attendance >= 90%

  • Rating >= 4.5

All four conditions must be TRUE.

Otherwise, show Promotion Not Recommended.

BONUS QUESTIONS

Q11.

Count the number of employees who are Outstanding according to the Rating category.

Q12.

Find the number of employees whose Sales are ₹50,000 or more.

Q13.

Find employees who have:

Experience >= 5 AND Attendance >= 90 AND Training = Completed

Q14.

Find employees who are:

High Performer OR Promotion Recommended

Q15. Final Employee Report

Create a final report containing:

Employee Name Performance Bonus % Training Status Attendance Status Promotion Final Status

Use formulas for all columns.

Important Note

For Nested IF, always check the highest condition first.

Example:

=IF(H2>=4.8,"Outstanding",IF(H2>=4.5,"Excellent",IF(H2>=4,"Good",IF(H2>=3,"Average","Needs Improvement"))))

If you check lower conditions first, higher-rated employees may get the wrong category.

PreviousNext