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