medianifs in Microsoft Excel 2010

Recent versions of Microsoft Excel have the averageifs() and countifs() functions, but no medianifs().  I like computing the median value of a set of values, with some criteria filtered in.  Averageifs() can be affected by large outliners.  Searching Google for medianifs yields several suggestions –  for example  http://www.excelforum.com/excel-worksheet-functions/649086-medianifs-possible.html, this posting is just a summary of my findings.  The key to creating a medianifs() equivalent is entering it as an array function.  Using Excel 2010,  I entered the values in columns a – c.  The formula for median was entered in D2. Since there are an even number of values, the median is the average of the middle two values (5+6)/2=5.5.  You can also get the value by enter the same formula, but instead of pressing the enter key, press the <control><shift><enter> keys simultaneously (all three keys at once otherwise it won’t be treated as an array function).  Note the { and } brackets around the formula.  In D4, by adding the IF formula, you can select the rows you want, in this case I added a restriction of only those values in column a whose value in column B are 3. After typing in the formula displayed in E4, and pressing <control><shift><enter>, you should get 8.5.  Note, the number of elements in “B” matches the number in “A” (B2:B11 = 10 elements, A2:A11 = 10 elements).  You can combine multiple criteria as ‘AND’s by multiplying them together in the if() clause as seen in cell E5 below, where I am finding the median of those cells in A where the corresponding value in B is 3 and the corresponding value in C is 1.

a

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: