---
title: "How to average in Google Sheets: AVERAGE and AVERAGEIF"
canonical: "https://www.owox.com/blog/articles/average-functions-google-sheets"
updated: "2026-10-05"
---

# How to calculate an average in Google Sheets: AVERAGE, AVERAGEIF and AVERAGEIFS

AVERAGE returns the mean of a range, AVERAGEA also counts text and TRUE/FALSE, AVERAGEIF averages the cells that meet one condition and AVERAGEIFS the cells that meet several. This guide shows each with examples, and how to fix #DIV/0! and averages thrown off by zeros, text and errors.

[Google Sheets Tips](/blog/topics/google-sheets-tips) · Updated October 2, 2026 · [Vadym Kramarenko](/team/vadym-kramarenko) · 14 min read

![How to calculate an average in Google Sheets: AVERAGE, AVERAGEIF and AVERAGEIFS](https://cdn.owox.ai/www/webflow/6a147462338c110acc42a103_AVERAGE-FUNCTIONS-IN-GOOGLE-SHEETS.png/public)

To calculate an average in Google Sheets, type **\=AVERAGE(range)** in an empty cell, for example `=AVERAGE(C3:C12)`, and press Enter. The function adds the numbers in the range and divides the total by how many numbers there are. Empty cells and text are ignored; zeros are counted.

| You want to | Use |
| --- | --- |
| Average the numbers in a range | `=AVERAGE(C3:C12)` |
| Count text and FALSE as 0, TRUE as 1 | `=AVERAGEA(C3:C12)` |
| Average the rows that meet one condition | `=AVERAGEIF(C3:C12, "Marketing", D3:D12)` |
| Average the rows that meet several conditions | `=AVERAGEIFS(D3:D12, C3:C12, "Marketing", E3:E12, ">70")` |

This guide goes through each function with an example, then through text, date, comparison and wildcard criteria, and finally through the reasons an average comes out wrong.

## AVERAGE, AVERAGEA, AVERAGEIF and AVERAGEIFS: syntax and a first example

### AVERAGE function

AVERAGE returns the **arithmetic mean of the numbers in one or more ranges**, and recalculates when the data changes.

#### Syntax of AVERAGE function

The syntax for the AVERAGE function in Google Sheets is:

> `=AVERAGE(value1, [value2, ...])`

Let’s break down what these parameters mean:

*   **value1:** The first number or range from which to calculate the average.
*   **\[value2, …\]:** Additional numbers or ranges you can include in the average calculation.

#### Example of AVERAGE: Calculating simple averages

A table lists the August 2024 salaries of 10 employees in column C. To find the average salary:

> `=AVERAGE(C3:C12)`

![Demonstrating the basic usage of the AVERAGE function in Google Sheets with sample numeric data.](https://cdn.owox.ai/www/webflow/67882b7e56aa364e88ee8659_56894.png/public)

Here’s the breakdown:

*   **C3:C12**: this is a range in column C, which contains the salary data for the 10 employees.

### AVERAGEA function

AVERAGEA **counts every non-empty cell**. Numbers are taken as they are, text counts as 0, TRUE as 1 and FALSE as 0. Use it when a text entry such as “n/a” or “absent” should pull the average down instead of being skipped.

#### Syntax of AVERAGEA function

The syntax for the AVERAGEA function in Google Sheets is:

> `=AVERAGEA(value1, [value2], …)`

Let’s break down what these parameters mean:

*   **value1:** The first number, text, or range considered when calculating the average with the AVERAGEA function.
*   **\[value2, …\]:** Extra numbers, text, or ranges that can also be included in the AVERAGEA calculation.

#### Example of AVERAGEA: Including numbers and text

Take the same salary list and replace one salary with a text entry. **AVERAGE** skips that cell and averages the other nine:

> `=AVERAGE(C3:C12)`

![Explaining the AVERAGEA function applied to a dataset containing both numbers and text.](https://cdn.owox.ai/www/webflow/67882b7f91388d21ed286deb_56898.png/public)

**AVERAGEA** counts the text cell as a zero and divides by ten:

> `=AVERAGEA(C3:C12)`

![Further showcasing how the AVERAGEA function processes mixed data types in Google Sheets.](https://cdn.owox.ai/www/webflow/67882b7ff93359357174b6b7_57034.png/public)

The two results differ because the **divisor differs**: nine for AVERAGE, ten for AVERAGEA. Neither is wrong; they answer different questions.

### AVERAGEIF function

AVERAGEIF averages only the cells that meet **one condition**, such as a category, a threshold or a date.

#### Syntax of AVERAGEIF function

The AVERAGEIF function in Google Sheets has the following syntax:

> `=AVERAGEIF(criteria_range, criterion,[average_range])`

Let us break down its arguments:

*   **criteria\_range:** The range that is tested against the criterion.
*   **criterion:** The condition that will be evaluated against the criteria\_range, which can be a number, text, date, or expression.
*   **average\_range (optional):** The range with the values to average. If omitted, the values in criteria\_range are averaged.

💡 _AVERAGEIF tests a condition and averages. To test a condition and return a value instead, see the_ [_IF function_](/blog/articles/if-function-google-sheets)_._

[![How to Leverage the IF Function in Google Sheets for Conditional Logic](https://cdn.owox.ai/www/webflow/679fdc99dc2f428751032307_679fdc23dc2f428751028cb7_-D0-97-D0-BD-D1-96-D0-BC-D0-BE-D0-BA-20-D0-B5-D0-BA-D1-80-D0-B0-D0-BD-D0-B0-202025-02-02-20-D0-BE-2022.56.34.png/public)](/blog/articles/if-function-google-sheets)

#### Example of AVERAGEIF: Averaging based on specific criteria

Add the department of each employee in column C, with the salaries now in column D. To get the average salary in the Marketing department:

> `=AVERAGEIF(C3:C12,"Marketing",D3:D12)`

![Applying the AVERAGEIF function with a single condition to filter data in Google Sheets.](https://cdn.owox.ai/www/webflow/67882b7fe4207b21a58e0331_56910.png/public)

Here’s the breakdown:

*   **C3:C12:** The range of cells to evaluate against the criterion.
*   **“Marketing”:** The criteria to apply.
*   **D3:D12:** The range of cells to average.

### AVERAGEIFS function

AVERAGEIFS averages the cells that meet **every one of several conditions**. Each condition has its own range.

#### Syntax of AVERAGEIFS function

The syntax for the AVERAGEIFS function is:

> `=AVERAGEIFS(avg_range, crit_range1, criterion1, crit_range2, criterion2)`

*   **avg\_range:** The range to average. Note the order: in AVERAGEIFS it comes first, in AVERAGEIF it comes last.
*   **crit\_range1:** The range tested against the first condition.
*   **criterion1:** The first condition.
*   **\[crit\_range2, criterion2, …\] (optional):** Additional ranges and criteria.

💡 _For a chain of conditions that returns a value instead of an average, see the_ [_IFS function_](/blog/articles/ifs-function-google-sheets)_._

[![How to Use the IFS Function in Google Sheets for Advanced Conditional Logic](https://cdn.owox.ai/www/webflow/679fe24c036ebe0f6ae0a0e1_679fe176036ebe0f6adfacec_-D0-97-D0-BD-D1-96-D0-BC-D0-BE-D0-BA-20-D0-B5-D0-BA-D1-80-D0-B0-D0-BD-D0-B0-202025-02-02-20-D0-BE-2023.19.12.png/public)](/blog/articles/ifs-function-google-sheets)

#### Example of AVERAGEIFS: Averaging based on several variables

Add the KPI performance of each employee in column E. To get the average salary of the Marketing employees whose KPI is above 70:

> `=AVERAGEIFS(D3:D12,C3:C12,"Marketing",E3:E12,">70")`

![Using the AVERAGEIFS function to calculate averages with multiple conditions in Google Sheets.](https://cdn.owox.ai/www/webflow/67882b7e6ec49336aca5c29e_56916.png/public)

Here’s the breakdown:

*   **D3:D12:** The salaries to average.
*   **C3:C12:** The departments, tested against the first condition.
*   **“Marketing”:** The first condition.
*   **E3:E12:** The KPI values, tested against the second condition.
*   **“>70”:** The second condition.

## Practical applications of AVERAGE and AVERAGEA functions

AVERAGE takes scattered cells, typed numbers, percentages, several columns and other tabs. AVERAGEA differs only in how it treats text and TRUE/FALSE.

### Average scattered values

The cells do not have to be next to each other. **Separate individual cells or ranges with commas**. To average the KPI of the Sales and Marketing employees only:

> `=AVERAGE(D3,D5,D6,D8,D9,D11)`

![Calculating the average of scattered cell values across a sheet in Google Sheets.](https://cdn.owox.ai/www/webflow/67882b7e1299f137d7d758d1_56924.png/public)

When the list of cells follows a rule (“every Sales and Marketing row”), AVERAGEIF or AVERAGEIFS is safer than picking the cells by hand: it keeps working when rows are added.

### Combining values and cell references with AVERAGE

A typed number can sit next to a range in the same formula. Suppose one salary is not in the sheet yet. To include it in the average:

> `=AVERAGE(C3:C12,6523)`

![The use of the AVERAGE function with a mix of direct values and cell references.](https://cdn.owox.ai/www/webflow/67882b7e7d57e974d0fed90d_56928.png/public)

### Calculating average percentages

Percentages are numbers, so AVERAGE works on them as usual. With the KPI values formatted as percentages:

> `=AVERAGE(C3:C12)`

![Finding the average of percentage values in Google Sheets.](https://cdn.owox.ai/www/webflow/67882b7e5f474a4ff713593f_56934.png/public)

One caution: this is the average of the percentages, with every row weighing the same. If the rows stand for groups of different size, such as conversion rates of campaigns with different traffic, divide the totals instead, or weight the average with SUMPRODUCT.

### Using AVERAGE for multiple columns

A range can span several columns. AVERAGE then returns **one average of all the numbers in the block**. With the June, July and August salaries in columns C to E, the average summer salary is:

> `=AVERAGE(C3:E12)`

![Computing averages across multiple columns of data in Google Sheets.](https://cdn.owox.ai/www/webflow/67882b7fde9bf765883530e9_56938.png/public)

For a separate average per column, write one AVERAGE per column.

### Using AVERAGE across sheets

AVERAGE can use cells from **other tabs of the same spreadsheet**. Put the tab name and an exclamation mark before the cell, and wrap the name in single quotes if it contains a space. Here John Smith’s salary is in cell C3 of two tabs, **‘July 2024 Salary’ and ‘August 2024 Salary’**.

![Using the AVERAGE function to calculate values across different sheets.](https://cdn.owox.ai/www/webflow/67882b7e430fbe857a302738_56948.png/public)

His average salary for the two months:

> `=AVERAGE('July 2024 Salary'!C3,'August 2024 Salary'!C3)`

![Continuing the example of averaging data from multiple sheets in Google Sheets.](https://cdn.owox.ai/www/webflow/67882b7e6b524d7e252827d7_56952.png/public)

### Using AVERAGE and AVERAGEA with blank cells

Both functions **ignore empty cells**. With KPI values missing for some employees, AVERAGE returns:

> `=AVERAGE(C3:C12)`

![Highlighting the impact of blank cells on the AVERAGE and AVERAGEA functions.](https://cdn.owox.ai/www/webflow/67882b7f82e8e1aac50a22f0_56956.png/public)

And AVERAGEA returns the same:

> `=AVERAGEA(C3:C12)`

![Comparing results of AVERAGE and AVERAGEA functions with datasets containing blank cells.](https://cdn.owox.ai/www/webflow/67882b7e1620c73e5cb5b196_56960.png/public)

With only numbers and empty cells, the two functions **agree**. They part ways as soon as a cell contains text, which AVERAGEA counts as 0. A cell that looks empty but holds a space or an empty string from a formula is text to AVERAGEA.

### Using AVERAGEA when all values are Boolean

A checkbox is a Boolean value: a **checked box is TRUE, an unchecked box is FALSE**. With a checkbox per employee showing whether they work from the office:

> `=AVERAGEA(C3:C12)`

![Showing how AVERAGEA processes a dataset composed entirely of boolean values.](https://cdn.owox.ai/www/webflow/67882b7f7f315a0fda892e1d_56966.png/public)

Here, the AVERAGEA function treats a checked box (TRUE) as 1 and an unchecked box (FALSE) as 0. In this example, the AVERAGEA function **returns 0.6, meaning that 60%** of the employees work from the office.

### Seeing an average without a formula

For a quick look you do not need a formula. **Select the cells**, and Google Sheets shows a calculation in the bottom right corner of the window. Click it and choose **Avg** to see the average of the selection, next to the sum, minimum, maximum and count.

![Google Sheets with a salary column selected and the bottom right menu showing Sum, Avg, Min, Max and Count for the selection.](https://cdn.owox.ai/www/webflow/67882b7fae7d8680cf41ba98_56970.png/public)

The number is not stored anywhere; it disappears when you click another cell.

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## AVERAGEIF and AVERAGEIFS criteria: text, dates, comparisons and wildcards

The criterion decides which rows are averaged. It can be a text, a date, a comparison or a pattern.

### AVERAGEIF criteria in the form of text

A text criterion in quotes **matches the cells that contain exactly that text**, in upper or lower case. To get the average KPI of the Business Development department:

> `=AVERAGEIF(B3:B12,"Business Development",C3:C12)`

![Demonstrating the use of text criteria in the AVERAGEIF function in Google Sheets.](https://cdn.owox.ai/www/webflow/67882b7f393e4571ee3f86bc_56978.png/public)

### AVERAGEIF criteria for date

A date works as a criterion too. To get the average salary paid on August 24th, 2024, with the payment dates in column D:

> `=AVERAGEIF(D3:D12,"24/08/2024",C3:C12)`

![Applying date criteria in the AVERAGEIF function for targeted calculations.](https://cdn.owox.ai/www/webflow/67882b7e275555f5786757ad_56982.png/public)

A date typed as text is read according to the spreadsheet’s locale: “24/08/2024” works in a day-first locale and fails in a month-first one. `DATE(2024,8,24)` as the criterion means the same everywhere. For everything after a date, join an operator to it: `">"&DATE(2024,8,24)`.

### Using comparison operators in AVERAGEIF function

A comparison operator in front of the value turns the criterion into a threshold:

*   **\=** equal to
*   **<>** not equal to
*   **<** less than
*   **\>** greater than
*   **<=** less than or equal to
*   **\>=** greater than or equal to

To get the average salary of the employees who earn less than 6,500:

> `=AVERAGEIF(C3:C12,"<6500")`

![Using comparison operators like greater than or less than in the AVERAGEIF function.](https://cdn.owox.ai/www/webflow/67882b7fb827d560b12117af_56988.png/public)

There is no average\_range here, so the same column is tested and averaged.

### Using AVERAGEIF to skip zeros

AVERAGE counts zeros, and a zero that really means “no data” pulls the average down. The criterion **“<>0”**, not equal to zero, leaves the zeros out:

> `=AVERAGEIF(C3:C12,"<>0")`

![Configuring AVERAGEIF to exclude zero values from the calculation.](https://cdn.owox.ai/www/webflow/67882b7f361ff094f7cfeca4_56991.png/public)

Use it only when a zero stands for a missing value. If zero is a real result, such as a day with no sales, it belongs in the average.

### Using the AVERAGEIF function with wildcards

Wildcards match part of the text:

*   **Question mark (?):** exactly one character.
*   **Asterisk (\*):** any number of characters.
*   **Tilde (~):** placed before ? or \* to match that character itself.

To average the salaries of every employee whose name starts with “John”, type the pattern **John\*** in cell E3 and use it as the criterion:

> `=AVERAGEIF(B3:B12,E3,C3:C12)`

![Explaining wildcard usage in the AVERAGEIF function for flexible criteria matching.](https://cdn.owox.ai/www/webflow/67882b7f8ebbfe189915ac4b_56993.png/public)

The pattern can also be typed straight into the formula: `=AVERAGEIF(B3:B12, "John*", C3:C12)`.

### Using multiple conditions with AVERAGEIFS

Every condition of an AVERAGEIFS must be true for a row to be included. To get the average salary of the Marketing employees whose KPI is below 80:

> `=AVERAGEIFS(C3:C12,B3:B12,"Marketing",D3:D12,"<80")`

![Multiple criteria application in the AVERAGEIFS function.](https://cdn.owox.ai/www/webflow/67882b8092e90ac62da37991_56995.png/public)

All the ranges must be the same size. If no row meets all the conditions, the function returns #DIV/0!.

### Using AVERAGE with ARRAYFORMULA and IF

An IF inside AVERAGE, wrapped in [ARRAYFORMULA](/blog/articles/array-formulas-google-sheets), averages the rows that pass a test. With regions in column B, sales in column D and the region to report on in cell F3:

> `=ARRAYFORMULA(AVERAGE(IF(B3:B12=F3,D3:D12)))`

![Combining the AVERAGE function with ARRAYFORMULA to calculate group-wise averages.](https://cdn.owox.ai/www/webflow/67882b7f90ec8970f82dd64b_56997.png/public)

Let’s explain the formula:

*   **IF(B3:B12=F3,D3:D12):** Checks each cell of B3:B12 (the regions) against the region in F3. Where it matches, it returns the sales figure from D3:D12; where it does not, it returns FALSE, which AVERAGE ignores.
*   **ARRAYFORMULA:** This allows the formula to handle an array (range of cells) instead of just a single cell.
*   **AVERAGE:** This calculates the average of the sales figures returned by the IF function.

Change the region in F3 and the average follows. For a plain match like this one, `=AVERAGEIF(B3:B12, F3, D3:D12)` gives the same result and is easier to read. The IF form is worth it when the test is a calculation that a criterion cannot express.

## Why an average is wrong or returns an error

### Resolving #DIV/0! error with blank cells

⚠️ **Error:** The #DIV/0! error occurs because there are no numeric values in the range, leading the AVERAGE function to attempt dividing by zero.

![Addressing the #DIV\_0! error caused by blank cells in average calculations.](https://cdn.owox.ai/www/webflow/67882b7f4a702a7b905ea407_57001.png/public)

**✅ Solution:** Ensure the range contains relevant data, or use this formula:

> `=IFERROR(AVERAGE(C3:C12), "No data")`

The formula shows “No data” until the range has at least one number.

### Fixing #DIV/0! error when numbers are formatted as text

⚠️ **Error:** The #DIV/0! error occurs because the cells you’re trying to average are recognized as text instead of numbers.

![Fixing #DIV\_0! errors due to numeric values being stored as text.](https://cdn.owox.ai/www/webflow/67882b7e2f256afc0e89ac26_57003.png/public)

✅ **Solution**: Convert the text back into numbers. Select the range and choose **Format → Number → Automatic**, and remove anything that keeps a cell as text, such as a leading apostrophe, a space or a currency symbol. If the source cannot be edited, convert inside the formula: `=AVERAGE(ARRAYFORMULA(VALUE(C3:C12)))`.

The quieter version of this problem is worse: when only **some** of the numbers are stored as text, there is no error. AVERAGE skips them and returns a plausible, wrong result.

### An error inside the range

![Troubleshooting common errors encountered while calculating averages in Google Sheets.](https://cdn.owox.ai/www/webflow/67882b7f51b2df713f66b5dc_57005.png/public)

⚠️ **Error:** If a cell in the range contains an error such as #N/A, AVERAGE returns that error instead of a number.

✅ **Solution**: Fix the cell that produces the error if you can. To average around it, use AVERAGEIF with a criterion that the error does not meet:

> `=AVERAGEIF(C3:C12,"<>#N/A")`

![Providing solutions to additional errors during average calculations in Google Sheets.](https://cdn.owox.ai/www/webflow/67882b7e56aa364e88ee8660_57007.png/public)

To skip every kind of error, a numeric criterion works, because an error is never greater than zero:

> `=AVERAGEIF(C3:C12,">0")`

![Summarizing error resolutions for average calculations in Google Sheets.](https://cdn.owox.ai/www/webflow/67882b7e4e363a318da82e98_57009.png/public)

Be aware of what this criterion also leaves out: zeros and negative numbers. Use it only when all valid values are positive.

### The average is skewed by a few extreme values

⚠️ **Error:** Nothing is broken, but one very large or very small value moves the average away from what is typical.

✅ **Solution:** Report the [median](/blog/articles/max-min-median-functions-google-sheets) next to the average, or use `=TRIMMEAN(C3:C12, 0.2)`, which leaves out the highest and lowest 10% of the values before averaging. To keep bad entries out in the first place, restrict the column to numbers with [data validation](/blog/articles/data-validation-google-sheets).

## Related Google Sheets functions

Functions that are often used next to AVERAGE, each with its own guide:

*   [VLOOKUP:](/blog/articles/vlookup-in-google-sheets) Searches for a value in the first column of a range and returns a value in the same row from a specified column.
*   [UNIQUE Function](/blog/articles/unique-function-google-sheets): Extracts unique values from a specified range or array, eliminating duplicates.
*   [Pivot Tables:](/blog/articles/pivot-tables-google-sheets) Summarizes large data sets and allows dynamic reorganization and analysis of data through a tabular format.
*   [MATCH Function](/blog/articles/google-sheets-match-function): Returns the position of a specified item in a range.
*   [COUNT and COUNTA](/blog/articles/count-and-counta-in-google-sheets): COUNT calculates the number of cells with numerical data, while COUNTA counts all non-empty cells.
*   [INDEX and MATCH](/blog/articles/index-and-match-google-sheets): Combines INDEX and MATCH functions for advanced lookups, replacing more complex VLOOKUP scenarios.
*   [COUNTIF and COUNTIFS:](/blog/articles/countif-and-countifs-google-sheets) COUNTIF counts the cells that meet one condition, COUNTIFS the cells that meet several.
*   [PERCENTRANK:](/blog/articles/percentrank-function-google-sheets) Returns the percentage rank of a value within a dataset.
*   [QUARTILE:](/blog/articles/quartile-function-google-sheets) Returns the value at a given quartile of a dataset.

## An average is only as good as the rows under it

AVERAGE divides a total by a count, and both depend on which rows are in the range. An export that silently stops at a row limit, a month pasted twice, test orders nobody removed: the formula has no way to notice, and an average hides such problems better than a total does, because the result still looks reasonable.

The [OWOX Data Marts extension for Google Sheets](https://workspace.google.com/marketplace/app/owox_data_marts/94902851409) covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.

![Google Sheets with the OWOX Data Marts extension open beside an ad spend table: the Unified Ad Spend data mart, nine of fourteen columns ticked and a Save & Run button – the rows in the sheet come from a governed data mart, not a pasted export](https://cdn.owox.ai/www/content-marketing-article/average-functions-google-sheets/screens/1.png/public)

What changes for the person who maintains the sheet:

*   **Refresh replaces re-export.** _Refresh current report_ in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
*   **The source is written on the sheet.** The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
*   **No SQL and no warehouse access needed.** The analyst decides which data marts and columns are available. You choose from that list.

The extension does not write or change the analyst’s SQL, and it does not replace AVERAGEIFS: you still choose what to average and by which condition. The rows it is calculated on are the ones the analyst defined, the same for everyone who opens the report. The extension comes with the OWOX Cloud plans of [OWOX Data Marts](https://www.owox.com/app-signup); [spreadsheet reporting](/features/spreadsheet-reporting) describes both ways data reaches a sheet.

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

When should you avoid using AVERAGE in Google Sheets?

You should avoid using the AVERAGE function in Google Sheets when your dataset contains outliers, extreme values, or non-numerical data, as these can skew the results, leading to a misleading representation. For example, if your data includes a few significantly high or low values, the AVERAGE might not accurately reflect the typical value in your dataset.In such cases, consider using the MEDIAN function, which provides a better central value that isn't affected by outliers. Additionally, AVERAGE is not suitable for categorical data, as it's designed for numerical calculations only.

How do I skip zeros when averaging data using the AVERAGEIF function in Google Sheets?

To skip zeros when averaging data using the AVERAGEIF function in Google Sheets, you can set the condition to exclude zeros. For example, if your data range is A1 to A10, you would use the formula =AVERAGEIF(A1:A10, "<>0"). This formula calculates the average of all values in the range that are not equal to zero, effectively skipping any zeros in the dataset.

What should I do if I encounter the #DIV/0! error when using the AVERAGE function in Google Sheets?

If you encounter the #DIV/0! error when using the AVERAGE function in Google Sheets, it usually means that the function is trying to divide by zero, which happens when there are no numeric values in the selected range. To fix this, you can wrap the AVERAGE function in an IFERROR function. For example, you can use =IFERROR(AVERAGE(A1:A10), "No data"). This formula will display "No data" instead of the #DIV/0! error when the range contains no valid numbers​.

When to Use the AVERAGEIF function?

Use the AVERAGEIF function when you want to calculate the average of numbers that meet a specific condition. It’s ideal for situations where you need to focus on certain data points within a larger dataset, such as finding the average of sales figures above a certain threshold or for a specific category. This function helps you narrow down and calculate the mean only for the data that matters most to your analysis.

What is the difference between the AVERAGE, AVERAGEA, and AVERAGEIF functions in Google Sheets?

The AVERAGE function calculates the mean of a numeric range, ignoring non-numeric values. AVERAGEA differs by including all values, treating text as 0, and evaluating TRUE as 1 and FALSE as 0. The AVERAGEIF function goes further by averaging only the numbers in a range that meet a specific condition. These functions serve different purposes depending on the dataset's requirements.

How do you use the AVERAGE function in Google Sheets to calculate the mean of a dataset?

To use the AVERAGE function in Google Sheets, start by selecting the cell where you want the average to appear. Type =AVERAGE(, then select the range of cells that contain your data. After selecting the range, close the parentheses and press Enter. The function will calculate and display the mean of the selected data range.

## Who wrote this

![Vadym Kramarenko](https://cdn.owox.ai/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public)

[Vadym Kramarenko](/team/vadym-kramarenko) · Growth Marketing Manager

Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks.

[LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko)

[Google Sheets Tips](/blog/topics/google-sheets-tips)

## Related articles

[Google Sheets Tips · Pivot tables in Google Sheets: how to create and use them · October 2, 2026](/blog/articles/pivot-tables-google-sheets)

[Google Sheets Tips · UNIQUE function in Google Sheets: syntax, examples and errors · October 2, 2026](/blog/articles/unique-function-google-sheets)

[Google Sheets Tips · QUERY function in Google Sheets: syntax, clauses, examples · October 2, 2026](/blog/articles/query-function-google-sheets)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google Sheets Tips](https://www.owox.com/blog/topics/google-sheets-tips) — /blog/topics/google-sheets-tips.md
- [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md
- [IF function](https://www.owox.com/blog/articles/if-function-google-sheets) — /blog/articles/if-function-google-sheets.md
- [IFS function](https://www.owox.com/blog/articles/ifs-function-google-sheets) — /blog/articles/ifs-function-google-sheets.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [ARRAYFORMULA](https://www.owox.com/blog/articles/array-formulas-google-sheets) — /blog/articles/array-formulas-google-sheets.md
- [median](https://www.owox.com/blog/articles/max-min-median-functions-google-sheets) — /blog/articles/max-min-median-functions-google-sheets.md
- [data validation](https://www.owox.com/blog/articles/data-validation-google-sheets) — /blog/articles/data-validation-google-sheets.md
- [VLOOKUP:](https://www.owox.com/blog/articles/vlookup-in-google-sheets) — /blog/articles/vlookup-in-google-sheets.md
- [UNIQUE Function](https://www.owox.com/blog/articles/unique-function-google-sheets) — /blog/articles/unique-function-google-sheets.md
- [Pivot Tables:](https://www.owox.com/blog/articles/pivot-tables-google-sheets) — /blog/articles/pivot-tables-google-sheets.md
- [MATCH Function](https://www.owox.com/blog/articles/google-sheets-match-function) — /blog/articles/google-sheets-match-function.md
- [COUNT and COUNTA](https://www.owox.com/blog/articles/count-and-counta-in-google-sheets) — /blog/articles/count-and-counta-in-google-sheets.md
- [INDEX and MATCH](https://www.owox.com/blog/articles/index-and-match-google-sheets) — /blog/articles/index-and-match-google-sheets.md
- [COUNTIF and COUNTIFS:](https://www.owox.com/blog/articles/countif-and-countifs-google-sheets) — /blog/articles/countif-and-countifs-google-sheets.md
- [PERCENTRANK:](https://www.owox.com/blog/articles/percentrank-function-google-sheets) — /blog/articles/percentrank-function-google-sheets.md
- [QUARTILE:](https://www.owox.com/blog/articles/quartile-function-google-sheets) — /blog/articles/quartile-function-google-sheets.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [spreadsheet reporting](https://www.owox.com/features/spreadsheet-reporting) — /features/spreadsheet-reporting.md
- [Google Sheets Tips · QUERY function in Google Sheets: syntax, clauses, examples · October 2, 2026](https://www.owox.com/blog/articles/query-function-google-sheets) — /blog/articles/query-function-google-sheets.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md