𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this Excel problem.

You have the following data:

Employee Department Salary

John IT 75,000
Sarah HR 60,000
Mike IT 82,000
David Finance 90,000
Alice HR 65,000


Find the employees whose salary is above the average salary of their department.

𝗠𝗲: Challenge accepted! 💪

=C2>AVERAGEIF(B2:B6,B2,C2:C6)


💡 Explanation:

The formula compares each employee's salary with the average salary of their own department.

• AVERAGEIF() calculates the average salary for the employee's department.
• B2 identifies the current employee's department.
• C2 is the employee's salary.

The formula returns TRUE when the employee earns more than their department average.


🎯 Expected Output Example

Employee Department Salary Above Dept. Average?

John IT 75,000 FALSE
Sarah HR 60,000 FALSE
Mike IT 82,000 TRUE
David Finance 90,000 FALSE
Alice HR 65,000 TRUE


🚀 Bonus — Return the Employee Name Only

In Excel 365:

=FILTER(
A2:A6,
C2:C6>AVERAGEIF(B2:B6,B2:B6,C2:C6)
)

This returns the employees whose salaries are above their respective department averages.


❤️ React with ❤️ for more Excel interview challenges!