We can use an AVERAGE function-based formula in Google Sheets for calculating the arithmetic mean. Here are some advanced tips.
Arithmetic mean or simply mean is the average you are familiar with from your school days. In short, it is the sum divided by the count of a set of numbers.
For your info, there is no MEAN function in Google Sheets.
Other than the Arithmetic Mean, there are different kinds/types of mean calculations.
For that, there are specific functions too in Google Sheets, such as TRIMMEAN, HARMEAN, and GEOMEAN.
Average Function – Syntax and Arguments in Google Sheets
To code an Average formula, learn the syntax and arguments of the function first.
Syntax:
AVERAGE(value1, [value2, …])
Arguments:
value1 – The first value or array to consider when calculating the mean.
[value2, …] It’s optional and repeatable – Any additional values or arrays to consider when calculating the mean.
It supports 30+ arguments.
See the use of the Average function in Google Sheets.
=average(A2:A4)
In this average formula, there are three values, and the sum of these values is 150.
So the mean of these numbers is 150/3 = 50.
I have used value1 in the above Average formula example.
In the following example, you can see the use of value1 and value2.
=average(C14:C16,E14:E15)
Note:- The Average formula in Google Sheets will ignore texts and blank cells, if any, in the array/range.
Skip Zeros in an Average Formula In Google Sheets
See how 0 (zero) affects the average calculation.
In the above first formula example, if the value in cell A2 is 0, the mean calculation will be like 50/3=16.67.
To skip zero in the Average function in Google Sheets, you can use the Averageif function as below.
=averageif(A2:A4,"<>0")
Averageifs is another function that you can use for conditional mean.
Now I’m taking you to some advanced use of the Average function in Google Sheets. Maybe it’s easier for some of you.
How to Skip or Avoid Hidden Row Values in the Mean Calculation
There is an alternative to the Average function in Google Sheets.
Whether your data is filtered or not, not matter, you can use a different kind of average formula in Google Sheets.
It’s none other than the Subtotal.
You can use the Subtotal function to find the arithmetic mean of a set of numbers in Google Sheets.
The Subtotal function uses function numbers to find the mean, Count, Counta, Max, Min, Product, Stdev, Stdevp, Sum, Var, and Varp.
Use the Subtotal function with function number 101 to return the mean of visible cells.
Example:
=subtotal(101,A2:A4)
In the above example, you can see that row # 3 is hidden.
Actually, the value in hidden cell A3 is 25.
But the above Subtotal formula skips this value in the mean calculation. It adds 100+25 and divide it by 2, i.e. 125/2 = 62.5.
Here is a detailed tutorial on this topic – Find the Average of Visible Rows in Google Sheets.
How to Differentiate Hidden Rows and Filtered Out Rows?
There are two function numbers that you can use in Subtotal to calculate the arithmetic mean. They are 101 and 1.
What is the difference between function # 101 and 1 in the Subtotal-based average formula in Google Sheets?
The former excludes all types of hidden rows, whereas the latter only excludes the rows that are not visible due to filtering.
Hidden Rows:- 1) Hidden by Grouping Rows or Columns. 2) Manually Hidden (Right-Click)
Filtered Rows:- Hidden via Data > Create a Filter / Filter views.
Grouping and Mean Using the Average or Query Functions
There is one unique way of finding group-wise average in Google Sheets. For that, we can use the Avg function with the Query.
Before going to that, let me explain how to calculate group-wise mean using the Average function in Google Sheets.
In the below example, I’m trying to find moth wise mean of Sales Value. It’s just an example, and the data may not be realistic.
Month Wise Mean Using Query or Average Functions In Google Sheets
Average Formula:
I have used the below ArrayFormula in cell B2 to return the month numbers of dates in cell range A2:A7
=ArrayFormula(month(A2:A7))
Here is the average function array formula I have used in cell D2 and then copied to down.
=ArrayFormula(average(if($B:$B=B2,$C:$C)))
Can I use the Subtotal function to find the group-wise mean in Google Sheets?
Nope! Because it won’t accept any expression in the range. As you can see, in the above formula, I’ve used an IF logical test.
Query Formula:
=query({A1:B7},"Select month(Col1)+1,avg(Col2) group by month(Col1)+1",1)
If you know how to use Query, you can easily use the Avg function to find the Group-wise mean.
So, I’m skipping the anatomy of the above Query formula here.
You May Also Like:- Average by Month in Google Sheets (Formula Options).
Group Wise Average Formula in a Filtered Data Set
In grouping, we can only include the visible rows in the mean calculation.
The below example explains how to calculate the average of visible rows in the grouping.
Here we require a helper column as below.
What’s the content or formula in it? Here you go!
Here I’ve used the same formula which I’ve used in the above example.
But here, I’ve made two minor changes to the formula and to the source data.
I’ve marked both changes in the above screenshot. What are they?
Changes in the Formula:
=query({A1:C7},"Select month(Col1)+1,avg(Col2) where Col3>0 group by month(Col1)+1",1)
Changes in the Source Data:
Column C, which is a helper column, is new here.
In this helper column, I’ve inserted the below formula cell C2 and copied it down.
=subtotal(109,B2)
The above Subtotal formula can return the value 0 if the row is hidden or not visible.
In the Query, with an additional “where” clause, I’ve excluded hidden rows in the Average calculation.
That’s all about the Average function in Google Sheets and some advanced tips related to it. Enjoy!
In Sheets, is there a way to avoid/ignore cells that contain the error “DIV/01!” when that cell needs to stay in line with other cells in the same row to calculate “Average”?
Randomly, all cells could have numbers in them, they could be empty or they could contain the error “DIV/01!”
Hi, Keith Janes,
You can use the IFERROR function for that.