Table of Contents
To calculate the average of filtered rows in Google Sheets, follow these steps:
1. Select the range of cells that contain the data you want to filter.
2. Click on the “Data” tab and then click on “Filter” from the dropdown menu.
3. Use the filter options to select the rows you want to include in the average calculation.
4. Once the filter is applied, click on an empty cell where you want the average to be displayed.
5. Type the formula “=AVERAGE(range)”, replacing “range” with the range of cells containing the filtered data.
6. Press enter to calculate the average.
For example, if you have a dataset of sales figures for different products and you want to calculate the average of only the products with a sales value above $100, you would filter the data by selecting the “Sales” column and choosing “greater than” from the filter options. Then, in an empty cell, you would type “=AVERAGE(A2:A10)” where A2:A10 is the range of filtered cells containing the sales data. This will give you the average of only the filtered rows.
Average Filtered Rows in Google Sheets (With Example)
The easiest way to take the average of a filtered range in Google Sheets is to use the following syntax:
SUBTOTAL(101, A1:A10)
Note that the value 101 is a for taking the average of a filtered range of rows.
The following example shows how to use this function in practice.
Example: Average Filtered Rows in Google Sheets
Suppose we have the following spreadsheet that contains information about various basketball teams:
To add a filter to this data, we can highlight cells A1:B10, then click the Data tab, then click Create a filter:
We can then click the Filter icon at the top of the Points column and uncheck the box next to the first three values 84, 89, and 93:
Once we click OK, the data will be filtered to remove these values.
If we attempt to use the AVERAGE() function to average the points column of the filtered rows, it will not return the correct value:
Instead, need to use the SUBTOTAL() function:
This function takes the average of only the visible rows.
Average of Visible Rows: (99 + 94 + 97 + 104 + 109 + 99) / 6 = 100.333.
Additional Resources
Cite this article
stats writer (2024). What are the steps to calculate the average of filtered rows in Google Sheets, and can you provide an example?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/what-are-the-steps-to-calculate-the-average-of-filtered-rows-in-google-sheets-and-can-you-provide-an-example/
stats writer. "What are the steps to calculate the average of filtered rows in Google Sheets, and can you provide an example?." PSYCHOLOGICAL SCALES, 28 Jun. 2024, https://scales.arabpsychology.com/stats/what-are-the-steps-to-calculate-the-average-of-filtered-rows-in-google-sheets-and-can-you-provide-an-example/.
stats writer. "What are the steps to calculate the average of filtered rows in Google Sheets, and can you provide an example?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/what-are-the-steps-to-calculate-the-average-of-filtered-rows-in-google-sheets-and-can-you-provide-an-example/.
stats writer (2024) 'What are the steps to calculate the average of filtered rows in Google Sheets, and can you provide an example?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/what-are-the-steps-to-calculate-the-average-of-filtered-rows-in-google-sheets-and-can-you-provide-an-example/.
[1] stats writer, "What are the steps to calculate the average of filtered rows in Google Sheets, and can you provide an example?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. What are the steps to calculate the average of filtered rows in Google Sheets, and can you provide an example?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.