ASSIGNMENT 49 – ANSWER KEY
NESTED IF + MULTIPLE CONDITIONS
Q1. Performance Category
Formula:
=IF(H2>=4.8,"Outstanding",IF(H2>=4.5,"Excellent",IF(H2>=4,"Good",IF(H2>=3,"Average","Needs Improvement"))))| Employee | Rating | Performance |
|---|---|---|
| Aarav | 4.2 | Good |
| Meera | 4.5 | Excellent |
| Rahul | 4.8 | Outstanding |
| Pooja | 4.9 | Outstanding |
| Karan | 3.2 | Average |
| Simran | 4.5 | Excellent |
| Mohit | 4.9 | Outstanding |
| Neha | 4.6 | Excellent |
| Arjun | 2.9 | Needs Improvement |
| Priya | 4.4 | Good |
| Rohan | 4.9 | Outstanding |
| Ananya | 4.7 | Excellent |
| Vikas | 3.5 | Average |
| Riya | 4.7 | Excellent |
| Nitin | 3.4 | Average |
Q2. Bonus Percentage
Formula:
=IF(E2>=60000,15%,IF(E2>=50000,10%,IF(E2>=40000,7%,IF(E2>=30000,5%,0%))))| Employee | Sales | Bonus |
|---|---|---|
| Aarav | ₹45,000 | 7% |
| Meera | ₹38,000 | 5% |
| Rahul | ₹52,000 | 10% |
| Pooja | ₹60,000 | 15% |
| Karan | ₹30,000 | 5% |
| Simran | ₹48,000 | 7% |
| Mohit | ₹72,000 | 15% |
| Neha | ₹42,000 | 7% |
| Arjun | ₹28,000 | 0% |
| Priya | ₹55,000 | 10% |
| Rohan | ₹65,000 | 15% |
| Ananya | ₹47,000 | 7% |
| Vikas | ₹40,000 | 7% |
| Riya | ₹51,000 | 10% |
| Nitin | ₹35,000 | 5% |
Q3. Training Status
Formula:
=IF(G2="Completed","Eligible","Training Required")Answers
Eligible:
-
Aarav
-
Meera
-
Rahul
-
Pooja
-
Simran
-
Mohit
-
Neha
-
Priya
-
Rohan
-
Ananya
-
Riya
Training Required:
-
Karan
-
Arjun
-
Vikas
-
Nitin
Total Eligible = 11
Training Required = 4
Q4. Attendance Status
Formula:
=IF(F2>=90,"Excellent Attendance",IF(F2>=80,"Good Attendance","Poor Attendance"))| Employee | Attendance | Status |
|---|---|---|
| Aarav | 92% | Excellent Attendance |
| Meera | 88% | Good Attendance |
| Rahul | 95% | Excellent Attendance |
| Pooja | 96% | Excellent Attendance |
| Karan | 75% | Poor Attendance |
| Simran | 90% | Excellent Attendance |
| Mohit | 94% | Excellent Attendance |
| Neha | 91% | Excellent Attendance |
| Arjun | 68% | Poor Attendance |
| Priya | 89% | Good Attendance |
| Rohan | 97% | Excellent Attendance |
| Ananya | 93% | Excellent Attendance |
| Vikas | 82% | Good Attendance |
| Riya | 94% | Excellent Attendance |
| Nitin | 80% | Good Attendance |
Q5. Employee Eligibility for Promotion
Conditions:
-
Experience >= 5
-
Sales >= ₹50,000
-
Attendance >= 90%
Formula:
=IF(AND(D2>=5,E2>=50000,F2>=90),"Eligible","Not Eligible")Eligible Employees
-
Rahul
-
Mohit
-
Rohan
Total = 3
All others = Not Eligible
Q6. High Performer
Conditions:
-
Rating >= 4.8 OR
-
Sales >= ₹60,000
Formula:
=IF(OR(H2>=4.8,E2>=60000),"High Performer","Regular Performer")High Performers
-
Rahul
-
Pooja
-
Mohit
-
Rohan
Total = 4
Q7. Incentive Category
Formula:
=IF(AND(E2>=60000,H2>=4.8),"Platinum",IF(AND(E2>=50000,H2>=4.5),"Gold",IF(AND(E2>=40000,H2>=4),"Silver","Bronze")))| Employee | Incentive |
|---|---|
| Aarav | Silver |
| Meera | Bronze |
| Rahul | Gold |
| Pooja | Platinum |
| Karan | Bronze |
| Simran | Silver |
| Mohit | Platinum |
| Neha | Silver |
| Arjun | Bronze |
| Priya | Silver |
| Rohan | Platinum |
| Ananya | Silver |
| Vikas | Bronze |
| Riya | Gold |
| Nitin | Bronze |
Summary
-
Platinum: 3
-
Gold: 2
-
Silver: 5
-
Bronze: 5
Q8. Employee Grade
Formula:
=IF(AND(F2>=90,H2>=4.8),"A+",IF(AND(F2>=90,H2>=4.5),"A",IF(AND(F2>=80,H2>=4),"B","C")))| Employee | Grade |
|---|---|
| Aarav | B |
| Meera | B |
| Rahul | A+ |
| Pooja | A+ |
| Karan | C |
| Simran | A |
| Mohit | A+ |
| Neha | A |
| Arjun | C |
| Priya | B |
| Rohan | A+ |
| Ananya | A |
| Vikas | C |
| Riya | A |
| Nitin | C |
Grade Summary
-
A+ = 4
-
A = 4
-
B = 3
-
C = 4
Q9. Final Performance Status
Formula:
=IF(AND(E2>=60000,F2>=90,H2>=4.8),"Star Performer",IF(AND(E2>=50000,F2>=90,H2>=4.5),"Excellent Performer",IF(AND(E2>=40000,F2>=80,H2>=4),"Good Performer","Needs Improvement")))| Employee | Final Status |
|---|---|
| Aarav | Good Performer |
| Meera | Needs Improvement |
| Rahul | Excellent Performer |
| Pooja | Star Performer |
| Karan | Needs Improvement |
| Simran | Good Performer |
| Mohit | Star Performer |
| Neha | Good Performer |
| Arjun | Needs Improvement |
| Priya | Needs Improvement |
| Rohan | Star Performer |
| Ananya | Good Performer |
| Vikas | Needs Improvement |
| Riya | Excellent Performer |
| Nitin | Needs Improvement |
Final Status Summary
-
Star Performer = 3
-
Pooja
-
Mohit
-
Rohan
-
-
Excellent Performer = 2
-
Rahul
-
Riya
-
-
Good Performer = 4
-
Aarav
-
Simran
-
Neha
-
Ananya
-
-
Needs Improvement = 6
-
Meera
-
Karan
-
Arjun
-
Priya
-
Vikas
-
Nitin
-
Q10. Promotion Decision
Conditions:
-
Experience >= 5
-
Sales >= ₹50,000
-
Attendance >= 90%
-
Rating >= 4.5
Formula:
=IF(AND(D2>=5,E2>=50000,F2>=90,H2>=4.5),"Promotion Recommended","Promotion Not Recommended")Promotion Recommended
-
Rahul
-
Mohit
-
Rohan
Total = 3
BONUS QUESTIONS
Q11. Number of Outstanding Employees
Outstanding means:
Rating >= 4.8
Formula:
=COUNTIF(H2:H16,">=4.8")Answer:
4
Employees:
-
Rahul
-
Pooja
-
Mohit
-
Rohan
Q12. Employees with Sales ₹50,000 or More
Formula:
=COUNTIF(E2:E16,">=50000")Answer:
6
Employees:
-
Rahul – ₹52,000
-
Pooja – ₹60,000
-
Mohit – ₹72,000
-
Priya – ₹55,000
-
Rohan – ₹65,000
-
Riya – ₹51,000
Q13. Experience >= 5 AND Attendance >= 90 AND Training Completed
Formula:
=IF(AND(D2>=5,F2>=90,G2="Completed"),"Qualified","Not Qualified")Qualified Employees
-
Rahul
-
Simran
-
Mohit
-
Rohan
-
Ananya
Total = 5
Q14. High Performer OR Promotion Recommended
Formula:
=IF(OR(AND(H2>=4.8,E2>=60000),AND(D2>=5,E2>=50000,F2>=90,H2>=4.5)),"Selected","Not Selected")Selected Employees
-
Rahul
-
Pooja
-
Mohit
-
Rohan
Total = 4
Q15. FINAL EMPLOYEE REPORT
| Employee | Performance | Bonus | Training Status | Attendance Status | Promotion | Final Status |
|---|---|---|---|---|---|---|
| Aarav | Good | 7% | Eligible | Excellent Attendance | Not Recommended | Good Performer |
| Meera | Excellent | 5% | Eligible | Good Attendance | Not Recommended | Needs Improvement |
| Rahul | Outstanding | 10% | Eligible | Excellent Attendance | Recommended | Excellent Performer |
| Pooja | Outstanding | 15% | Eligible | Excellent Attendance | Not Recommended | Star Performer |
| Karan | Average | 5% | Training Required | Poor Attendance | Not Recommended | Needs Improvement |
| Simran | Excellent | 7% | Eligible | Excellent Attendance | Not Recommended | Good Performer |
| Mohit | Outstanding | 15% | Eligible | Excellent Attendance | Recommended | Star Performer |
| Neha | Excellent | 7% | Eligible | Excellent Attendance | Not Recommended | Good Performer |
| Arjun | Needs Improvement | 0% | Training Required | Poor Attendance | Not Recommended | Needs Improvement |
| Priya | Good | 10% | Eligible | Good Attendance | Not Recommended | Needs Improvement |
| Rohan | Outstanding | 15% | Eligible | Excellent Attendance | Recommended | Star Performer |
| Ananya | Excellent | 7% | Eligible | Excellent Attendance | Not Recommended | Good Performer |
| Vikas | Average | 7% | Training Required | Good Attendance | Not Recommended | Needs Improvement |
| Riya | Excellent | 10% | Eligible | Excellent Attendance | Not Recommended | Excellent Performer |
| Nitin | Average | 5% | Training Required | Good Attendance | Not Recommended | Needs Improvement |
Important Excel Learning
Nested IF
Used when there are multiple levels/categories.
=IF(condition1,result1,IF(condition2,result2,IF(condition3,result3,result4)))AND
All conditions must be TRUE.
=AND(A2>=5,B2>=50000,C2>=90)OR
At least one condition must be TRUE.
=OR(A2>=60000,B2>=4.8)Important Rule
In Nested IF, always check the highest condition first.
For example:
=IF(H2>=4.8,"Outstanding",
IF(H2>=4.5,"Excellent",
IF(H2>=4,"Good",
IF(H2>=3,"Average","Needs Improvement"))))If 4.8 is checked after 4.5, an employee with 4.8 may incorrectly receive Excellent instead of Outstanding.