Sometimes data are grouped into categories. To calculate a sample statistic for a single category (e.g. the median) you need to nest an IF() statement inside the Excel function (e.g. MEDIAN(your_data)), and then execute the cell as an array function (CTRL+SHIFT+ENTER).

Suppose you have an array of data called “VALUE” and each value belongs to a category listed in a column called “CATEGORY”. An easy way to calculate, say, the median for each category is to create a column with your categories (e.g. A, B, C, D) and a column next to it that will contain the median. In the empty cell next to the column, enter your function as:

={MEDIAN(IF(CATEGORY = condition, VALUE))}

The IF() statement checks which values of CATEGORY meet the condition (a given category, e.g. A) and then takes the relevant data from VALUE and uses it to calculate the median. Remember to execute with (CTRL+SHIFT+ENTER). (You will know the cell executed as an array function because in the contents of the cell will the function will be wrapped in curly braces.)

If you freeze the categories and the data within the function using dollar signs, the only moving part will be the condition. Now you can pull down the formula into the remaining empty cells and calculate the medians for the other categories.

Demo