MEDIAN IF example
| |

MEDIAN IF tutorial in Excel 2007 and Excel 2010

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.

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

Download “MEDIAN IF example”

MEDIAN-IF-example.zip – Downloaded 4511 times – 9.71 KB

5 Comments

  1. 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.

    1. 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

  2. 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?

    1. 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.