Published at Thu Aug 22 2024 in

For Teams

How to Calculate and Use Averages in Google Sheets in 2024

Alberto Manassero, Product Growth Manager, Rows

Manually calculating averages in documents like sales reports and expense reports can be time-consuming. This can leave you drowning in numbers, unable to make sense of the trends and insights buried in your financial data.

But, with the AVERAGE formula in Google Sheets, you can quickly analyze your data with just a single click.

In this guide, we will walk you through step-by-step instructions on how to use the AVERAGE, AVERAGEIF, and other average formulas in Google Sheets. From basic calculations to more advanced applications, you'll learn how to automate your average computations, visualize your findings, and optimize your workflow for better results.

What is the Google Sheets Average Formula?

The Google Sheets average function is a fundamental tool used to calculate the mean value of a set of numbers. It is particularly useful when you need to find the central value of a range of data points, such as student grades, sales figures, or any other numerical dataset.

When to use the average function:

The average function is ideal for situations where you want to determine the typical value within a dataset.

For example:

Student grades: If you have a list of grades and want to find the average score.
Sales data: Calculating the average monthly sales to assess performance.
Budgeting: Finding the average spending across different categories.

Syntax and format:

=AVERAGE(range)

range: This group of cells contains the numbers you want to average.

Example:

Imagine you have sales data for six months in cells A1 to A6 and want to calculate the average monthly sales. You would enter the following formula:

=AVERAGE(A1:A6)

This formula will calculate the sum of the numbers in the specified range and then divide it by the number of cells in that range, giving you the average value.

Using the average function simplifies finding the mean value in your data, making it a fundamental tool in any basic spreadsheet analysis.

The spreadsheet where data comes to life

Connected to your business data. Powered by AI. Delightful to share.

Try Rows for Free

How to Use the AVERAGE Formula in Google Sheets: Step-by-Step Guide

The AVERAGE Function —

The most basic way to calculate an average in Google Sheets is to use the AVERAGE() function. This function takes a range of cells as an argument and returns the arithmetic mean of the values in that range.

Syntax

