The following step-by-step example shows how to calculate the median value by group in Excel.
Related: How to Calculate the Average by Group in Excel
Step 1: Enter the Data
First, let’s enter the following dataset that shows the total points scored by various basketball players:
Now suppose we’d like to find the median value of the points scored, grouped by team.
To do so, we can use the UNIQUE() function to first create a list of the unique teams.
We’ll type the following formula into cell E2:
=UNIQUE(B2:B12)
Once we press Enter, a list of unique team names will be displayed:
Next, we can use the following formula to find the median value of points scored by players on each team:
=MEDIAN(IF($B$2:$B$12=E2, $C$2:$C$12))
We’ll type this formula into cell F2 and copy and paste it down to each remaining cell in column F:
Column E displays each of the unique teams and column F displays the median value of the points scored by each team.
From the output we can see:
- Median value of points for Lakers players: 16
- Median value of points for Mavericks players: 25
- Median value of points for Spurs players: 30.5
We can verify these results are correct by manually calculating the median for one of the teams.
For example, the points scored by players on the Spurs team is: 14, 28, 33, 35
The median value would be the value directly in the middle of 28 and 33, which is 30.5.
This matches the value calculated using the formula.
Related: How to Perform a Median IF Function in Excel
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate a Five Number Summary in Excel
How to Calculate the Mean and Standard Deviation in Excel
How to Calculate the Interquartile Range (IQR) in Excel