Intro
Discover how to average only filtered cells in Excel with these 4 expert techniques. Learn to use AutoCalculate, SUBTOTAL function, and pivot tables to calculate averages of visible cells. Improve your data analysis skills and boost productivity with these Excel tips and tricks for working with filtered data.
Averaging only filtered cells in Excel can be a bit tricky, but there are several ways to achieve this. Whether you're working with a small dataset or a large one, being able to calculate averages based on filtered data is a useful skill to have. In this article, we'll explore four different methods to average only filtered cells in Excel.
When working with datasets in Excel, you often need to analyze data that meets specific criteria. Filtering your data is a great way to narrow down your focus, but when it comes to calculating averages, the traditional AVERAGE function may not give you the results you're looking for. This is because the AVERAGE function calculates the average of all cells in a range, regardless of whether they're filtered or not.
Fortunately, there are several workarounds to this problem. Let's dive into four different methods to average only filtered cells in Excel.
Average Only Filtered Cells Using the SUBTOTAL Function
One of the most straightforward ways to average only filtered cells is by using the SUBTOTAL function. This function ignores any hidden rows, making it perfect for working with filtered data.
To use the SUBTOTAL function, follow these steps:
- Select the cell where you want to display the average.
- Type
=SUBTOTAL(1,followed by the range of cells you want to average. - Close the parentheses and press Enter.
For example, if you want to average the values in cells A1:A10, you would use the following formula:
=SUBTOTAL(1, A1:A10)
The number 1 in the formula tells Excel to calculate the average. If you want to calculate a different type of aggregation, such as the sum or count, you can use a different number. Here's a list of the numbers you can use with the SUBTOTAL function:
- 1: Average
- 2: Count
- 3: CountA
- 4: Max
- 5: Min
- 6: Product
- 7: StDev
- 8: StDevP
- 9: Sum
- 10: Var
- 11: VarP
How to Use the SUBTOTAL Function with Multiple Ranges
If you want to average values in multiple ranges, you can use the SUBTOTAL function with multiple arguments. For example:
=SUBTOTAL(1, A1:A10, C1:C10)
This formula averages the values in cells A1:A10 and C1:C10.
Average Only Filtered Cells Using the AVERAGEIF Function
Another way to average only filtered cells is by using the AVERAGEIF function. This function averages only the cells that meet a specific condition.
To use the AVERAGEIF function, follow these steps:
- Select the cell where you want to display the average.
- Type
=AVERAGEIF(followed by the range of cells you want to average, the criteria range, and the criteria. - Close the parentheses and press Enter.
For example, if you want to average the values in cells A1:A10 where the corresponding values in cells B1:B10 are greater than 10, you would use the following formula:
=AVERAGEIF(B1:B10, ">10", A1:A10)
This formula averages only the values in cells A1:A10 where the corresponding values in cells B1:B10 are greater than 10.
How to Use the AVERAGEIF Function with Multiple Criteria
If you want to average values based on multiple criteria, you can use the AVERAGEIFS function. This function is similar to the AVERAGEIF function, but it allows you to specify multiple criteria ranges and criteria.
For example:
=AVERAGEIFS(A1:A10, B1:B10, ">10", C1:C10, "<20")
This formula averages the values in cells A1:A10 where the corresponding values in cells B1:B10 are greater than 10 and the corresponding values in cells C1:C10 are less than 20.
Average Only Filtered Cells Using Power Query
Power Query is a powerful tool in Excel that allows you to manipulate and analyze data in a variety of ways. One of the things you can do with Power Query is average only filtered cells.
To use Power Query to average only filtered cells, follow these steps:
- Select the range of cells you want to average.
- Go to the "Data" tab in the ribbon and click "From Table/Range".
- In the Power Query Editor, click "Filter" and select the filter criteria you want to apply.
- Click "Group By" and select the column you want to average.
- Click "Load" to load the results back into your worksheet.
For example, if you want to average the values in cells A1:A10 where the corresponding values in cells B1:B10 are greater than 10, you would follow these steps:
- Select cells A1:A10.
- Go to the "Data" tab in the ribbon and click "From Table/Range".
- In the Power Query Editor, click "Filter" and select "Greater Than" from the dropdown menu.
- Enter the value "10" in the filter criteria box.
- Click "Group By" and select column A.
- Click "Load" to load the results back into your worksheet.
Average Only Filtered Cells Using VBA
If you're comfortable with VBA, you can use a macro to average only filtered cells. Here's an example of a VBA macro that does this:
Sub AverageFilteredCells()
Dim rng As Range
Dim avg As Double
Set rng = Range("A1:A10")
avg = Application.WorksheetFunction.Average(rng.SpecialCells(xlCellTypeVisible))
Range("E1").Value = avg
End Sub
This macro averages the values in cells A1:A10 where the corresponding values are visible (i.e., not filtered out).
To use this macro, follow these steps:
- Press Alt+F11 to open the VBA Editor.
- In the VBA Editor, click "Insert" and select "Module".
- Paste the macro code into the module.
- Click "Run" to run the macro.
The average of the filtered cells will be displayed in cell E1.
Gallery of Averaging Filtered Cells in Excel
Averaging Filtered Cells Image Gallery
We hope this article has helped you learn how to average only filtered cells in Excel. Whether you're using the SUBTOTAL function, the AVERAGEIF function, Power Query, or VBA, there are many ways to achieve this. Do you have any questions about averaging filtered cells in Excel? Leave a comment below and we'll do our best to help.