+ Reply to Thread
Results 1 to 10 of 10

MedianIFS possible?

  1. #1
    Registered User
    Join Date
    07-07-2008
    Location
    Tennessee
    Posts
    4

    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. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Did you try using the Auditing Toolbar to watch the formula evaluate?

  3. #3
    Registered User
    Join Date
    07-07-2008
    Location
    Tennessee
    Posts
    4
    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. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    ... 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. #5
    Registered User
    Join Date
    07-07-2008
    Location
    Tennessee
    Posts
    4
    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. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    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. #7
    Registered User
    Join Date
    07-07-2008
    Location
    Tennessee
    Posts
    4
    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. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    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. #9
    Registered User
    Join Date
    06-05-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2007
    Posts
    1

    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. #10
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1