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.

## medianifs in Microsoft Excel 2010

Advertisements

## Leave a Reply