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.

29 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

  10. Prerona says:

    Hi, I need some help here. I'm trying to find the mode of one column, but only if a condition in a different column is met. Here's an example:

    x 3
    x 6
    y 5
    x 3
    y 3
    x 5
    y 6
    x 3
    y 3
    x 5
    x 6
    x 3
    y 6
    y 3
    x 5

    I need to find the mode of values in the second column, but only if the corresponding cell in the first column has an x in it. Please help!

    • Dan says:

      You need the following forumula:

      =MODE(B:B*IF(A:A="x",1,RAND()))

      When you've typed it, hit CTRL+Shift+Enter to make it an array formula.

      This basically creates a temporary value of the value multiplied by 1 (which equals the value) if there's a x in column A. Or creates a random number if not. And takes the mode of those values.

      The reason I've used a random number is to make sure that the numbers are all different. If I used a zero, then you would have lots of zeroes, and the mode would therefore be zero. By making it a random number for all non-x values, it means that they'll only appear once (hopefully) and so it will create a mode of only the whole numbers associated with the x entries.

      Hope that helps.

      Dan.

  11. Ulysse says:

    Hi there,
    I need advice on how to pull the median along with other data elements. So for example, I want to median salary along with location, is that possible to do?

  12. Andrew says:

    Hi There,
    Struggling with an excel formula...
    How would you extract the mode of a range based on meeting an identifying criteria within a large range?

    • Dan says:

      The simplest way to do this is to create a secondary column that contains the value if meets the criteria, or blank otherwise. And do a mode of that.
      So the secondary column is =IF(A2>5,A2,"")
      And then do a mode of that.

  13. Shiraz says:

    Hi Dan
    I have a 2 columns. 1 with stockcodes and the other with a length. The code are remain the same and the length keep changing. What formula can we used to pull out the most common used lenght. Please advise

    • Dan says:

      It's not clear what you're needing, Shiraz. If you want to know the most common length, then it's just MODE(B:B) if your lengths are in column B.

  14. Ryan Dull says:

    Hi Dan,

    I want my formula to calculate the mode but if there is no mode to return the max value. Below is the formula I'm using.

    =if(iferror(MODE(H27,J27,M27,O27),MAX(H27,J27,M27,O27))=0,"",iferror(MODE(H27,J27,M27,O27),MAX(H27,J27,M27,O27)))

    The data in the 4 cells is 2, 2, 3, 3. Since there is no mode, or that there are 2 modes, I want 3 to be returned since it is the max.

  15. Eric Reed says:

    Dan,
    In sheets , in column A will be a cost of between 0-100, 101- 200, 201-300, 301-500, 501-1000 and 1001 - ? I will add to that in order 50%,40%,30%,25%, 15%,and10% in column B I want the total,
    Can you help please with a formula.

  16. Michael says:

    I'm looking for a way to find the median of a mid-range of values. I want to leave out a set of lower values, say <200, and also leave out higher values, >600. In other words, median of values between 200 and 600, for example.

  17. James Childers says:

    I am attempting to identify the mode of column C, if column Y is >= 775 and column Y is <=825. =MODE('Raw Data'!C2:C10000, IF( 'Raw Data'!$Y$2:$Y$10000 >="775", 'Raw Data'!$Y$2:$Y$10000 <="825")) and =MODE(IF('Raw Data'!F2:F10000, 'Raw Data'!$Y$2:$Y$10000 >="775", 'Raw Data'!$Y$2:$Y$10000 <="825")) I know this is not correct, but am struggling to identify the solution.

  18. Emily says:

    Hi there,

    I am using this formula and it works great! However, I am having one minor issue that is skewing the results of the formula. I have several 0 values in my data that are accurate, but I also have several that are null values and this is also valid. This formula seems to count the null values as zeros and this results in the formula not producing the correct results. Any ideas?

    Thank you!

  19. Mimi says:

    Hello, Dan. I have used Excel to find the mode within entries in several columns of data. However, I am trying to find a way to utilize Excel to calculate the 2nd most frequently occurring data entries, 3rd most frequently occurring data entries, 4th most frequently occurring data entries, and so forth. I have attempted to use the mode function while excluding the mode value to find the 2nd most frequently occurring value but failed. HELPPPPP!!! Thank you.

Comments are closed.