=AVERAGE( followed by the range of cells you want to average, separated by commas.

For example: =AVERAGE(A1:A10)

Here is a step by step guide to help you.

Step 1: Select the cell where you want the average to be calculated and displayed.

Step 2: Type the AVERAGE function.

In the selected cell, type the formula =AVERAGE(to start the AVERAGE function.

Step 3: Select the cells or the range of cells you want to find their average.

After the opening parenthesis, select the range of cells you want to calculate the average. You can do this by clicking and dragging the mouse over the cells, or by typing the cell references manually (e.g., A1:A10).

Step 4: Close the function

After selecting the range, type the closing parenthesis ) to complete the AVERAGE function.

Step 5: Press Enter.

Hitting the Enter key will calculate the average of the selected range and display the result in the cell.

Example 1: Averaging numerical data

Let's say you have the following numbers in cells A1 through A5:

- A1: 10

- A2: 20

- A3: 30

- A4: 40

- A5: 50

Here is how to calculate the average of these numbers:

1. Select the cell where you want the average displayed (e.g., cell A8).

2. Type the formula =AVERAGE(A1:A7)

3. Press Enter.

The result in cell A8 would be 40, which is the average of the numbers 10, 20, 30, 40 and 50.

Example 2: Averaging a large dataset

Suppose you have a large dataset of sales numbers in the range A1 through A1000. To calculate the average of this range:

1. Select the cell where you want the average displayed (e.g., cell C2).

2. Type the formula =AVERAGE(A1:A1000)

3. Press Enter.

The AVERAGE function will calculate the average of all 1,000 sales numbers and display the result in cell C2.

Tips and Considerations:

The AVERAGE function can handle numerical and text data but will only include the numerical values in the calculation.
If there are any empty cells or non-numerical values in the range, the AVERAGE function will exclude them from the calculation.
You can also use the AVERAGE function with individual cell references instead of a range, like =AVERAGE(A1, A2, A3).
The AVERAGE function is commonly used in spreadsheets to summarize and analyze numerical data quickly.

Handling Blank and Text Cells

The standard AVERAGE() function in Google Sheets behaves the same way whether you have blank cells or text cells in the range:

1. Blank Cells: The AVERAGE() function will ignore blank cells and only calculate the average of the numeric values.

2. Text Cells: The AVERAGE() function will also ignore any cells containing text and only calculate the average of the numeric values.

If you want to treat blank cells as zeros when calculating the average, use the standard `AVERAGE()` function and replace the blank cells with zeros before applying the formula. Here's an example:

=AVERAGE(IF(A1:A10="",0, A1:A10))

This formula first checks each cell in the range A1:A10 and replaces any blank cells with a zero, then calculates the average of the resulting values.

Calculating Weighted Averages

In some cases, you may need to calculate a weighted average, where certain values in the range have a greater influence on the final result.

Here is an example of how you can do this in Google Sheets.

In this example,

=SUMPRODUCT(A1:A1000, B1:B1000) / SUM(B1:B1000)

Advanced Average Techniques in Google Sheets

AVERAGEIF and AVERAGEIFS Functions

The AVERAGEIF() and AVERAGEIFS() functions allow you to calculate the average of a range based on specific criteria. This is useful when you need to find the average of a subset of your data.

The AVERAGEIF() function takes the following arguments:

=AVERAGEIF(range, criteria, [average_range])

range: The range of cells to evaluate based on the criteria.
criteria: The condition that the cells in the range must meet.
[average_range] (optional): The range of cells to average. If omitted, the function will use the same range as the first argument.

Example

Let’s assume you want to calculate the average of the "Sort" column (Column C) for all items in the "Type" column (Column B) that are labeled as "Planet."

Here’s how the formula would look:

=AVERAGEIF(B2:B12, "Planet", C2:C12)

This formula checks the range B2:B12 for the criteria "Planet" and then averages the corresponding values in the range C2:C12

If you want to input this formula, follow the steps below:

1. Select the cell you want the output to be. In this example, we are using H4

2. Enter the formula =AVERAGEIF(B2:B12, "Planet", C2:C12) and press Enter.

This will calculate the average for all rows where the Type is "Planet" based on their Sort values. Adjust the ranges as necessary if your data range is different.

The AVERAGEIFS function

The AVERAGEIFS function is similar, but allows you to specify multiple criteria:

The AVERAGEIFS function takes the following arguments:

The "range" parameter specifies the cells to evaluate based on the criteria.

The "criteria" parameter defines the condition that the cells must meet.

The optional "average_range" parameter allows you to specify a different range for the function to calculate the average.

The criteria can check for conditions like whether a number is greater than, less than, or equal to a certain value.

Formula:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

In this case, the function will calculate the average of the "average_range" based on all the specified criteria.

The criteria_range1, criteria_range2, etc. define the ranges where the respective criteria1, criteria2, etc. will be checked.

Overall, these functions provide a flexible way to compute averages for specific subsets of your data.

Example

In this example, if you want to calculate the average `Sort` value for rows where the `Type 1` column (Column B) is "Planet" and `Generation` (Column D) is 1, the formula should look like this:

=AVERAGEIFS(C2:C12, B2:B12, "Planet", D2:D12, 1)

Explanation:

C2:C12 is the range where the average is calculated.

B2:B12 is the criteria range to match "Planet".

"Planet" is the criteria for the `Type 1` column.

D2:D12 is the criteria range for `Generation`.

`1` is the criteria for the `Generation` column.

Visualizing Averages with Charts

Google Sheets makes it easy to visualize your average data using various chart types. Once you have calculated your averages, you can create charts such as line charts, bar charts, or scatter plots to display the data more intuitively and visually appealingly.

To create a chart in Google Sheets:

1. Select the range of cells containing your average data.

2. Click the "Insert" menu and select "chart".

3. Customize the chart settings to suit your needs.

Visualizing your average data can help you easily identify trends, patterns, and outliers, leading to more informed decision-making.

Common Mistakes to Avoid When Using the Google Sheets Average Formula

Avoiding common mistakes when using the Google Sheets average formula will ensure accurate results and save time troubleshooting.

Look out for some of the most common errors:

Incorrect Cell References: Double-check that you are referencing the correct range of cells in your average formula. Mixing up row and column references or forgetting to include all the desired cells can lead to inaccurate averages.
Empty Cells: If there are empty cells within the range you're averaging, the formula will exclude them, skewing the final result. Make sure to remove any blank entries or replace them with zeros if appropriate.
Text in Numerical Ranges: The average formula expects a range of numerical values. If there is any text or non-numeric data within the selected cells, the formula will not work correctly. Review your data thoroughly to remove or convert non numerical entries to numbers.
Inconsistent Data Types: Similarly, be mindful of mixing data types, such as combining numbers, dates, and text. The average function can only properly calculate numeric values, so any inconsistencies will cause errors.

Here’re some of the best ways to avoid mistakes when using Google Sheets Average formula:

Prepare Your Data: Before applying the average formula, ensure your data is clean, consistent, and free of any empty or text-based cells.
Double-Check Cell References: Carefully review the cell range you've specified in the formula to confirm it matches the intended data set.
Use Descriptive Column/Row Labels: Giving clear names to your data ranges can help you easily identify the correct cells to include in the average calculation.
Validate Results: After applying the average formula, cross-check the output against your original data to verify the accuracy of the calculation.

How to use AVERAGE in Rows [Easier Alternative]

Rows is a comprehensive spreadsheet for modern teams that offers a modern UI, built-in integrations for live data ingestion, and native AI capabilities (AI analyst, AI-generated subtitles, native AI functions).

How to use the AVERAGE Function in Rows

You can use the same method and syntax on sheets to calculate averages in Rows.

The most basic way to calculate an average in Rows is to use the AVERAGE() function. This function takes a range of cells as an argument and returns the arithmetic mean of the values in that range.

Syntax

=AVERAGE(followed by the range of cells you want to average, separated by commas).

For example: =AVERAGE(A1:A10)

Step 1: Type in the syntax on a separate cell to calculate the average of a specific range in your spreadsheet.

For this example, I calculated the average of the column tagged “unit price”.

Once you type the syntax, click the “enter tab” on your keyboard and you'll see the average of the data range.

How to use the AVERAGEIF Function in Rows

Returns the average of values in a range if they meet a criterion specified in another range.

Syntax

=AVERAGEIF(range, criterion, [average_range])

range

Range of values to check against the criterion. Must be of the same size (rows and columns) as average_range.

criterion

Pattern or test to apply to the range.

[optional] average_range

Range of values to be averaged.

Step 1. Insert the syntax for AVERAGEIF. Ensure you put the specific criterion in quotes.

The syntax prompts to get an average of numbers greater than or equal to 20. And the result shows an average of 24.

Data aggregation

You can access and aggregate live data from 50+ sources in a very simple manner, thanks to built-in data integrations in various domains:

Marketing: GA4, GSC, Facebook, Instagram, TikTok, etc
Productivity software: OpenAI, Notion, Slack, Email, Translate
Data Warehouse: MySQL, BigQuery, PostgreSQL, Snowflake, Amazon Redshift

And many more. See the example below, that show how to pull data about Google Ads campaigns:

AI-powered automation — Helps you automatically generate averages

With the AI Analyst, you can ask AI to analyze, summarize, transform, and enrich your analysis. Click on the "AI Analyst“ ✨ icon, at the top right corner of any table.

A chat interface will open on the right: you can ask a broad range of questions, from basic spreadsheet commands - taking conditional averages of a column - to more complex tasks, such as slicing, pivoting, or computing metrics about your data.

For example, given a dataset with daily revenue and costs of various marketing campaigns, you can ask the Analyst to add a column with the profit margin. Watch the video below:

In addition, our AI Analyst is instructed to use our native OpenAI functions to perform data enrichment or extraction tasks.

For example, you can ask the AI analyst to run a sentiment analysis on a column with product reviews, or add a column that categorizes addresses into regions, see below:

Want to know more about how our Analyst works? Check out our guide or watch our demo.

Modern UI

Spreadsheets in Rows present a document-like layout with standalone tables and charts stacked vertically and horizontally.
WYSIWYG: the spreadsheet IS the dashboard. All data is rendered as regular spreadsheet tables. You can move elements vertically and horizontally with drag and drop. And if something breaks, you can intervene on that table or chart flexibly, without the need to dive into complex workflows.
View Mode: View mode makes your spreadsheets work like web applications. With input fields, you can help anyone understand how to use your spreadsheet by highlighting the cells that need an input by the user.

The spreadsheet powered by AI

Get automated pivots, slices, and summaries of your data using AI.

Try Rows for Free

Supercharge your sheets with Rows

With Rows, you get access to 50+ built-in data source integrations in various domains.

This makes it easy to ingest data into your table quickly and reduces your team's stress.

With Rows, you can gather data from various sources in your system. This allows for more detailed analyses and helps your team uncover insights they might miss.

Ready to get started with Rows.com? Start using the product right away for free.