Whether you’re performing some simple calculations for school, dipping into more difficult statistics, or building a presentation for your office, the Google Sheets AVERAGE formula can come in very handy. There are plenty of applications you could use it for, whether using it by itself or nesting it with other functions. To use it you simple have to type the function name and select the range you wish to find the average of. But if you need a step-by-step guide, read on to learn more.
What Is Average?
The average of numbers in maths is defined as equal to the sum of numerical values divided by the total number of values. It is often also called the mean. Along with mode and median, the average is one of the central values in a set of data.
Averages are commonly used in real-life scenarios. For example, you can find the common score in a class of 30 students or the average sales from a team in a company.
AVERAGE Function in Google Sheets Syntax
Before we take a look at some examples of AVERAGE in Google Sheets, let’s look at the formula for average in Google Sheets. The formula is:
=AVERAGE(value1, valueN)
The formula only has one type of parameter. However, there can be multiple numbers depending on the number of values you wish to take the average of. The parameters are:
- value1: this parameter defines the first range or value to consider when finding the average value.
- valueN: this parameter defines the additional ranges or values to be considered when finding the average value.
Although the AVERAGE function takes a maximum of 30 parameters. Any text added to the value parameters will be ignored. If you want the AVERAGE function to consider text values as zero, use the AVERAGEA function in Sheets.
The AVERAGE function will return the mean of the value parameters meaning that the sum of the value parameter is divided by the number of value parameters. If you wish to learn how to calculate the median of values, use the QUARTILEÂ or MEDIAN function in Google Sheets instead.
How to Find the Average in Google Sheets
Here are a few examples showcasing the AVERAGE function in Google Sheets.
How to Average a Column in Google Sheets
In this example, we have a column with ten values. We want to find the average. Remember how I showed the method for calculating compound interest? I’ll do the same for finding the average. It’s easiest when I break it down into steps.
Here is how to calculate average in Google Sheets:
- Click on the cell where you wish to enter the formula.
- Enter =AVERAGE(
- Now, enter the value parameter. We have a single column containing all the values in this case, so we write it as A2:A11.
- Add a closing bracket to finish the formula and press Enter to execute it.
How to Average Scattered Values
Alternatively, if you have values in separate locations in the spreadsheet, you can AVERAGE them by adding the cell reference in the formula. In this example, we have different values scattered around the spreadsheet. Here is how to take the average in Google Sheets for scattered values:
- Click on the cell where you wish to enter the formula and enter the initial part of the formula, which is =AVERAGE(.
- Now, enter the value parameter. You can do this manually, or you can select the cell address. To do this, click to show the text cursor in the formula bar. Now, click on the cell containing the value you wish to average. The cell address will be added automatically.
- Add a comma and click on the cell location containing the second value.
- Repeat the previous step to add the remaining cell addresses.
- Add a closing bracket and press Enter to execute the formula.
How to Do Average in Google Sheets With Blank Cells
Google Sheets will get all the values in a range if you add a cell range into the AVERAGE function. However, if the cell range has empty cells or cells containing text, then Google Sheets will ignore these cells.
In this example, we have a column with seven values to be considered for taking AVERAGE. However, two of the cells in the selected range are blank, while the other has plain text. Google Sheets will only consider the cells containing numerical values in the calculation. However, Google Sheets will consider cells that contain a 0.
Note:Â You can also use the AVERAGE formula to Calculate Weighted Average in Google Sheets
When Should You Not Use Google Sheets AVERAGE Formula?
There are a few occasions where you shouldn’t use the Google Sheets AVERAGE function. One such occasion is when there is a list of numbers containing both negative and positive values. In this case, AVERAGE will output inaccurate and inconsistent results. In this case, you can use other AVERAGE formulas.
In these situations, you can nest the AVERAGE function into other functions or use an alternate AVERAGE function, like the ones below.
Similar Formulas to the AVERAGE Function in Sheets
You can use similar formulas instead of the AVERAGE function to perform mathematical calculations often closely associated with finding the average. Let’s take a brief look at some of them.
Google Sheets AVERAGEIF Function
The Google Sheets AVERAGEIF function can calculate the average of a set of data if it meets a specific condition. Here is the syntax for the AVERAGEIF function:
=AVERAGEIF(criteria-range, criterion, avg-range)
The formula requires three parameters to work.
- The criteria-range defines the category set containing the values whose average should be taken.
- The criterion parameter defines the condition you want to test against the criteria range. This parameter can be a number, expression, or text.
- The avg-range parameter is an optional parameter used to define the values you want to calculate the average of.
For a detailed explanation of the function and examples, read our article on using the AVERAGEIF Google Sheets function.
Google Sheets MEDIAN Function
The MEDIAN function in Sheets can be used to return the median value in a dataset. The function works similarly to the AVERAGE function. Here is the syntax for the formula:
=MEDIAN(value1, valueN)
The value parameters define the range or numerical values you wish to find the median. Like the AVERAGE function, the MEDIAN function takes a maximum number of 30 parameters, but Google Sheets supports an arbitrary number of values.
Google Sheets MODE Function
The MODE function works similarly to the MEDIAN and AVERAGE formula in Sheets. Here is the syntax for the MODE function:
=MODE(value1, valueN)
The value parameters define the numerical values or the range containing the numbers you want to find the mode of. Google Sheets supports an arbitrary number, but the formula is specified to take a maximum of 30 parameters.
Related:Â How to Calculate Moving Average in Google Sheets
Frequently Asked Questions
When Should You Not Use AVERAGE in Google Sheets?
You should avoid the AVERAGE function in scenarios with numerical and text in the number cells, as the AVERAGE formula will not count text values towards the average. You should also avoid using the AVERAGE function in datasets with negative and positive values, as the value calculated will be inaccurate.
What Are Some Similar Formulas to AVERAGE in Google Sheets?
There are several formulas that you can use other than AVERAGE in Sheets. Some of these functions include AVERAGEIFS, MEDIAN, MODE, and AVERAGEA. The AVERAGEA function calculates the numerical average in a text, whereas the AVERAGEIFS will return the average based on multiple criteria.
Wrapping Up
If you’ve performed calculations for averages in the past, the Google Sheets AVERAGE formula should be fairly easy to wrap your head around. You simple have to type the function name, then select the range you want to find the average of, simple. If you found this guide useful, you may want to also check out some of our related content below.
Related:
