Are you looking to calculate the average of a set of numbers in Google Sheets? Look no further! In this article, we’ll delve into the advanced tips and tricks of using the Average function, turning you into a Google Sheets pro in no time.
The average or mean is a common statistical measure you’ve probably encountered back in your school days. It’s simply the sum of a set of numbers divided by the count of those numbers. While Google Sheets doesn’t have a dedicated MEAN function, fear not, as the AVERAGE function is here to save the day!
Average Function – Unveiling the Syntax and Arguments
Before we dive into the advanced tips, let’s first familiarize ourselves with the basic syntax and arguments of the Average function in Google Sheets.
Syntax:
AVERAGE(value1, [value2, ...])Arguments:
value1– Required. The first value or array to consider when calculating the mean.[value2, ...]– Optional and repeatable. Any additional values or arrays to include in the calculation.
It’s important to note that the Average function supports more than 30 arguments, allowing you to work with a wide range of data. Let’s see it in action!
=average(A2:A4)In the above example, we have three values in the range A2:A4, with a sum of 150. Therefore, the mean of these numbers is 150/3 = 50. Simple, right?
If you want to include additional values or arrays, you can simply add them as arguments:
=average(C14:C16,E14:E15)Note: The Average function in Google Sheets automatically ignores any text or blank cells within the specified range.
Skipping Zeros in an Average Formula
Let’s talk about how the presence of zeros can affect your average calculation. If you have a value of 0 (zero) in your range, it would skew the mean calculation. For example, if the value in cell A2 is 0, the mean would be calculated as 50/3, resulting in 16.67.
To exclude zeros from the average calculation, you can utilize the Averageif function:
=averageif(A2:A4,"<>0")Another option is to use the Averageifs function for conditional mean calculations. Now, let’s move on to some advanced usage of the Average function in Google Sheets.
How to Exclude Hidden Row Values in Mean Calculation
What if you want to exclude hidden rows from your mean calculation? Fear not, for Google Sheets offers an alternative solution – the Subtotal function.
Whether your data is filtered or not, the Subtotal function enables you to find the mean of a set of numbers in Google Sheets. This versatile function supports various function numbers, including Mean, Count, Counta, Max, Min, Product, Stdev, Stdevp, Sum, Var, and Varp.
To find the mean of visible cells, use the Subtotal function with function number 101:
=subtotal(101,A2:A4)In this example, we have a hidden value in cell A3, with a value of 25. However, the Subtotal formula skips this value in the mean calculation, resulting in a mean of 62.5 (125/2).
For a more detailed tutorial on finding the average of visible rows in Google Sheets, check out our article on Calculating the Average of Visible Rows in Google Sheets.
Differentiating Hidden Rows and Filtered Out Rows
When using the Subtotal function, you can choose between two function numbers to calculate the mean: 101 and 1. But what’s the difference?
Function number 101 excludes all types of hidden rows, while function number 1 only excludes rows that are not visible due to filtering.
Hidden Rows:
- Hidden by Grouping Rows or Columns.
- Manually Hidden (Right-Click)
Filtered Rows:
Hidden via Data > Create a Filter / Filter views.
Grouping and Mean Using the Average or Query Functions
Now, let’s explore a unique way to find group-wise averages in Google Sheets. We can achieve this by combining the Avg function with the Query function.
Before we dive into that, let’s first understand how to calculate group-wise means using the Average function alone. In the example below, we’ll find the monthly mean of sales values:
Average Formula:
To return the month numbers of dates in range A2:A7, use the following ArrayFormula in cell B2:
=ArrayFormula(month(A2:A7))Once you have the month numbers, you can calculate the mean using the Average function:
=ArrayFormula(average(if($B:$B=B2,$C:$C)))But what if we want to calculate group-wise means using the Query function?
Query Formula:
=query({A1:B7},"Select month(Col1)+1,avg(Col2) group by month(Col1)+1",1)As you can see, by incorporating the Query function, you can easily use the Avg function to find group-wise means.
For a deeper understanding of using the Query function in Google Sheets, check out our article on Group-wise Averages Using the Query Function.
Group-Wise Average Formula in a Filtered Data Set
In grouping scenarios, you might want to exclude hidden rows from the mean calculation. Let’s take a look at an example that demonstrates how to calculate the average of visible rows within a group:
Here’s how we’ll achieve it:
- Create a helper column, as shown in the example.
- Use the Subtotal function with function number 109 to determine the visibility of each row in the group. Insert the formula
=subtotal(109,B2)in cell C2 and copy it down. - Modify the Query formula by adding a “where” clause to exclude hidden rows:
=query({A1:C7},"Select month(Col1)+1,avg(Col2) where Col3>0 group by month(Col1)+1",1)
By utilizing these techniques, you can calculate accurate group-wise averages while excluding hidden rows from the calculation.
That wraps up our exploration of the Average function in Google Sheets, along with some advanced tips and tricks. We hope you found these insights helpful on your Google Sheets journey. For more informative articles and tutorials, visit Crawlan.com. Happy calculating!
