Share via

Excel Returns the average salary of employees who:

Michael Hilland 20 Reputation points
2025-07-27T16:24:24.3533333+00:00

You have the following dataset in Excel (columns A to D):

A B C D
Name Department Join Date Salary
Name Department Join Date Salary
Alice HR 01/03/2020 38000
Bob IT 15/06/2021 42000
Carol Finance 10/01/2019 47000
David HR 22/07/2022 36000
Emma IT 04/09/2020 39000
... ... ... ...

How do i

Returns the average salary of employees who:

Work in either IT or HR

  **Joined after January 1st, 2020**
  
     Have a salary **greater than the average salary** of **all departments**
Microsoft 365 and Office | Excel | For education | Windows
Accepted answer
  1. IlirU 486 Reputation points Volunteer Moderator
    2025-07-27T18:57:02.8366667+00:00

    User's image

    Hi,

    See the screenshot above and apply formula in cell F1. This formula gives to you average for workers for HR and IT departments (both departments). I have used also SORT function to sort Department, Name and Join Date.

    =SORT(GROUPBY(A1:C6, D1:D6, AVERAGE, 3, 0,, (B1:B6 = "HR") + (B1:B6 = "IT")), {2,1,3})

    In case you want result only for one department (let say HR department) then use this formula:

    =GROUPBY(A1:C6, D1:D6, AVERAGE, 3, 0,, (B1:B6 = "HR"))

    Change HR in formula with IT in case you want result for IT department and so on.

    Hope this helps.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462K Reputation points MVP Volunteer Moderator
    2025-07-27T18:50:18.7133333+00:00

    Adjust the ranges as needed:

    =LET(Avg, AVERAGE(D2:D10), Flt, FILTER(D2:D10, ((B2:B10="IT")+(B2:B10="HR"))*(C2:C10>=DATE(2020, 1, 1))*(D2:D10>Avg)), AVERAGE(Flt))
    1 person found this answer helpful.
    0 comments
  2. Michael Hilland 20 Reputation points
    2025-07-27T16:24:53.7133333+00:00

    Excel formula (no helper columns) that:

    Returns the average salary of employees who:

    Work in either IT or HR

      **Joined after January 1st, 2020**
  
     Have a salary **greater than the average salary** of **all departments**
    0 comments
  3. Ashish Mathur 99,595 Reputation points Volunteer Moderator
    2025-08-09T23:35:02.88+00:00

    Hi,

    In cell G2, enter this formula

    =GROUPBY(A2:C6,D2:D6,SUM,0,0,,((C2:C6>=DATE(2020,1,1))((B2:B6="HR")+(B2:B6="IT"))(D2:D6>AVERAGE(D2:D6))))

    Hope this helps.

    User's image

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.