Advertisements

Techtites

Byte-sized bites of technology

Excel, Techtites Featured, Tips and Tutorials

MEDIAN IF tutorial in Excel 2007 and Excel 2010

MEDIAN IF example

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

Download “MEDIAN IF example” MEDIAN-IF-example.zip – Downloaded 1480 times – 10 KB

(Visited 26,168 times, 56 visits today)
Advertisements

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

  2. Oanh

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

  3. Mel

    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

      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

Theme by Anders Norén