I have a little secret to share with you. Are you ready to learn how to find the average of values only in the visible rows in Google Sheets? I mean, how to exclude other rows? Don’t worry, I’ll show you how in this tutorial.
Average Functions in Google Sheets
There are three popular Average functions in Google Sheets: Average, Averageif, and Averageifs. But unfortunately, none of them support the above type of average calculation without workarounds.
In addition to the above functions, you can also use the Query function to calculate the average in Google Sheets. However, even in Query, you should follow some workarounds to exclude hidden rows in the average calculation.
Calculate the Average in Google Sheets
Now, let’s get into the nitty-gritty of calculating the average in Google Sheets. In this tutorial, I’ll teach you how to calculate the average in Google Sheets, excluding filtered out, hidden via row grouping, and manually hidden rows (unconditional average).
But wait! Did you know that Google Sheets has a new feature to hide rows by row and column grouping? It’s true! With the grouping of rows, users can easily hide and unhide groups of rows in Google Sheets. So, I’ll also show you how to exclude the rows hidden by row grouping in Average, Averageif, Averagifs, and Query functions.
Enough chit-chat, let’s dive right in!
Average of Visible Rows
Average Excluding Filtered or Hidden Rows (Unconditional Average)
Before we proceed, let’s understand what average is and how to calculate it. Average is simply the total divided by the count.
For example, let’s say we have a list of names in column A and their ages in column B. We can calculate the average as follows:
Average = total/count.
Total: =sum(B2:B7)
Count: =count(B2:B7)
Average: =sum(B2:B7)/count(B2:B7)Alternatively, you can use the dedicated worksheet function:
Average: =average(B2:B7)Now, what if we want to filter out the gender “M” and calculate the average of the rest? Here’s how you can do it:
- Select the range A1:C7 and apply Data > Create a filter.
- Click on the field name “gender” in cell B1 and uncheck “M”.
- Check the average formula in cell C9. The answer includes the values in hidden rows.
- To exclude the values in the filtered-out rows, we need to find an alternative formula to Average().
One formula that can help us achieve this is by using the Subtotal function.
Average of Visible Rows in Google Sheets Using the Function Subtotal and Function # 1
To find the average in Google Sheets excluding filtered rows, you can use the Subtotal function and function number 1 as shown below:
=subtotal(1,C2:C7)Average of Visible Rows in Google Sheets Using the Function Subtotal and Function # 101
In the above case, you can also use function number 101 instead of 1:
=subtotal(101,C2:C7)This formula can also find the average of filtered rows. The difference is that it will only consider the visible rows in the average calculation, excluding all hidden rows.
Difference Between Function Number 1 and 101 in Google Sheets Subtotal Function in Calculating Average
The formula subtotal(1, ...) calculates the average of visible rows, excluding only the filtered-out rows. However, it includes manually hidden and grouped rows. To exclude such rows, use function number 101.
Single Conditional Average that Excluding Filtered or Hidden Rows
Now let’s talk about conditional average in Google Sheets. To calculate the conditional average, we can use the function Averageif.
For example, if we want to find the average age of females in the list, we can use the following formula:
=averageif(B2:B7,"F",C2:C7)But what about conditional averages with visible rows? Unfortunately, there is no Subtotal If function. However, we can use the Subtotal function in a helper or virtual helper column and use that in Averageifs for our purpose.
Yes, you heard it right! Use Averageifs, not Averageif. I’ll explain why Averageifs is better and how to exclude hidden, filtered out, and grouped rows in conditional average.
Multiple Conditional Average that Excluding Filtered or Hidden Rows
I know you must have a few questions in your mind. How do we exclude filtered, hidden, or grouped rows in Averageifs? Why am I recommending Averageifs over Averageif? What is a helper column or the content in the helper column?
Let’s address these questions one by one.
What is a Helper Column?
In the examples above, we used a helper column (column D) to simplify the formulas and exclude hidden rows in the average calculation. This additional column contains the Subtotal formula using function number 109 (sum).
For example:
=subtotal(109,C2)The purpose of this formula is to return the value of the corresponding cell in column C (in this case, C2), but if the row is hidden, the value will become zero.
To calculate the conditional average with visible rows, you can use the helper column in the Averageifs formula as follows:
=AVERAGEIFS(C2:C7,B2:B7,"F",D2:D7,">0")If you’re using a virtual helper column, replace D2:D7 with the Map formula:
=AVERAGEIFS(C2:C7,B2:B7,"F",map(C2:C7,lambda(r, subtotal(109,r))),">0")Query Function to Calculate Average in Google Sheets – Filtered and Unfiltered Rows
Alternatively, we can replace the above Averageifs formula with a Query formula as shown below:
=query(A2:D7,"Select Avg(C) where B='F' and D>0")In this formula, we’ve used the helper column D, which contains the Subtotal formula. Take a look at the “where” clause in the formula.
Here is the formula using a virtual helper column:
=query({A2:C7,map(C2:C7,lambda(r, subtotal(109,r)))},"Select Avg(Col3) where Col2='F' and Col4>0")Conclusion
Congratulations! You’ve learned how to find the average of visible rows in Google Sheets, including the exclusion of hidden, filtered out, and grouped rows. The key to achieving this is using the Subtotal function in one way or another.
I hope this tutorial has been helpful and that you now have a clear understanding of how to calculate averages in Google Sheets. If you have any further questions, feel free to reach out. Until then, happy calculating!
