median IF in excel

Question

looking for similar option for conditional average : =AVERAGEIF(A:A,A2,B:B) but this time for Median in excel. I want to generate Median per IDs in column 1 for large number of subjects

thanks

score 3 · Answer 1 · answered Oct 14 '19 at 14:30

3

Try this formula:

=MEDIAN(IF(A:A=A2,B:B))

This is an array formula, which means you will have to enter it by pressing Ctrl + Shift + Enter.

answered Oct 14 '19 at 14:30

riskypenguin

2,115
1
7
22

2

Please consider accepting the answer, to close the question and make it easier for other users to find. – riskypenguin Oct 14 '19 at 14:45

score 1 · Answer 2 · answered Oct 14 '19 at 16:08

Just for fun, here is another solution, for educational purposes only.

Far more manual, but does avoid CSE array formula.

=IF(
    ISODD(COUNTIFS($A$2:$A$9,$D2)),
    SUMPRODUCT(
        SMALL($B$2:$B$9*($A$2:$A$9=$D2), (COUNT($B$2:$B$9)-COUNTIFS($A$2:$A$9,$D2))+(COUNTIFS($A$2:$A$9,$D2)+1)/2)
    ),
    SUMPRODUCT(
        (
            SMALL($B$2:$B$9*($A$2:$A$9=$D2), (COUNT($B$2:$B$9)-COUNTIFS($A$2:$A$9,$D2))+(COUNTIFS($A$2:$A$9,$D2)/2))
            +
            SMALL($B$2:$B$9*($A$2:$A$9=$D2), (COUNT($B$2:$B$9)-COUNTIFS($A$2:$A$9,$D2))+(COUNTIFS($A$2:$A$9,$D2)/2)+1)
        )/2
    )
)

The IF checks if there are an odd number of values in the group we are interested in, because the median is calculated differently for an odd vs even number of values.

We can use SMALL to find the n'th smallest number. We can use $B$2:$B$9*($A$2:$A$9=$D2) to set all the values in groups that we are not interested in, to zero.

To get the count of values, not in the group that we are interested in, we can use COUNT($B$2:$B$9)-COUNTIFS($A$2:$A$9,$D2).

The we just need to add half the count of the values in the group which we are interested in: COUNTIFS($A$2:$A$9,$D2)/2.

If there is an odd number of values in the group we are interested in, that is us done. However, if there is an even number of values, we have to take the average of the middle two numbers. This is done by adding this SMALL formula to another SMALL formula with n+1:(COUNTIFS($A$2:$A$9,$D2)/2)+1 and dividing by 2.

Because we are dealing with arrays, we need to wrap these formulas in SUMPRODUCTS.

As I say, it is probably not worth you using this solution as it is significantly more complicated than M.Schalk's answer, but thought I would share anyway.

median IF in excel

2 Answers2