Advertisements

Excel 2007 brought a host of new functions to Excel that were missing in Excel 2003. One of these functions was AVERAGEIF which returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. One essential function that is still missing is MEDIANIF or MEDIAN IF which should ideally return the median of all the cells in a range that meet a given criteria.

However, with a little bit of array formula magic, you can easily create your own MEDIAN IF function. Here’s how you go about it.

You might want to get hold of the excel file from the bottom of this post to get hold of the excel data that I will be using in this example.

The problem

Once you’ve opened up the excel file, you’ll note the following series.

MEDIAN IF example

The series is the 2012 real GDP growth of countries in Central and Eastern Europe and Emerging Asia as per IMF’s World Economic Outlook Database, April 2013.

If you’d like to calculate the Median GDP growth rate for CEE or Asia, you can go about it the manually painful way of selecting each cell individually, something like:

=MEDIAN(D5,D8,D10,D13,D15:D16,D20,D22:D23,D29,D34:D35,D37,D43)

As you can see this is totally cumbersome to use. Firstly, you need to manually select each sell. If you have larger sets of data, you’ll probably spend the night in office finding the MEDIAN!

It’s not flexible, since the moment you add or remove line items or reorder them, your formula breaks.

MEDIAN IF – The solution

MEDIAN IF isn’t a function in Excel yet. But, using a combination of MEDIAN and IF, you can easily create your very own MEDIAN IF formula, without any VBA.

MEDIAN IF syntax

The syntax is simple:

{=MEDIAN(IF(range=criteria,median_range))}

It is the IF function nested within the MEDIAN function. The reason I show the curly brackets above is because this is how Excel displays array formulae.

Range is one or more cells to average, including numbers or names, arrays, or references that contain numbers.

Criteria is the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, or B4.

Average_range is the actual set of cells to average. If omitted, range is used.

e.g. to get the CEE Median in our example file, we use the following formula. Note that this is an array function. In order to enter the formula you need to hit CTRL+SHIFT+ENTER on your keyboard instead of just ENTER.

=MEDIAN(IF(B$5:B$46=F12,D$5:D$46))

Download MEDIAN IF example excel file

MEDIAN IF example (2580 downloads)
Advertisements

5 Comments

Joe Manausa · March 10, 2015 at 10:16 pm

I understand your example. Is it possible to nest the if … using your example add 1 more column so that the criteria is determined upon 2 variables, how would that be done? My problem is going through sales data with dates of each sale and then determining median values by month for specific types of sales.

    Michael · July 18, 2017 at 6:31 pm

    For 2 (or more columns), you can do this as:

    {=MEDIAN(IF((range2=criteria2)*(range2=criteria2),median_range))}

    For example, by Month (Column A) and Region (Column B) in data that looks like

    A B C D
    Month Region Salesperson Sales
    Jan A 1 1000
    Jan A 2 2000
    Jan A 3 3000
    Jan B 1 10,000
    Jan B 2 20,000
    Jan B 3 30,000

    Calculating medians beside your data (shown above)
    F G H
    1 Month Region Median Sales
    2 Jan A {=MEDIAN(IF((A:A=F2)*(B:B=G2),D:D))} gives 2000
    3 Jan B {=MEDIAN(IF((A:A=F3)*(B:B=G3),D:D))} gives 20,000

Oanh · October 25, 2015 at 9:39 pm

Thanks, your suggested formula is really useful and save time!

Mel · December 6, 2016 at 10:36 pm

HOW DO I CALCULATE median over an nonoverlapping range of values? for rows
meeting the IF condition?
Say AGE is column A
Calculate median over say B3:D100, F3:H100 for all people whose age is => 30?

    Ajay · December 6, 2016 at 11:02 pm

    There isn’t an easy way as the IF function can only take a range at a time. One thing worth trying is too pass a similar IF as above as a second argument for the MEDIAN.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.