0

Unfortunately, excel doesn't allow for median calculation in pivot tables so I am trying to do something similar with a formula.

For my example, I have 5 columns of data (A-E).

My columns A & B have different data classifying variables. Column E has the data for which I need the median. However, I want to be filtering to certain values from A & B to calculate the median for the rows with those filtered values only (like I would in a pivot)

Anyone know the best formula that by saying when A=x and when B=y, calculate the Median of column E. So only for the rows that A=x and B=y holds true. I have tried a few IF formulas but they don't seem to give the correct response.

thank you!

migrated from stats.stackexchange.com Nov 17 '15 at 21:41

This question came from our site for people interested in statistics, machine learning, data analysis, data mining, and data visualization.

1

From the help files for MEDIAN, we see that "If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included." So one solution would be (let's say in column F) to set an IF formula that returns the value in column E if the criteria are met, and "" or similar if the criteria are not met. Then just take the median of column F.

Suppose I want to know the median cuteness only of brown-eyed dogs:

Median only of cells meeting criteria

Which gives the required result:

Excel results for MEDIAN only of cells meeting criteria

This is not a brilliant solution, but it's a workable one. You get to see very explicitly which cells have been selected, so you can see immediately if you've used the right IF, but the downside is you might prefer not to have a column used in this way.

The "smarter" method is to use an array formula, but this doesn't give you such a visual check on which cells you selected:

=MEDIAN(IF((A2:A9="Dog")*(B2:B9="Brown"),E2:E9,""))

Remember to press CONTROL-SHIFT-ENTER to finish entering the array formula; the enter key alone is not enough. The IF in the array formula effectively selects only those cells in E2:E9 that match the criteria, and the MEDIAN will then only apply to the cells you want. This means you can then do something like this to get a table of medians according to various criteria:

Table of Excel medians according to different criteria

Take care with the absolute and relative cell references to make it easier to drag the formula! For instance the array formula in H2 should be as follows; note that in H$1 the row is fixed but the column will vary as you drag, and the reverse for $G2.

=MEDIAN(IF(($A$2:$A$9=H$1)*($B$2:$B$9=$G2),$E$2:$E$9,""))

This gives a handy table of results.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy