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.

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.

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))`

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.

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

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

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?

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.