How To Calculate The Mean In Google Sheets

We tin apply an AVERAGE function-based formula in Google Sheets for calculating the arithmetic mean. Here are some advanced tips.

Arithmetic hateful or but mean is the average you are familiar with from your school days. In brusk, it is the sum divided by the count of a set of numbers.

For your info, in that location is no MEAN function in Google Sheets.

Other than the Arithmetic Mean, there are different kinds/types of hateful 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 lawmaking an Average formula, learn the syntax and arguments of the function first.

Syntax:

          AVERAGE(value1, [value2, …])        

Arguments:

value1 – The commencement value or assortment to consider when calculating the mean.

[value2, …] It's optional and repeatable – Whatever boosted values or arrays to consider when calculating the mean.

It supports 30+ arguments.

See the employ of the Boilerplate function in Google Sheets.

          =average(A2:A4)        

In this boilerplate formula, there are three values, and the sum of these values is 150.

So the hateful of these numbers is 150/three = 50.

I have used value1 in the above Boilerplate formula instance.

In the following instance, you tin see the use of value1 and value2.

          =average(C14:C16,E14:E15)        

Annotation:- 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=sixteen.67.

To skip zero in the Average function in Google Sheets, y'all can use the Averageif function as below.

          =averageif(A2:A4,"<>0")        

Averageifs is another function that you lot can apply for conditional mean.

Now I'm taking you to some advanced use of the Boilerplate function in Google Sheets. Possibly it'south easier for some of you.

There is an alternative to the Boilerplate function in Google Sheets.

Whether your information is filtered or non, not matter, you lot can use a unlike kind of average formula in Google Sheets.

It'due south none other than the Subtotal.

You lot tin can use the Subtotal role to find the arithmetic mean of a set of numbers in Google Sheets.

The Subtotal function uses role numbers to detect the mean, Count, Counta, Max, Min, Product, Stdev, Stdevp, Sum, Var, and Varp.

Apply the Subtotal function with function number 101 to render the mean of visible cells.

Example:

          =subtotal(101,A2:A4)        

In the above instance, you lot tin see that row # 3 is hidden.

Actually, the value in hidden prison cell A3 is 25.

But the higher up Subtotal formula skips this value in the mean calculation. It adds 100+25 and carve up it by 2, i.e. 125/2 = 62.five.

Here is a detailed tutorial on this topic – Find the Average of Visible Rows in Google Sheets.

How to Differentiate Subconscious Rows and Filtered Out Rows?

There are two function numbers that you lot can use in Subtotal to calculate the arithmetic hateful. They are 101 and one.

What is the divergence between function # 101 and ane 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.

Subconscious Rows:- 1) Hidden past Grouping Rows or Columns. 2) Manually Hidden (Right-Click)

Filtered Rows:- Hidden via Information > Create a Filter / Filter views.

Grouping and Mean Using the Average or Query Functions

There is ane unique way of finding grouping-wise average in Google Sheets. For that, nosotros tin can use the Avg part with the Query.

Before going to that, allow me explain how to summate group-wise mean using the Average office in Google Sheets.

In the below case, I'm trying to find moth wise mean of Sales Value. It's just an case, and the data may not be realistic.

Month Wise Mean Using Query or Average Functions In Google Sheets

Average Formula:

I take 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 take used in cell D2 so copied to down.

          =ArrayFormula(average(if($B:$B=B2,$C:$C)))        

Can I use the Subtotal function to find the grouping-wise mean in Google Sheets?

Nope! Because it won't take any expression in the range. As you can encounter, in the to a higher place formula, I've used an IF logical exam.

Query Formula:

          =query({A1:B7},"Select calendar month(Col1)+1,avg(Col2) group past month(Col1)+1",one)        

If you know how to use Query, you lot can easily employ the Avg role to find the Group-wise mean.

Then, I'm skipping the anatomy of the above Query formula here.

You lot May Also Like:- Average by Month in Google Sheets (Formula Options).

Group Wise Average Formula in a Filtered Information Set

In grouping, we can but include the visible rows in the mean calculation.

The below example explains how to calculate the boilerplate of visible rows in the grouping.

Here we require a helper cavalcade as below.

What'due south the content or formula in it? Here you become!

Here I've used the same formula which I've used in the above case.

Only here, I've made 2 minor changes to the formula and to the source data.

I've marked both changes in the to a higher place screenshot. What are they?

Changes in the Formula:

          =query({A1:C7},"Select calendar month(Col1)+ane,avg(Col2)                          where Col3>0                        group by calendar month(Col1)+ane",1)        

Changes in the Source Data:

Column C, which is a helper column, is new here.

In this helper column, I've inserted the beneath formula prison cell C2 and copied it down.

          =subtotal(109,B2)        

The above Subtotal formula can render 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'southward all most the Average function in Google Sheets and some advanced tips related to it. Enjoy!

Source: https://infoinspired.com/google-docs/spreadsheet/google-sheets-average-function/

Share this post