You can use the AVERAGE function to calculate the arithmetic mean, or simply the mean, in Google Sheets. The arithmetic mean is the average you’re likely familiar with: it’s the sum of a set of numbers divided by the count of those numbers.

In addition to the arithmetic mean, there are other types of mean calculations, each with its function in Google Sheets, such as TRIMMEAN, HARMEAN, and GEOMEAN.

AVERAGE Function: Syntax and Examples

To use the AVERAGE function, start by understanding its syntax and arguments.

Syntax:

AVERAGE(value1, [value2, …])

Arguments:

• value1 – The first value or range to consider when calculating the mean.
• value2, … – Optional. Additional values or ranges to include in the calculation.

The dimensions of value1 and value2 do not need to be the same. All values or ranges will be included together in the mean calculation.

Examples

Assume the values in A1:A5 are 5, 10, 10, 5, and 20. The following formula will return 10 as the average:

=AVERAGE(A1:A5)

This is equivalent to =SUM(A1:A5)/COUNT(A1:A5).

Here’s an example with multiple ranges:

=AVERAGE(A1:A100, B10:B25)

Note: The AVERAGE function in Google Sheets will ignore text and blank cells in the range.

Skipping Zeros in an AVERAGE Function Calculation

How do you exclude zeros from the average calculation?

The simplest way is to use the combination of AVERAGEIF and VSTACK functions.

To get the average of values in column A excluding zeros, use the AVERAGEIF formula:

=AVERAGEIF(A1:A, "<>0")

For multiple ranges, such as A1:A and C1:C, use the VSTACK function with AVERAGEIF:

=AVERAGEIF(VSTACK(A1:A, C1:C), "<>0")

Additional Tips

To find the average of values excluding zeros, you can also use a QUERY formula or a combination of FILTER and AVERAGE.

I’m including these methods as you might encounter them in a shared sheet.

The following QUERY formula returns the average of values in column A excluding zeros:

=QUERY({A1:A}, "SELECT AVG(Col1) WHERE Col1>0 LABEL AVG(Col1) ''")

The following combination of FILTER and AVERAGE functions achieves the same result:

=AVERAGE(FILTER(A1:A, A1:A>0))

Here, the FILTER function filters the range A1:A for values greater than 0, and the AVERAGE function returns the average of the filtered rows.

Resources

Here are some related resources and advanced tips and tricks for using the AVERAGE function in Google Sheets.

• Find the Average of Visible Rows in Google Sheets
• Find the Average of the Last N Values in Google Sheets
• How to Add an Average Line to Charts in Google Sheets
• Average Array Formula Across Rows in Google Sheets
• Array Formula to Return Average of Every N Cell in Google Sheets
• Average Each Row in Dynamic Range in Google Sheets
• Calculating Running Average in Google Sheets (Array Formulas)
• Average of Smallest N Values in Google Sheets (Zero and Non-Zero)
• Google Sheets: Nested BYROW to Loop a Row-by-Row Average
• Calculating Rolling N Period Average in Google Sheets
• Google Sheets: Rolling Average Excluding Blank Cells and Aligning
• Calculating Simple Moving Average (SMA) in Google Sheets
• Weighted Moving Average in Google Sheets (Formula Options)