ASSIGNMENT 50 – FINAL ANSWER KEY

EMPLOYEE PERFORMANCE & SALARY ANALYSIS

PART B – CALCULATIONS

Target Status

Formula:

=IF(H2>=L2,"Achieved","Not Achieved")
Employee Sales Target Status
Aarav 45,000 50,000 Not Achieved
Meera 38,000 40,000 Not Achieved
Rahul 52,000 50,000 Achieved
Pooja 60,000 55,000 Achieved
Karan 30,000 40,000 Not Achieved
Simran 48,000 50,000 Not Achieved
Mohit 72,000 65,000 Achieved
Neha 42,000 40,000 Achieved
Arjun 28,000 35,000 Not Achieved
Priya 55,000 50,000 Achieved
Rohan 65,000 60,000 Achieved
Ananya 47,000 45,000 Achieved
Vikas 40,000 45,000 Not Achieved
Riya 51,000 50,000 Achieved
Nitin 35,000 40,000 Not Achieved

Achieved = 8

Not Achieved = 7

Achievement %

Formula:

=H2/L2*100
Employee Achievement %
Aarav 90.00%
Meera 95.00%
Rahul 104.00%
Pooja 109.09%
Karan 75.00%
Simran 96.00%
Mohit 110.77%
Neha 105.00%
Arjun 80.00%
Priya 110.00%
Rohan 108.33%
Ananya 104.44%
Vikas 88.89%
Riya 102.00%
Nitin 87.50%

Performance Category

Formula:

