ASSIGNMENT – 44

USE OF AVERAGEIF & AVERAGEIFS – PERFORMANCE ANALYSIS

Objective

In this assignment, you will learn how to calculate averages based on one or multiple conditions using:

  • AVERAGE

  • AVERAGEIF

  • AVERAGEIFS

  • Single Criteria

  • Multiple Criteria

  • Comparison Operators

EMPLOYEE PERFORMANCE DATA

Emp ID Employee Name Department Experience (Years) Sales Attendance % Performance
101 Aarav Sales 3 45000 92 Excellent
102 Meera HR 5 38000 88 Good
103 Rahul Sales 7 52000 95 Excellent
104 Pooja IT 4 60000 96 Excellent
105 Karan HR 2 30000 75 Average
106 Simran Sales 6 48000 90 Good
107 Mohit IT 8 72000 94 Excellent
108 Neha HR 4 42000 91 Good
109 Arjun Sales 1 28000 68 Average
110 Priya IT 5 55000 89 Good
111 Rohan Sales 9 65000 97 Excellent
112 Ananya HR 6 47000 93 Excellent
113 Vikas IT 3 40000 82 Average
114 Riya Sales 4 51000 94 Excellent
115 Nitin HR 7 35000 80 Average

QUESTIONS

Q1. Calculate the Overall Average Sales

Find the average sales of all employees.

Function: AVERAGE

Q2. Calculate the Average Sales of Sales Department

Find the average sales of employees working in the Sales department.

Function: AVERAGEIF

Q3. Calculate the Average Sales of HR Department

Find the average sales of employees working in HR.

Function: AVERAGEIF

Q4. Calculate the Average Sales of IT Department

Find the average sales of employees working in IT.

Function: AVERAGEIF

Q5. Calculate Average Sales of Employees with More Than 5 Years Experience

Find the average sales of employees whose experience is greater than 5 years.

Function: AVERAGEIF

Q6. Calculate Average Attendance of Sales Employees

Find the average attendance percentage of employees in the Sales department.

Function: AVERAGEIF

Q7. Calculate Average Sales of Employees with “Excellent” Performance

Find the average sales of employees whose performance is Excellent.

Function: AVERAGEIF

Q8. Calculate Average Sales of Sales Employees with Experience ≥ 5 Years

Conditions:

  • Department = Sales

  • Experience ≥ 5 years

Function: AVERAGEIFS

Q9. Calculate Average Sales of IT Employees with Attendance ≥ 90%

Conditions:

  • Department = IT

  • Attendance ≥ 90%

Function: AVERAGEIFS

Q10. Calculate Average Sales of Excellent Employees with Attendance ≥ 90%

Conditions:

  • Performance = Excellent

  • Attendance ≥ 90%

Function: AVERAGEIFS

BONUS CHALLENGE ⭐

Q11.

Find the average sales of employees with Sales > ₹50,000.

Q12.

Find the average attendance of employees with Sales ≥ ₹50,000.

Q13.

Find the average sales of HR employees with Attendance ≥ 90%.

Q14.

Find the average sales of IT employees with Experience > 4 years.

Q15.

Find the average sales of employees who satisfy all three conditions:

  • Department = Sales

  • Experience ≥ 5 years

  • Attendance ≥ 90%

FORMULA PRACTICE

AVERAGE

Used to calculate the average of all selected numbers.

=AVERAGE(E2:E16)

AVERAGEIF

Used when there is ONE condition.

Syntax:

=AVERAGEIF(criteria_range,criteria,average_range)

Example:

=AVERAGEIF(C2:C16,"Sales",E2:E16)

AVERAGEIFS

Used when there are TWO OR MORE conditions.

Syntax:

=AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2)

Example:

=AVERAGEIFS(E2:E16,C2:C16,"Sales",D2:D16,">=5")

IMPORTANT DIFFERENCE

Function Use
AVERAGE Average of all values
AVERAGEIF Average based on one condition
AVERAGEIFS Average based on multiple conditions

STUDENT TASK

Complete all 15 questions using formulas.

Do not calculate the answers manually.

Use cell references and verify each result carefully.

PreviousNext