Whether you’re doing simple calculations at school, looking up more difficult statistics, or creating presentations at the office, the average formula for Google Sheets can come in very handy. There are many applications where this can be used, whether used alone or nested with other functions. To use it, just enter the function name and select the range you want to average. But if you want a step-by-step guide, keep reading to find out more.
Contents
What Is Average?
The mean of numbers in mathematics is defined as equal to the sum of the numbers divided by the total number of values. Also called the average value. The mean is one of the central values in a set of data, along with the mode and median.
Average is often used in real world scenarios. For example, you can find the common score for a class of 30 students or the average sales for a team within your company.
AVERAGE Function in Google Sheets Syntax
Before looking at some examples of AVERAGE in Google Sheets, let’s take a look at the average formula in Google Sheets. The formula is:
=AVERAGE(value1, valueN)
A formula contains only one type of parameter. However, there may be multiple numbers, depending on how many values you want to average. The parameters are:
- value1: This parameter defines the first range or value to consider when finding the average value.
- valueN: This parameter defines additional ranges or values to be considered when finding the mean.
However, the AVERAGE function accepts up to 30 parameters. Text added to the value parameter is ignored. If you want the AVERAGE function to consider text values as zero, use the AVERAGEA function in your sheet.
The AVERAGE function returns the average of the value parameters, which is the sum of the value parameters divided by the number of value parameters. If you want to know how to calculate the median of values, use the QUARTILE or MEDIAN functions in Google Sheets instead.
How to Find the Average in Google Sheets
Here are some examples demonstrating the AVERAGE function in Google Sheets.
How to Average a Column in Google Sheets
In this example, we have a column with 10 values. I would like to find the average. Here’s how to calculate the average in Google Sheets:
- Click the cell where you want to enter the formula.
- =average(
- Then enter the value parameter. In this case, we have one column that contains all the values, so we write A2:A11.
- Add a closing parenthesis to end the formula and press Enter to run it.
How to Average Scattered Values
Alternatively, if you have values in different places in your spreadsheet, you can average them by adding a cell reference to your formula. In this example, various values are scattered on the spreadsheet. Here’s how to average scattered values in Google Sheets:
- Click the cell where you want to enter the formula and enter the first part of the formula, =AVERAGE(.
- Then enter the value parameter. You can do this manually or choose a cell address. To do this, click to display the text cursor in the formula bar. Now click on the cell containing the values you want to average. Cell addresses are added automatically.
- Add a comma and click the cell position containing the second value.
- Repeat the previous step to add the remaining cell addresses.
- Add a closing parenthesis and press Enter to run the formula.
How to Do Average in Google Sheets With Blank Cells
If you add a range of cells to the AVERAGE function, Google Sheets will grab all the values in the range. However, if the cell range contains empty cells or cells containing text, Google Sheets will ignore these cells.
In this example, there is a column with 7 values that are considered to determine the AVERAGE. However, two cells in the selection are blank and one is plain text. Google Sheets only counts cells that contain numbers. However, Google Sheets considers cells containing 0.
Note: You can also use the AVERAGE formula to calculate weighted averages in Google Sheets
When Should You Not Use Google Sheets AVERAGE Formula?
There are some cases where the Google Sheets AVERAGE function should not be used. One such case is when you have a list of numbers containing both negative and positive values. In this case, AVERAGE will give inaccurate and inconsistent results. In this case, you can use other AVERAGE formulas.
In situations like this, you can nest the AVERAGE function inside other functions, or use an alternative AVERAGE function like the one below.
Similar Formulas to the AVERAGE Function in Sheets
Similar formulas can be used in place of the AVERAGE function to perform mathematical calculations closely related to calculating the average. Let’s take a quick look at some of them.
Google Sheets AVERAGEIF Function
The AVERAGEIF function in Google Sheets can calculate the average of a set of data if certain conditions are met. The AVERAGEIF function has the following syntax:
=AVERAGEIF(reference_range, reference, average_range)
Three parameters are required for this formula to work.
- Criteria-range defines a set of categories containing values that should be averaged.
- The Criterion parameter defines the criteria to test against the reference range. This parameter can be a number, expression, or text.
- The Avg-range parameter is an optional parameter used to define the values over which the average is calculated.
See our article on using the AVERAGEIF Google Sheets function for a detailed explanation of the function and examples.
Google Sheets MEDIAN Function
You can use the sheet’s MEDIAN function to return the median of a dataset. This function works like the AVERAGE function. The formula syntax is:
=MEDIAN(value1, valueN)
The value parameter defines the range or number for which you want the median. Like the AVERAGE function, the MEDIAN function takes up to 30 parameters, but Google Sheets supports any number of values.
Google Sheets MODE Function
The MODE function works like the MEDIAN and AVERAGE formulas in Spreadsheet. The MODE function has the following syntax:
=MODE(value1, valueN)
The value parameter defines a number or range containing numbers to search for modes. Google Sheets supports arbitrary numbers, but formulas are specified to take up to 30 parameters.
How to Calculate Moving Average in Google Sheets
Frequently Asked Questions
When Should You Not Use AVERAGE in Google Sheets?
Do not use the AVERAGE function in scenarios where numeric cells contain numbers and text, because the AVERAGE formula does not count text values in the average. Also avoid using he AVERAGE function on datasets with negative and positive values as the calculated value will be inaccurate.
What Are Some Similar Formulas to AVERAGE in Google Sheets?
In addition to AVERAGE, there are several other formulas that can be used in spreadsheets. These functions include AVERAGEIFS, MEDIAN, MODE, and AVERAGEA. The AVERAGEA function calculates numerical averages in text, while AVERAGEIFS returns averages based on multiple criteria.
Wrapping Up
If you’ve performed average calculations in the past, the AVERAGE formula in Google Sheets should be easy to understand. Just enter the function name and select the range you want to average. If you found this guide helpful, please also check out the related content below.
