MedianIFS possible?

[Jonny323]

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!

[shg]

Did you try using the Auditing Toolbar to watch the formula evaluate?

[Jonny323]

Yes I did look through the formula auditor and I'll be honest, I'm not sure I understand exactly what it was saying, nor is it easy to read that tiny little window when my list is 2000 cells long. Regardless, I've made progress but now have a new issue.

I spoke with a colleague of mine and he helped me figure out a format that actually works (with only one problem);

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

{=MAX(IF((File=$A$3)*(JobFunction=$A$6),Salary))}

{=STDEV(IF((File=$A$3)*(JobFunction=$A$6),Salary))}

All of these work perfectly AS LONG AS I have a specific value for each variable [file and jobfunction], but say I'm interested in the median salaries of all accountants regardless of the File Year they are in- this particular way of writing the formula always returns a "#NUM" error when I try to input a * into the File field.

Is there another way to write the formula so that it can handle wildcards without errors?

Maybe I need a different kind of wildcard? Is there some way to create a list and have a 'any of the above' or something like that? As it is now, I have a drop down list created through data validation that looks like this for File...

2003
2004
2005
2006
2007
*

Selecting the "*" works just fine with the =AVERAGEIFS function [obviously.../sigh] but returns an error with any of the other formulas. Anyone know why?
Any help is appreciated.

Thanks,
Jonathan

[shg]

... nor is it easy to read that tiny little window when my list is 2000 cells long

That's masochistic; don't try to debug array formulas with that much data; limit it to just enough to test possible error conditions.

Selecting the "*" works just fine with the =AVERAGEIFS function [obviously.../sigh] but returns an error with any of the other formulas.

Not offhand, but try using formula auditing with a small portion of the data.

You could also use Autofilter or Advanced Filter instead of the formulas.

[Jonny323]

Unfortunately, I can't use Autofilter or Advanced filter because I need this spreadsheet to be simple enough for people who are not familiar with Excel to use easily.

I'll create a new sheet with a smaller range and see if I can use the auditing tools to figure out the problem.

[shg]

All of those functions ignore text and logical values, so something else is going on.

If you want a hack, you could do something like this:

=IF( A3 = "*", STDEV(IF( (JobFunction = $A$6), Salary) ), STDEV(IF( (File = $A$3) * (JobFunction = $A$6), Salary) )