Hello,

I have a database of employees with 7 different variables [Year, JobFunction, PreviousIndustry, PreviousRegion, Degree, Experience, CollegeMajor] of which I would like to be able to select any number of and find their median/max/min/etc salary.

For example, I may need to know the median salary of all employees who started in Year 2006, who work in accounting, and have an MBA.

------------------------------------------------------------------

Rather than copy and paste my 7 variable AVERAGEIFS function, I'm just going to use a two variable example here to keep things simple.

I had no trouble doing what I need with this 2 variable AVERAGEIFS function like so;

{=AVERAGEIFS(Salary,File,$A$3,JobFunction,$A$6)}

...where Salary, File and JobFunction are the names of a cells in another worksheet and $A$3 is a drop down menu that I can select the File Year and $A$6 is a drop down menu where I can select the JobFuction.

Now I would also like to have the same kind of conditional "IFS" filtering with the MEDIAN function, but I have been unable to figure it out since Excel doesn't have a MEDIANIFS function. This is what I've tried so far;

{=MEDIAN(IF(AND(File=$A$3,JobFunction=$A$6),Salary))}

but this just returns "0" which obviously means I'm doing something wrong, can anyone help me out here?

Note: I was able to get it to work with just a single variable just fine like so;

{=MEDIAN(IF(File=$A$3,Salary))}

but trying to add the second [and eventually 3rd, 4th, 5th, 6th, and 7th] conditions is the problem.

Thank you!

## Bookmarks