How to perform a Median IF function in Excel?

By /

You can use the following formula to perform a Median IF function in Excel:

=MEDIAN(IF(GROUP_RANGE=VALUE, MEDIAN_RANGE))

This formula finds the median value of all cells in some range that belong to a certain group.

When you type this formula into a cell in Excel, you need to press Ctrl + Shift + Enter since this is an array formula.

The following example shows how to use this function in practice.

Example: Median IF Function in Excel

Suppose we have the following dataset that shows the total points scored by 15 different basketball players:

Now suppose we’d like to find the median of the points scored by players on each 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 F2:

=UNIQUE(B2:B16)

Once we press enter, a list of unique team names will be displayed:

Next, we can use the =MEDIAN() function to find the median number of points scored by players on each team.

We’ll type the following formula into cell G2 and press Ctrl + Shift + Enter so Excel knows this is an array formula:

=MEDIAN(IF(B2:B16=F2, C2:C16))

That’s it!

Column F displays each of the unique teams and column G displays the median of the points scored by players on each team.

x

Related terms:

  1. How to Perform a Median IF Function in Google Sheets?
  2. How to Perform Mood’s Median Test in R
  3. How can I use the IF Function with the LEFT Function in Microsoft Excel?
  4. How to add median line to chart in Excel?
  5. How to Calculate Median of Filtered Rows in Excel
  6. How do I calculate the median in an Excel pivot table?
  7. How to find mean, median & mode in Excel (with examples)?
  8. How to Calculate Median Absolute Deviation in Excel
  9. How to Perform a Percentile IF Function in Excel
  10. The SUBSTR Function in SAS (With Examples) How to use the SUBSTR function in SAS?