MODEIF and MEDIANIF (conditional mode and median)

There's not an inbuilt function to cater for the conditional mode or median. So we need to use an array function.

=MODE(IF($A:$A=D1,$B:$B))

If you have supplier names in column A and their delivery lead times in column B, the above formula finds out what the mode of those delivery times is for the supplier referenced in cell D1. Change MODE to MEDIAN with obvious results.

Because the formula itself references an array, you'll need to hit CTRL+SHIFT+ENTER instead of just ENTER when you've finished writing the formula. Excel will put some lovely curly brackets around the formula for you, reminding itself that arrays are contained therein.

Happy days.

This entry was posted in How to. Bookmark the permalink.

16 Responses to MODEIF and MEDIANIF (conditional mode and median)

  1. Hi Dan, found your website really helpful, great stuff. I'm trying to
    construct a COUNTIF formula that looks at two columns & counts the nbr of duplicates. Problem is i'm trying to get the formula to only count the duplicates total in the column B if text in column A is "X".
    Example:

    X 12345
    X 56789
    X 12345
    X 11111
    X 22222
    X 12345
    Y 22222
    Y 22222
    Y 33333
    Y 44444
    Any ideas would be greatly apprecaited.
    John

  2. Frank says:

    I have a sample table as below and want to calculate the Average, Mode, Min, Max, and Median.

    I entered each of the formulas using CTRL+SHIFT+ENTER:

    =AVERAGE(IF(Table2[Column1]= 2, Table2[Column2]))
    =MIN(IF(Table2[Column1]= 2, Table2[Column2]))
    =MAX(IF(Table2[Column1]= 2, Table2[Column2]))
    =MEDIAN(IF(Table2[Column1]= 2, Table2[Column2]))
    =MODE(IF(Table2[Column1]= 2, Table2[Column2]))

    Using a C+S+E, a pair of braces were placed around the entire formula(s)

    All of them except Mode return the correct value. Mode returns a #N/A. Removing the {} from the Mode returns a #VALUE.

    What am I not seeing.

    Excel version is 2007

    Column1 Column2
    1 5
    2 10
    3 25
    1 50
    4 125
    4 75
    3 150
    2 175
    2 200

  3. bob says:

    Hi Frank, I need some help on this one please.
    I want to construct a formula to give me a value in a cell if a condition is met, the conditions are:-

    If the value in cell C1 is greater than cell D2 then E2 should be a positive value and if C1 is less than D2 the E2 should also give a positive value. It has to be a positive value so that other cells on the sheet work correctly.
    At the moment I get a - value or a + value, any Ideas?

  4. Maria says:

    I work with a hospital system that retrieves multiple data (28 facilities). I have three columns - column A (28 different facilities), column B (various discharge dispositions), Column C (LOS in Minutes) I am trying to capture the median LOS in Minutes when I filter on a particular hospital showing all discharge dispositions

    For example:
    Col A Col B Col C
    Hosp A Routine Discharge 150
    Hosp A Admit to Inpatient 99
    Hosp A Transferred to Beh Health 360
    Hosp B Routine Discharge 75
    Hosp B Routine Discharge 88
    Hosp B Admit to Inpatient 80
    Hosp B Admit to Inpatient 101
    Hosp B Transferred 55
    Hosp B Transferred 99
    Hosp C Expired 75
    Hosp C Admitted 66

  5. Jeff says:

    Can you also use this same approach to create multiple conditions like the AVERAGEIFS function allows you to do? If I replace the IF with IFS in the example you gave when producing a MEDIAN will that work for me to your knowledge?

  6. Richard Moore says:

    Good morning Dan

    Do you know if Median works with the new IFS function in Excel 2016?

    Look forward to hearing from you.

    Kind regards

    Richard

  7. Kenneth Christopher says:

    Hey is there a formula to calculate median if the mode formula returns #NA as value. Or selects the value from the neighboring cell?

  8. Aaron says:

    I need some advice. I am trying to track performance metrics for my team of case managers. Part of our performance is based on the median earnings during the program year. I can manage an equation that counts the median earnings of the entire case load (thanks to your equation above) but I am having trouble constraining the results to a time frame. This is what I have so far:
    =(countifs(Dashboard!$B:$B,"CaseManager",Dashboard!$T:$T,">0",Dashboard!$J:$J,">="&$M$1,Dashboard!$J:$J,"<="&$N$1)) B is the case manager name T is the median earnings J is their date of program exit M is the beginning of the program year and N is the end The results are dumped onto a "summary" page The problem is that, no matter the earnings, the equation returns 1 for each entry that it counts instead of the median. I'm doing this through Google Sheets. I'm willing to accept that it is not robust enough to handle this operation. Thank you for any help

    • Aaron says:

      I apologize, I grabbed the wrong equation from my spreadsheet and I cannot see a way to delete my prior post.

      This is the formula that is giving me trouble:
      =ArrayFormula(median(COUNTIFS(Dashboard!$B:$B,"CaseManager",Dashboard!$V:$V,">0",Dashboard!$J:$J,">="&$M$1,Dashboard!$J:$J,"<="&$N$1)))

      • Dan says:

        I don't think you'd want a COUNTIFS here, simply an IF(AND(...

        But I can't get this to work with multiple arguments. Sorry I can't be more helpful.

        • Aaron says:

          Thank you for looking into it for me. I have a fairly quick method I can use to calculate the medians I need manually so it's not a big loss. I just can't generate the information real-time. It's all part of the learning process.

          • Dan says:

            No worries. I'm wondering whether you may be able to use some form of RANK function to get the rankings of the values that can then be used to find the halfway point.

  9. JC says:

    Hello Dan,

    I am having trouble figuring out how to do a conditional median.

    Specifically, I have three cells of which I need the median (say, A, B, and C), but if any one of those cells are empty because of lack of data, I need to pull a median from a fourth cell (say, D). Cell D becomes a consideration, if and only if one of cells A, B, or C are missing.

    Typically cell B is the missing cell, so even if a formula only considered B as the "if," I'd be grateful.

    My goal is either to have the cell containing the median automatically highlight, or for it to calculate in a 5th cell, (say, E).

    Any help or direction would be appreciated.

    Thank you,
    JC

Leave a Reply

Your email address will not be published. Required fields are marked *