How to Average Filtered Rows in Google Sheets
To average filtered rows in Google Sheets, you can use the AVERAGEIF
or AVERAGEIFS
functions, which allow you to calculate the average of a range based on specific criteria. Here's a step-by-step guide on how to use these functions.
Using AVERAGEIF
- Open Google Sheets and input your data.
- In a cell where you want to display the average, type the formula
=AVERAGEIF(range, criteria, [average_range])
. Replace "range" with the range of cells containing the criteria, "criteria" with the condition you want to filter, and "average_range" with the range of cells you want to calculate the average for. - Press Enter to apply the formula and get the average.
Using AVERAGEIFS
- Open Google Sheets and input your data.
- In a cell where you want to display the average, type the formula
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
. Replace "average_range" with the range of cells you want to calculate the average for, "criteria_range1" with the range of cells containing the first criteria, "criteria1" with the first condition, and so on for additional criteria. - Press Enter to apply the formula and get the average.
Example
Data:
A B
1 Name Age
2 Alice 25
3 Bob 30
4 Carol 55
5 Dave 40
6 Alice 28
Let's say we want to find the average age of people named "Alice". We can use the AVERAGEIF
function to do this.
- In cell D1, type the formula
=AVERAGEIF(A1:A6, "Alice", B1:B6)
. - Press Enter.
- The result, 26.5, will be displayed in cell D1, which is the average age of people named "Alice".
Now, let's find the average age of people named "Alice" who are older than 25. We can use the AVERAGEIFS
function for this.
- In cell E1, type the formula
=AVERAGEIFS(B1:B6, A1:A6, "Alice", B1:B6, ">25")
. - Press Enter.
- The result, 28, will be displayed in cell E1, which is the average age of people named "Alice" who are older than 25.
Did you find this useful?