ASSIGNMENT – 44
ANSWER KEY – AVERAGEIF & AVERAGEIFS
Data Range
-
Department →
C2:C16 -
Experience →
D2:D16 -
Sales →
E2:E16 -
Attendance →
F2:F16 -
Performance →
G2:G16
Q1. Overall Average Sales
Formula
=AVERAGE(E2:E16)Answer:
₹47,200
Q2. Average Sales of Sales Department
Formula
=AVERAGEIF(C2:C16,"Sales",E2:E16)Sales Employees:
Aarav ₹45,000
Rahul ₹52,000
Simran ₹48,000
Arjun ₹28,000
Rohan ₹65,000
Riya ₹51,000
Answer:
₹48,166.67
Q3. Average Sales of HR Department
Formula
=AVERAGEIF(C2:C16,"HR",E2:E16)Answer:
₹38,400
Q4. Average Sales of IT Department
Formula
=AVERAGEIF(C2:C16,"IT",E2:E16)Answer:
₹56,750
Q5. Average Sales of Employees with More Than 5 Years Experience
Condition:
Experience > 5
Formula
=AVERAGEIF(D2:D16,">5",E2:E16)Answer:
₹53,166.67
Q6. Average Attendance of Sales Employees
Formula
=AVERAGEIF(C2:C16,"Sales",F2:F16)Sales Employees’ Attendance:
92%, 95%, 90%, 68%, 97%, 94%
Answer:
89.33%
Q7. Average Sales of Excellent Employees
Formula
=AVERAGEIF(G2:G16,"Excellent",E2:E16)Excellent Employees:
| Employee | Sales |
|---|---|
| Aarav | ₹45,000 |
| Rahul | ₹52,000 |
| Pooja | ₹60,000 |
| Mohit | ₹72,000 |
| Rohan | ₹65,000 |
| Ananya | ₹47,000 |
| Riya | ₹51,000 |
Answer:
₹56,000
Q8. Average Sales of Sales Employees with Experience ≥ 5 Years
Conditions:
-
Department = Sales
-
Experience ≥ 5
Formula
=AVERAGEIFS(E2:E16,C2:C16,"Sales",D2:D16,">=5")Matching Employees:
| Employee | Experience | Sales |
|---|---|---|
| Rahul | 7 | ₹52,000 |
| Simran | 6 | ₹48,000 |
| Rohan | 9 | ₹65,000 |
Answer:
₹55,000
Q9. Average Sales of IT Employees with Attendance ≥ 90%
Conditions:
-
Department = IT
-
Attendance ≥ 90%
Formula
=AVERAGEIFS(E2:E16,C2:C16,"IT",F2:F16,">=90")Matching Employees:
| Employee | Attendance | Sales |
|---|---|---|
| Pooja | 96% | ₹60,000 |
| Mohit | 94% | ₹72,000 |
Answer:
₹66,000
Q10. Average Sales of Excellent Employees with Attendance ≥ 90%
Conditions:
-
Performance = Excellent
-
Attendance ≥ 90%
Formula
=AVERAGEIFS(E2:E16,G2:G16,"Excellent",F2:F16,">=90")Matching Employees:
Aarav, Rahul, Pooja, Mohit, Rohan, Ananya and Riya
Answer:
₹56,000
BONUS ANSWERS ⭐
Q11. Average Sales Where Sales > ₹50,000
Formula
=AVERAGEIF(E2:E16,">50000",E2:E16)Answer:
₹59,166.67
Q12. Average Attendance Where Sales ≥ ₹50,000
Formula
=AVERAGEIF(E2:E16,">=50000",F2:F16)Answer:
94.17%
Q13. Average Sales of HR Employees with Attendance ≥ 90%
Formula
=AVERAGEIFS(E2:E16,C2:C16,"HR",F2:F16,">=90")Matching Employees:
| Employee | Attendance | Sales |
|---|---|---|
| Neha | 91% | ₹42,000 |
| Ananya | 93% | ₹47,000 |
Answer:
₹44,500
Q14. Average Sales of IT Employees with Experience > 4 Years
Formula
=AVERAGEIFS(E2:E16,C2:C16,"IT",D2:D16,">4")Matching Employees:
| Employee | Experience | Sales |
|---|---|---|
| Mohit | 8 | ₹72,000 |
| Priya | 5 | ₹55,000 |
Answer:
₹63,500
Q15. Sales Department + Experience ≥ 5 + Attendance ≥ 90%
Conditions:
-
Department = Sales
-
Experience ≥ 5 years
-
Attendance ≥ 90%
Formula
=AVERAGEIFS(E2:E16,C2:C16,"Sales",D2:D16,">=5",F2:F16,">=90")Matching Employees:
| Employee | Experience | Attendance | Sales |
|---|---|---|---|
| Rahul | 7 | 95% | ₹52,000 |
| Simran | 6 | 90% | ₹48,000 |
| Rohan | 9 | 97% | ₹65,000 |
Answer:
₹55,000
FINAL ANSWER SUMMARY
| Question | Function | Answer |
|---|---|---|
| Q1 | AVERAGE | ₹47,200 |
| Q2 | AVERAGEIF | ₹48,166.67 |
| Q3 | AVERAGEIF | ₹38,400 |
| Q4 | AVERAGEIF | ₹56,750 |
| Q5 | AVERAGEIF | ₹53,166.67 |
| Q6 | AVERAGEIF | 89.33% |
| Q7 | AVERAGEIF | ₹56,000 |
| Q8 | AVERAGEIFS | ₹55,000 |
| Q9 | AVERAGEIFS | ₹66,000 |
| Q10 | AVERAGEIFS | ₹56,000 |
| Q11 | AVERAGEIF | ₹59,166.67 |
| Q12 | AVERAGEIF | 94.17% |
| Q13 | AVERAGEIFS | ₹44,500 |
| Q14 | AVERAGEIFS | ₹63,500 |
| Q15 | AVERAGEIFS | ₹55,000 |
QUICK REVISION
AVERAGE
Average of all selected values:
=AVERAGE(E2:E16)AVERAGEIF
One condition:
=AVERAGEIF(C2:C16,"Sales",E2:E16)AVERAGEIFS
Multiple conditions:
=AVERAGEIFS(E2:E16,C2:C16,"Sales",D2:D16,">=5")Remember
AVERAGE → No condition
AVERAGEIF → One condition
AVERAGEIFS → Multiple conditions