HomeGoogle DocsSpreadsheetAverage Function in Google Sheets (Advanced Tips and Tricks)

Average Function in Google Sheets (Advanced Tips and Tricks)

Published on

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)
Average Function in Google Sheets - Formula Examples

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)
Arithmetic Mean of Visible Cells in Google Sheets

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))
Calculating Month Wise Mean Using the Average Function

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)
Calculating Month Wise Mean Using the Average Function

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.

Excluding Hidden Rows in Group Wise Average Formula

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!

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

2 COMMENTS

  1. 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!”

LEAVE A REPLY

Please enter your comment!
Please enter your name here