The following step-by-step example shows how to calculate the median value by group in Excel.

Related:

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:

The following tutorials explain how to perform other common tasks in Excel: