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.


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.

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

    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.

  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


  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?

Leave a Reply

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