Challenge: Employee Performance and Payroll Analysis with Excel Functions

You are a human resources manager tasked with analyzing employee performance and payroll data. You need to evaluate bonus eligibility, calculate performance ratings, and identify key patterns to optimize employee management.
Assignment:

1. AND and OR Functions;
   Use logical functions to evaluate employee bonus eligibility;

   • AND:
     Check if an employee is eligible for a bonus by meeting both performance and attendance criteria;
     (Use =AND(C2>=90, D2>=95) where C2 is "Performance Score (%)" and D2 is "Attendance Rate (%)." Returns TRUE if both conditions are met);
   • OR:
     Determine if an employee qualifies for special recognition by meeting either a high-performance score or perfect attendance;
     (Use =OR(C2>=95, D2=100) to check if an employee's performance is above 95% or attendance is 100%);

2. IF and Nested IF;
   Make conditional decisions based on employee data;

   • IF:
     Assign a "Bonus" label if eligible, otherwise "No Bonus";
     (Use =IF(AND(C2>=90, D2>=95), "Bonus", "No Bonus").);
   • Nested IF:
     Classify performance levels as "Excellent" (>=95), "Good" (80-94), or "Needs Improvement" (<80);
     (Use =IF(C2>=95, "Excellent", IF(C2>=80, "Good", "Needs Improvement")).);

3. IFS Function;
   Simplify multiple conditions into a single function;

   • Performance Rating:
     Use IFS to categorize ratings: "Outstanding" (>=98), "Above Average" (90-97), "Average" (70-89), and "Below Average" (<70);
     (Use =IFS(C2>=98, "Outstanding", C2>=90, "Above Average", C2>=70, "Average", C2<70, "Below Average").);

4. SUMIFS, COUNTIFS, AVERAGEIFS;
   Analyze payroll and performance data using multiple criteria;

   • SUMIFS:
     Calculate total salary of employees with performance scores above 90 and attendance above 95;
     (Use =SUMIFS(F2:F9, C2:C9, ">90", D2:D9, ">95") where column F is "Salary");
   • COUNTIFS:
     Count the number of employees eligible for a bonus;
     (Use =COUNTIFS(C2:C9, ">=90", D2:D9, ">=95").);
   • AVERAGEIFS:
     Calculate the average salary for employees rated "Good" or "Excellent";
     (Use =AVERAGEIFS(F2:F9, C2:C9, ">=80", C2:C9, "<=95").);