Table of Contents
To calculate the median of filtered rows in Excel, first filter the rows you want to include in the median calculation, then use the MEDIAN function to find the median of the filtered data. The MEDIAN function takes the range of the filtered data as an argument, so you can simply drag the fill handle of the formula to the right or down to select the filtered data range. This will give you the median of the filtered rows in Excel.
You can use the following syntax to calculate the median value of a filtered range in Excel:
=AGGREGATE(12,1,B2:B13)
The value 12 is a for calculating the median of a range and the value 1 tells Excel to ignore hidden rows.
The following example shows how to use this function in practice.
Example: Calculate Median of Filtered Rows in Excel
Suppose we have the following dataset that shows the number of sales made during various days by a company:
Next, let’s filter the data to only show the dates that are in January or April.
To do so, highlight the cell range A1:B13. Then click the Data tab along the top ribbon and click the Filter button.
Then click the dropdown arrow next to Date and make sure that only the boxes next to January and April are checked, then click OK:
The data will automatically be filtered to only show the rows where the dates are in January or April:
If we attempt to use the MEDIAN() function to find the average value in the Sales column, it will actually return the median of all of the original values:
Instead, we can use the AGGREGATE() function:
This function calculates the median of only the visible rows.
We can manually verify this by calculating the median of the visible rows:
Median of Sales in visible rows: [9, 13, 14, 14, 19]
When the values are arranged in ascending order, the value located in the middle is indeed 14.