=IF(J2>=4.8,"Outstanding",IF(J2>=4.5,"Excellent",IF(J2>=4,"Good",IF(J2>=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

Bonus %

Formula:

=IF(H2>=60000,15%,IF(H2>=50000,10%,IF(H2>=40000,7%,IF(H2>=30000,5%,0%))))
Employee Bonus %
Aarav 7%
Meera 5%
Rahul 10%
Pooja 15%
Karan 5%
Simran 7%
Mohit 15%
Neha 7%
Arjun 0%
Priya 10%
Rohan 15%
Ananya 7%
Vikas 7%
Riya 10%
Nitin 5%

Bonus Amount

Formula:

=F2*P2
Employee Bonus Amount
Aarav ₹2,450
Meera ₹2,100
Rahul ₹5,000
Pooja ₹6,900
Karan ₹1,900
Simran ₹3,850
Mohit ₹9,300
Neha ₹3,080
Arjun ₹0
Priya ₹5,200
Rohan ₹9,750
Ananya ₹3,290
Vikas ₹2,800
Riya ₹5,100
Nitin ₹1,750

Total Bonus = ₹63,470

Promotion Status

Conditions:

  • Experience >= 5

  • Sales >= Target

  • Attendance >= 90%

  • Rating >= 4.5

Formula:

=IF(AND(G2>=5,H2>=L2,I2>=90,J2>=4.5),"Promotion Recommended","Promotion Not Recommended")

Promotion Recommended

Employee
Rahul
Mohit
Rohan
Ananya

Total = 4

Attendance Status

Formula:

=IF(I2>=90,"Excellent",IF(I2>=80,"Good","Poor"))

Summary

  • Excellent = 9

  • Good = 4

  • Poor = 2

Training Status

Formula:

=IF(K2="Completed","Eligible","Training Required")

Eligible = 11

Training Required = 4

Training Required employees:

  • Karan

  • Arjun

  • Vikas

  • Nitin

PART C – LOOKUP PRACTICE

Q1. XLOOKUP – Employee Name

Formula:

=XLOOKUP(A2,$A$2:$A$16,$B$2:$B$16,"Employee Not Found")
ID Employee
103 Rahul Verma
106 Simran Kaur
110 Priya Sharma
114 Riya Kapoor
115 Nitin Sharma
120 Employee Not Found

Q2. Department

Formula:

=XLOOKUP(A2,$A$2:$A$16,$C$2:$C$16,"Not Found")
ID Department
103 Sales
106 Sales
110 IT
114 Sales
115 HR
120 Not Found

Q3. Salary

Formula:

=XLOOKUP(A2,$A$2:$A$16,$F$2:$F$16,"Not Found")
ID Salary
103 ₹50,000
106 ₹55,000
110 ₹52,000
114 ₹51,000
115 ₹35,000
120 Not Found

Q4. Rating

Formula:

=XLOOKUP(A2,$A$2:$A$16,$J$2:$J$16,"Not Found")
ID Rating
103 4.8
106 4.5
110 4.4
114 4.7
115 3.4
120 Not Found

Q5. ID 120

Answer:

Employee Not Found

Q6. INDEX + MATCH

Formula:

=INDEX(F2:F16,MATCH(111,A2:A16,0))

Answer:

Rohan Mehta – ₹65,000

PART D – SALES ANALYSIS

Q7. Total Sales

Formula:

=SUM(H2:H16)

Answer:

₹7,08,000

Q8. Total Sales – Sales Department

Formula:

=SUMIF(C2:C16,"Sales",H2:H16)

Answer:

₹2,89,000

Q9. Total Sales – Sirsa

Formula:

=SUMIF(D2:D16,"Sirsa",H2:H16)

Answer:

₹2,89,000

Q10. Sales Department + Sirsa

Formula:

=SUMIFS(H2:H16,C2:C16,"Sales",D2:D16,"Sirsa")

Answer:

₹2,89,000

PART E – AVERAGE ANALYSIS

Q11. Average Salary

Formula:

=AVERAGE(F2:F16)

Answer:

₹47,333.33

Q12. Average Salary – Sales Department

Formula:

=AVERAGEIF(C2:C16,"Sales",F2:F16)

Answer:

₹50,666.67

Q13. Average Sales – Attendance >= 90%

Formula:

=AVERAGEIF(I2:I16,">=90",H2:H16)

Answer:

₹53,555.56

Q14. Average Rating – Sales >= ₹50,000

Formula:

=AVERAGEIF(H2:H16,">=50000",J2:J16)

Answer:

4.76 approximately

PART F – COUNT ANALYSIS

Q15. Total Employees

=COUNTA(B2:B16)

Answer: 15

Q16. Employees Who Achieved Target

=COUNTIF(M2:M16,"Achieved")

Answer: 8

Q17. Sales Department Employees

=COUNTIF(C2:C16,"Sales")

Answer: 6

Q18. Sales >= ₹50,000 AND Attendance >= 90%

=COUNTIFS(H2:H16,">=50000",I2:I16,">=90")

Answer: 5

Employees:

  • Rahul

  • Pooja

  • Mohit

  • Rohan

  • Riya

Q19. Sales Department + Rating >= 4.5 + Training Completed

=COUNTIFS(C2:C16,"Sales",J2:J16,">=4.5",K2:K16,"Completed")

Answer: 4

Employees:

  • Rahul

  • Simran

  • Rohan

  • Riya

PART G – SERVICE CALCULATION

Assuming date is 07-10-2026.

Q20. Completed Years of Service

=DATEDIF(E2,DATE(2026,10,7),"y")
Employee Years
Aarav 4
Meera 5
Rahul 7
Pooja 4
Karan 2
Simran 6
Mohit 8
Neha 4
Arjun 1
Priya 5
Rohan 9
Ananya 5
Vikas 3
Riya 4
Nitin 6

Q21. Remaining Months

=DATEDIF(E2,DATE(2026,10,7),"ym")

Examples:

  • Aarav → 4 months

  • Meera → 1 month

  • Rahul → 2 months

  • Pooja → 6 months

  • Karan → 10 months

  • Simran → 3 months

  • Mohit → 8 months

  • Neha → 0 months

  • Arjun → 8 months

  • Priya → 3 months

  • Rohan → 6 months

  • Ananya → 11 months

  • Vikas → 8 months

  • Riya → 4 months

  • Nitin → 9 months

Q22. Service Duration Statement

Formula:

=B2&" has completed "&DATEDIF(E2,DATE(2026,10,7),"y")&" years and "&DATEDIF(E2,DATE(2026,10,7),"ym")&" months of service."

Example:

Aarav Sharma has completed 4 years and 4 months of service.

PART H – TEXT FUNCTIONS

Q23. First Name

=LEFT(B2,FIND(" ",B2)-1)

Examples:

Full Name First Name
Aarav Sharma Aarav
Meera Kapoor Meera
Rahul Verma Rahul
Pooja Singh Pooja
Karan Mehta Karan

Q24. Lowercase

=LOWER(B2)

Example:

Aarav Sharma → aarav sharma

Q25. Length of Employee Name

=LEN(B2)

Examples:

  • Aarav Sharma = 13

  • Meera Kapoor = 12

  • Rahul Verma = 11

  • Pooja Singh = 11

  • Karan Mehta = 11

PART I – TOP & LOWEST SALES

Q26. Highest Sales

=LARGE(H2:H16,1)

Answer:

₹72,000 – Mohit Kumar

Q27. Second Highest Sales

=LARGE(H2:H16,2)

Answer:

₹65,000

Rohan Kumar/Mehta is the employee with ₹65,000 sales.

Q28. Third Highest Sales

=LARGE(H2:H16,3)

Answer:

₹65,000

Because ₹65,000 occurs twice:

  • Rohan = ₹65,000

  • The second ₹65,000 is also the 3rd largest value.

Q29. Lowest Sales

=SMALL(H2:H16,1)

Answer:

₹28,000 – Arjun Verma

Q30. Second Lowest Sales

=SMALL(H2:H16,2)

Answer:

₹30,000 – Karan Mehta

PART J – FINAL DASHBOARD KPIs

KPI Answer
Total Employees 15
Total Sales ₹7,08,000
Average Salary ₹47,333.33
Highest Sales ₹72,000
Lowest Sales ₹28,000
Achieved Targets 8
Not Achieved Targets 7
Average Rating 4.27
Promotion Recommended 4
Training Required 4

Useful Dashboard Formulas

Total Employees

=COUNTA(B2:B16)

Total Sales

=SUM(H2:H16)

Average Salary

=AVERAGE(F2:F16)

Highest Sales

=MAX(H2:H16)

Lowest Sales

=MIN(H2:H16)

Achieved

=COUNTIF(M2:M16,"Achieved")

Not Achieved

=COUNTIF(M2:M16,"Not Achieved")

Average Rating

=AVERAGE(J2:J16)

Promotion Recommended

=COUNTIF(R2:R16,"Promotion Recommended")

Training Required

=COUNTIF(T2:T16,"Training Required")

PART K – CONDITIONAL FORMATTING

Sales >= ₹50,000

Use:

Cell Value >= 50000

Attendance < 80%

Cell Value < 80

Rating >= 4.8

Cell Value >= 4.8

Target Status

  • Achieved → Green

  • Not Achieved → Red

Promotion Status

  • Promotion Recommended → Green

  • Promotion Not Recommended → Red

PART L – FINAL QUESTIONS

Q31. Who has the highest Sales?

Answer:

Mohit Kumar – ₹72,000

Q32. Who has the lowest Sales?

Answer:

Arjun Verma – ₹28,000

Q33. Which department has the highest total Sales?

Department Total Sales
Sales ₹2,89,000
IT ₹2,27,000
HR ₹1,92,000

Answer:

Sales Department – ₹2,89,000

Q34. How many employees achieved their targets?

Answer:

8 Employees

Q35. How many employees are eligible for promotion?

Answer:

4 Employees

They are:

  • Rahul

  • Mohit

  • Rohan

  • Ananya

Q36. How many employees have Rating >= 4.5?

=COUNTIF(J2:J16,">=4.5")

Answer:

9 Employees

Q37. Which employee has the highest Rating?

Answer:

There is a tie:

  • Pooja – 4.9

  • Mohit – 4.9

  • Rohan – 4.9

Highest Rating = 4.9

Q38. Which employee has the highest Salary?

Answer:

Rohan Mehta – ₹65,000

Q39. How many employees have Attendance >= 90%?

=COUNTIF(I2:I16,">=90")

Answer:

9 Employees

Q40. How many employees require training?

=COUNTIF(K2:K16,"Pending")

Answer:

4 Employees

They are:

  • Karan

  • Arjun

  • Vikas

  • Nitin

FINAL PROJECT SUMMARY

Category Result
Total Employees 15
Total Sales ₹7,08,000
Average Salary ₹47,333.33
Average Rating 4.27
Highest Sales ₹72,000
Lowest Sales ₹28,000
Achieved Targets 8
Not Achieved 7
Promotion Recommended 4
Training Required 4
Rating >= 4.5 9
Attendance >= 90% 9
Highest Sales Department Sales
Department Sales ₹2,89,000

🎯 What Students Have Practiced

By completing Assignment 50, students have combined:

IF → Nested IF → AND → OR → IFERROR → COUNTIF → COUNTIFS → SUMIF → SUMIFS → AVERAGEIF → AVERAGEIFS → VLOOKUP → XLOOKUP → INDEX + MATCH → DATEDIF → LEFT → FIND → LEN → LOWER → LARGE → SMALL → NETWORKDAYS → Conditional Formatting → Dashboard

This makes Assignment 50 a suitable Final Excel Practical Project covering the major functions taught throughout the assignment series.

Previous