How to Calculate Median of Filtered Rows in Excel

This guide will explain how to calculate the median of filtered rows in Excel using the AGGREGATE function.

The rules for using the AGGREGATE function in Excel are the following:

• Firstly, the AGGREGATE function is used to return an aggregate calculation such as AVERAGE, COUNT, MAX, etc. Additionally, we can also choose to ignore hidden rows and errors. 
• Secondly, the function has several options for ignoring hidden rows and errors. 
• Thirdly, the function can support many array operations without the control + shift + enter keys.
• If a second function argument is required but is not provided, the function will return a #VALUE! error. 
• However, the function does not support 3D references. 
• Lastly, the function is designed for vertical ranges. And it does not work on horizontal ranges.

Excel is an excellent tool to use for different purposes and situations. Hence, we can easily manipulate, organize, and arrange data using the different built-in functions and tools. Moreover, one versatile tool we can utilize on our data set is the filter feature.

So the filter feature allows us to hide irrelevant rows of data and only display specific rows of data. Furthermore, this would help us focus on the rows of data we actually need. Thus, we will learn how to calculate the median of filtered rows.

Moreover, we can easily get the median of filtered rows using the AGGREGATE function in Excel. However, we cannot use the MEDIAN function directly because it will return the median for the entire data set and not only the filtered rows.

Let’s take a sample scenario wherein we need to calculate the median of filtered rows in Excel.

Suppose you have a sales report containing the number of sales made for different dates of the month. And you want to find the median of the sales made in the months of October and November. So you first filtered out the data set to only display the rows from October and November.

Then, you used the AGGREGATE function to calculate the median of the sales of the filtered rows. 

Before we move on to a real example of calculating the median of filtered rows in Excel, let’s first learn how to write the AGGREGATE function.

The Anatomy of the AGGREGATE Function

The syntax or the way we write the AGGREGATE function is as follows:

=AGGREGATE(function_num, options, array, [k],...)

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we activate any function in Excel.
• AGGREGATE() is our AGGREGATE function. And this function is used to return an aggregate in a list or database.
• function_num is a required argument. So this refers to the number 1 to 19 that will specify the summary function for the aggregate.
• options is another required argument. And this refers to the numbers 0 to 7 that will specify the values to ignore for the aggregate.
• array is also a required argument. So this refers to the array or range of numerical data on which to calculate the aggregate.
• k is an optional argument. And this value will indicate the position in the array. For instance, it can be k-th largest, k-th smallest, k-th percentile, k-th quartile.

Great! Now we can dive into a real example of calculating the median of filtered rows in Excel.

A Real Example Calculating Median of Filtered Rows in Excel

Let’s say we have a data set that shows the number of sales made on different dates for the months of October to December. So our initial data set would look like this:

Furthermore, our goal is to calculate the median of the number of sales made for the months of October and November. Hence, we would first need to filter out the needed rows. Since the data set shows rows for the months of October, November, and December, we would need to hide the rows from December. 

So we can simply go to the data tab and apply a filter to our data set. Then, we would uncheck December to hide the rows and only display the rows from October and November. After filtering out the needed rows, we can now proceed to use the AGGREGATE function to calculate the median. 

Furthermore, the AGGREGATE function can support a total of 19 functions or calculations. So we can refer to the table below for the 19 functions.

Additionally, the function has 7 behavior options for ignoring errors, hidden rows, and other functions. Furthermore, we can refer to the table below.

Thus, we will utilize function 12, which will calculate the median, and behavior option 1, which will ignore hidden rows, SUBTOTAL, and AGGREGATE functions. Otherwise, we can also use behavior option 5, which will still ignore hidden rows. 

So our final data set would look like this:

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can proceed and explain the steps of how to calculate the median of filtered rows in Excel.

How to Calculate Median of Filtered Rows in Excel

In this section, we will explain the step-by-step process of how to calculate the median of filtered rows in Excel using the AGGREGATE function. Additionally, each step has detailed instructions and pictures to help you along the process.

1. Firstly, we need to first filter out the data set to only display the rows we need. Then, we will select the entire data set and go to the Data tab. Next, we will click the Filter icon.

2. Secondly, we will click the dropdown arrow in the Date header column. Then, we will uncheck December. Lastly, we will click Apply.

3. Thirdly, we can now calculate the median. Next, we can simply type in the formula “=AGGREGATE(12,1,C2:C7)”. Lastly, we will press the Enter key to return the result.

4. And tada! We have successfully calculated the median of filtered rows in Excel.

And that’s pretty much it! We have successfully explained how to calculate the median of filtered rows using the AGGREGATE function. Now you can apply this method to your work whenever you need to get the median of filtered rows.

Are you interested in learning more about what Excel can do? You can now use the AGGREGATE function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe