The term average is the mathematical calculation, which is defined as the sum of all the numerical quantities divided by the number of the quantities. It is sometimes also referred to as the mean of the numerical values. In this guide, we will learn how we can get the average of the numerical values using the AVERAGE() formula in Google Sheets. In this regard, the following concepts will be illustrated using the practical demonstration:
- How to Use AVERAGE Formula in Google Sheets?
- Find Average Using One Compulsory Argument
- Use of AVERAGE Formula Using Additional Argument
- How to Use AVERAGE() Formula with Scattered Values
- Find Average For Data having Text, Blank, and Numerical Values
- Formulas that Work Similar to AVERAGE Formula in Google Sheets
- Use of MEDIAN() Formula
- Use of AVERAGEIF() Formula
- Use of AVERAGEIFS() Formula
How to Use AVERAGE Formula in Google Sheets?
Concerning the definition of the average of the numerical values, it looks very easy to calculate the average of any number of numerical values. But sometimes, we have to calculate the average of a very large dataset of numerical values. In such cases, you may find yourself helpless, as it is impossible to add one by one when you have hundreds or thousands of numerical values.
Google Sheets has very useful built-in AVERAGE() formulas to find out the average for large data in a few clicks.
The syntax used for the AVERAGE() formula in Google Sheets is as follows:
AVERAGE(value1, [value2, ...])Where,
- value1 is the first value or the cell range that contains the numerical value for which we want to calculate an average value
- value2 is an optional argument and is the additional range of cells containing numerical values for which we wish to calculate an average value
We will execute the AVERAGE formula using one compulsory argument value1 and using both of the compulsory and optional argument value2 on the same numerical values but in two different ways to better understand the formula.
Example 1: Find Average Using One Compulsory Argument
Suppose we have a test result report of a class and data is arranged in a single column in Google Sheets. We will find the average of all marks obtained by the students using the AVERAGE() formula as follows.
Step 1: Select an empty cell
Click on an empty where we want to insert the average value and type the average formula as
=AVERAGE( :
Step 2: Select the cell range
Now select the cell range that contains the values for which we want to calculate the average value, as the value1 argument in the AVERAGE formula:
Step 3: Find the Average
After putting the cell range, close the formula bracket and press Enter. You will get the average of all the values in the selected cell range:
Example 2: Use of AVERAGE Formula Using Additional Argument
Suppose, we split the students in the above example into two sections, and then we will find the average value of all the students. We may take another example, but we same example so that we can check whether the result will match the above example or not because the numerical values are the same in both examples.
Now click on an empty cell and type the AVERAGE formula as mentioned above. Select the cell range that takes the numerical values for which we want to find the average as the value1 argument of the AVERAGE() formula, which is B4:B10 in our example.
Then put a comma and select the other cell range that contains numerical values as the additional argument value2 of the AVERAGE(0 formula, which is D4:D10 in our example:
Now close the bracket and press Enter. Will get the same result as in the previous example:
Example 3: How to Use AVERAGE() Formula with Scattered Values
Sometimes, we need to find the average of the values in Google Sheets for the scattered data or for specific values inside the data. For example, we want to find the average score of a single player in all the matches he participated in.
In this case, the score record of this single player is available in different cells in the Google Sheets, and we need to pick all the cells separately in the AVERAGE() formula as we need to find the average of the scores of this single player:
After putting all the required cell’s reference addresses, close the bracket and press Enter:
Example 4: Find the Average For Data Having Text, Blank, and Numerical Values
Sometimes, we have data in the form of text, numerical values, and empty cells. To find the average of all the numerical values, we do not need to put the reference addresses of all the cells containing numerical values, as we do in the above example.
We just select the range of the cells that contain all the data including text, numerical values, and the empty cells. The AVERAGE() formula will consider the cells that contain the numerical values only and ignore the cells that contain text or are empty:
Formulas that Work Similar to AVERAGE Formula in Google Sheets
The AVERAGE() Formula in Google Sheets is used to calculate an average of the numerical values in a given data. We can also find the average of the numerical values using the following formulas in Google Sheets.
- Use of MEDIAN() Formula
- Use of AVERAGEIF() Formula
- Use of AVERAGEIFS() Formula
We will discuss one by one how these formulas work similarly to the AVERAGE() formula.
1: AVERAGE() Formula Vs MEDIAN() Formula
The MEDIAN() formula is used for calculating the average of the numerical values in Google Sheets. It works in the same way as the AVERAGE() formula does. The syntax used for the MEDIAN() formula is the same as that of the AVERAGE() formula:
MEDIAN(value1, [value2, ...])Where
| Argument | Use |
|---|---|
| value1 | It is the first value or the cell range that contains the numerical value for which we want to calculate an average value |
| value2 | This is an optional argument and is the additional range of cells containing numerical values for which we wish to calculate an average value |
Now, we use the MEDIAN() formula to find the median or average of the numerical values. We will take example 1 of this guide, where we have applied the AVERAGE() formula so that we can check whether the result of both formulas will be the same or not.
Type the MEDIAN() formula in an empty cell and select the cell range, as we did for the AVERAGE() in example 1. Close the bracket and press Enter to get Median. We will see that the result is the same as that of the AVERAGE() formula:
2: Use of AVERAGEIF() Formula
The AVERAGEIF() formula is used to find the average over a specified criterion of the numerical values in Google Sheets. For example, you have data in Google Sheets as the test result of a class having boys and girls. If the data is small, you can find the average of the scores for boys or girls separately. But when there is large data, it is not possible to separate all the categories like boys and girls in our example.
The AVERAGEIF() formula in Google Sheets helps us in finding the average for any specified category in a given data.
The syntax for the AVERAGEIF() formula is as follows:
AVERAGEIF(criteria_range, criterion, [average_range])Where
| Argument | Use |
|---|---|
| criteria_range | It is the range for which the formula searches to check against criterion. |
| criterion | It is the condition under which the average has to be calculated. |
| average_range | It is the argument for the values for which we want to calculate the average value. |
Consider we have a test result of a class which includes both boys and girls. In case, we want to find the average score obtained by boys, we will use AVERAGEIF() formula as follows.
Step 1: Click on an empty cell and type AVERAGE. Google Sheets’ suggested formulas will appear below this cell, click on the AVERAGEIF() formula from them:
Step 2: Select the criteria_range that includes all the categories to search against the criterion, the next argument in the formula. In our example, the criteria_range is the range of data that identifies the gender of the students B4:B19.
Place a comma and select the criterion which the category for which you want to find the average. In our example, we are going to calculate the average of scores obtained by all the boys. For this, select any cell reference address that represents the category of boys, for this we select B4.
Again, place a comma and select the averag_range that includes the all numerical values over which we are going to find the average obtained score by boys. In our example, the average_range is the C4:C19:
Step 3: Close the bracket and press Enter to get the average:
3: Use of AVERAGEIFS() Formula
The AVERAGEIFS() formula works similarly to the AVERAGEIF() formula in Google Sheets, but the key difference i that the AVERAGEIF() formula calculates the average of the values for single criteria and the AVERAGEIFS() formula calculates the average over multiple conditions.
The syntax used for the AVERAGEIFS() formula in Google Sheets is as follows:
AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, ...], [criterion2, ...])Where,
| Argument | Use |
|---|---|
| average_range | It is the argument for the values for which we want to calculate the average value. |
| critera_range | It is the range for which the formula searches to check against criterion 1. |
| criterion1 | It is the condition under which the average has to be calculated. |
| critera_range2 | This is an optional argument used for the additional range to check against criterion2. |
In the above example, Suppose, you want to find the average of the scores for boys whose scores are greater than and equal to 25. This involves two categories to find the average, and we can do that with the help of the AVERAGEIFS() formula.
Select an empty cell, type the AVERAGEIFS() formula, and insert the above-mentioned argument as follows:
Close the bracket and press Enter to get the average value over the specified categories:
Conclusion
Average is defined as the sum of all the numerical quantities divided by the total number of the quantities. It is quite a simple formula, but it is much more difficult to find averages, when have very large data of numerical values. Google Sheets has very useful built-in formulas, with these formulas, we can easily find out the average for a large data in a few clicks as you see in the above examples. Apart from the AVERAGE() formula, there are three more Google Sheets built-in formulas that do the same job in more complicated conditions. These are the MEDIAN() formula, AVERAGEIF() formula, and AVERAGEIFS() formula.