# MedianIFS possible?

1. ## MedianIFS possible?

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!

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

3. 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

4. ... 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.

5. 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.

6. 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) )

7. Excellent. Thanks for your help, SHG. This is a very interesting solution, I've never seen anything like this before. It works (just needed one more parentheses at the end ) BUT...

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

...unfortunately, I can't follow the logic of this formula, so I am still confused as to how to apply this so that the wildcard could be in either File or JobFunction. I think the key to my understanding of what's going on here is if I could see it extended to 3 variables...

e.g.
{=STDEV(IF((File=\$A\$3)*(JobFunction=\$A\$6)*(Industry=\$A\$9),Salary))}

...so that I could use a specific value or a wildcard in any of the 3 variables at any place. I have a feeling this is going to get messy, but if you wouldn't mind explaining this, then I believe I would be able to take over from there.

Thank you again for your help,
Jonathan

8. The wildcard matches anything, so excluding the test is equivalent. Unfortunately, for three conditions, there are eight possible formulas -- so rather than a very long ugly formula, it would be better to sort out what's going wrong with the one simple one.

9. ## Re: MedianIFS possible?

It may be a far less sophisticated solution, but I exploited the fact that MEDIAN doesn't count certain values, and created a range on a hidden sheet that changed the cells not matching the criteria to false and applying the MEDIAN function to that range. For example, suppose I have the following table on Sheet1:

A
1 10
2 55
3 150
4 87

If I want the median of all values < 100, I first create a table on Sheet2 using

if(Sheet1!\$A1<100,\$a1,false)

So on Sheet2 I end up with

A
1 10
2 55
3 FALSE
4 87

Next, simply use the ordinary median funtion

median(Sheet2!\$a\$1:\$a\$4)

and it is if I have been able to use

medianif(Sheet1\$a\$1,"<100")

And it is not difficult to take this the next step to multiple criteria and columns, which effectively gives me a MEDIANIFS function as well

10. ## Re: MedianIFS possible?

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=STDEV(IF((File=\$A\$3)+(\$A\$3="*"),IF((JobFunction=\$A\$6)+(\$A\$6="*"),IF((Industry=\$A\$9)+(\$A\$9="*"),Salary))))

If you're going to add more than seven variables, use the following syntax instead...

=STDEV(IF(((File=\$A\$3)+(\$A\$3="*"))*((JobFunction=\$A\$6)+(\$A\$6="*"))*((Industry=\$A\$9)+(\$A\$9="*")),Salary))

Also, to make it easier for other users to understand, you may want to change the "*" in the drop-down menu to "All". Of course, replace each instance of "*" in the formula with "All".