How to Find Average in Google Sheets (8 Easy Ways)

One technique to examine a group of numbers and identify the core value is to calculate the AVERAGE. When a teacher takes a test on a group of students, for instance, he may determine how well the class performed overall by looking at the AVERAGE grade. And we all know that Google Sheets or Excel are the most used software in anywhere for these kinds of tasks. Finding AVERAGE in Google Sheets may turn out much easier than you thought. Following the article, you will get to know some convenient methods on how to find AVERAGE in Google Sheets.


A Sample of Practice Spreadsheet

You can download the Google Sheets used to demonstrate methods in this article from here.


What Is AVERAGE Function in Google Sheets?

Syntax

The Syntax of the AVERAGE function is like-

AVERAGE(value1, [value2, …])

Syntax of average function in google sheets

Arguments

Argument Requirement Function
value1 Required The first value or range to consider when calculating the average value
value2 Required Additional values or ranges to consider when calculating the average value.

Output

The output AVERAGE(A2:A100, B2:B100) will show the average value for the values in cell range A2:A100 and B2:B100.


8 Easy Methods to Find Average in Google Sheets

We will use the following sample dataset to demonstrate these methods accurately. The dataset represents some students’ obtained marks in three subjects.

sample data to find average in google sheets


1. Using Explore Tool

The Explore feature in Google Sheets gives us a basic overview of functions that we may use for a particular range of cells. In brief, if we select a range of cells containing numbers, Explore feature will show us the SUM, AVERAGE, MINIMUM, MAXIMUM etc values for the selected range of cells.

Steps:

  • For example, we will see the AVERAGE of cell range C5:C16 and also C5:E16 in the following dataset.

using explore tool to calculate mean in google sheets

  • First Select cell range C5:C16.

  • At the bottom right of the sheet, you will find the option “Explore”.

  • Press that option and you will see something like this where you will find the AVERAGE value of cell range C5:C16 along with their total SUM, MINIMUM, MAXIMUM values, and the total number of cells that are represented by COUNTA.

  • Now to see the AVERAGE of cell range C5:E16, select the cell range like previously.

use of explore tool to perform average in google sheets

  • Then simply use the “Explore” option like previously and you will find the AVERAGE of those cells also.

explore tool to measure average in google sheets

Read More: How to Average Cells from Different Sheets in Google Sheets


2. Applying Drop Down Menu from Quick View

If we select a particular range of cells containing numbers, a Drop Down menu appears at the bottom right of the sheet where it gives us the Sum, Average, Minimum, Maximum, and total number of cells that is “Count” and the total number of cells containing numbers that is “Count Numbers” for the selected range of cells. If you want to find AVERAGE in Google Sheets within the shortest possible time then this feature is for that.

Steps:

  • Select the cell range of numbers whose average you want to know. Here I have selected Cell Range C5:C16.

applying drop down menu from quick view to know average of a range of cells in google sheets

  • At the bottom right of the sheet, you will find a Drop Down menu beside the Explore tool.

  • Click on the Drop Down menu and you will simply get a quick view of the AVERAGE of Cell Range C5:C16. Mark it, and then it will show the average by default whenever you select a range.

  • Like, here I have selected Cell Range C5:E16 and got a quick view of the AVERAGE for those selected cells.

drop down menu for a quick view of average value in google sheets


3. Using Basic Mathematics Formula

How do we do the calculation of AVERAGE in our exam? We sum all the values and then divide them by the total number of values. Just like that! We will do the same in Google Sheets also. We can find the AVERAGE in Google sheets using basic mathematics in 2 ways-


3.1 Applying Manual Method

In this method, we will add Cells and divide the sum by the total number of Cells.

Steps:

  • Let’s assume, we need to calculate the AVERAGE grade in Maths from the following dataset. The results of students in Maths are shown in Cell Range C5:C16.

Using Basic Mathematics Formula to find average in google sheets

  • We selected Cell C18, in which the AVERAGE value will appear. In Cell C18 we will do the following Mathematics and the AVERAGE will be shown in Cell C18.

=(C5+C6+C7+C8+C9+C10+C11+C12+C13+C14+C15+C16)/12

  • Now if you want to get the AVERAGES for Physics and Biology, you can do the exact same or you can simply drag right selecting the Fill Handle icon as shown in the circled portion.

Using Basic Mathematics Formula to find average in google sheets

  • And you will get the AVERAGE for those also.


3.2 Using SUM Function with Division

If we use the SUM formula then we don’t need to type down Cell numbers or select Cells one by one manually like the previous method.

Steps:

  • We can use the SUM function for the same thing. Here like previously, we want to calculate the AVERAGE grade in Maths from the following dataset. The results of students in Maths are shown in Cell range C5:C16.

Using Basic Mathematics Formula to find average in google sheets

  • We selected Cell C18 like previously, in which the AVERAGE value will appear. In Cell C18 we will apply the following formula-

=SUM(C5:C16)/12

What does the following formula indicate? The SUM function will sum up all the values in Cell Range C5:C16 and then we divide this total sum by 12 because here the number of cells containing numbers is 12.

  • Then drag right selecting the Fill Handle icon as shown in the circled portion.

  • And you will also receive the AVERAGE for those also.

Using Basic Mathematics Formula to find average in google sheets


4. Using AVERAGE Function Manually

The built-in AVERAGE function in Google Sheets returns the average of a selected range of cells. And we can use this function manually through a simple process.

Steps:

  • Suppose, we want to calculate the AVERAGE of Cell Range C5:C16 from the following dataset. First, we selected Cell C18 where the AVERAGE value will appear.

Using AVERAGE Function Manually to calculate average in google sheets

  • At the top at Toolbar, press the Sigma “” option. This is the Drop Down List of important functions of Google Sheets.

  • Select AVERAGE from the Drop Down Menu.

Using AVERAGE Function Manually to calculate average in google sheets

  • In Cell C18 the AVERAGE function will appear.

  • Now, simply select Cell Range C5:C16.

Using AVERAGE Function Manually to calculate average in google sheets

So the final formula will be as follows:

=AVERAGE(C5:C16)
  • Finally press Enter. And here is your AVERAGE in Cell C18.

  • If you want to get the AVERAGE for the other subjects, you don’t need to do the same again because using the Fill Handle icon just Drag Right.

Using AVERAGE Function Manually to calculate average in google sheets

  • And here are the AVERAGES of Physics and Biology in Cell D18 and E18 respectively.


5. Applying AVERAGE Function

Using the AVERAGE function will allow us to continue adding values along a column or row or in a range of cells. We don’t have this opportunity for the other methods shown before.


5.1 For a Range of Cells

To perform the AVERAGE function for a range of cells just go through the following steps-

Steps:

  • Let’s assume, we want to calculate the AVERAGE mark of the first 5 students in “Maths” from the following dataset.

Applying AVERAGE Function to find average in google sheets

  • In Cell E18 type “=AVERAGE(”.

  • Select Cell C5, C6, C7, C8 and C9 with pressing Ctrl.

So the final formula will be as follows-

=AVERAGE(C5,C6,C7,C8,C9)

  • Now just press Enter and then you will get the AVERAGE in Cell E18.

Applying AVERAGE Function to find average in google sheets


5.2 For a Column

Basically, the AVERAGE function is used widely to find AVERAGE along a column in Google Sheets.

Steps:

  • We will use the following dataset as an example.

how to find average for a column in google sheets

  • Here, we will calculate the AVERAGE mark of the students in “Maths”.
  • In Cell C18 type “=AVERAGE(” like previously.

  • Select Cell Range C5:C16.

how to find average for a column in google sheets

  • Press Enter and here is the AVERAGE

So the final formula will be as follows-

=AVERAGE(C5:C16)

  • Now Drag Right using the Fill Handle icon as shown to get the AVERAGE for the other two subjects as well.

how to find average for a column in google sheets

  • AVERAGE marks of Physics and Biology will appear in Cell D18 and E18 respectively.


5.3 For a Row

Performing horizontal AVERAGING is as simple as doing it for a column.

Steps:

  • In the following dataset, we want to calculate the AVERAGE marks for each of the students.

calculating mean for a row in google sheets

  • In Cell F5 simply type “=AVERAGE(” like previously.

  • Select Cell Range C5:E5.

calculating mean for a row in google sheets

  • Press Enter.

So the final formula will be as follows-

=AVERAGE(C5:E5)

  • Drag Down using the Fill Handle icon as shown to get AVERAGE marks of other students as well-

calculating mean for a row in google sheets

  • And all the AVERAGES are calculated along Cell Range F5:F16.

Read More: Calculate Average of Last N Rows in Google Sheets (3 Ways)


5.4 For an Entire Table

Sometimes it is necessary to express the whole progress as an AVERAGE. Finding AVERAGE for an entire table is simple.

Steps:

  • Let’s assume, we want to calculate the AVERAGE mark of the students from the following dataset.

measuring average for a entire table in google sheets

  • In Cell E18 type “=AVERAGE(”.

  • Select Cell Range C5:E16.

measuring average for a entire table in google sheets

  • Simply press Enter and the AVERAGE value will appear in Cell E18.

So the final formula will be as follows-

=AVERAGE(C5:E16)

measuring average for a entire table in google sheets


6. Inserting AVERAGEIF Function

The AVERAGEIF function in Google Sheets returns AVERAGE of a range of cells based on a single criterion.

Steps:

  • Assume, from the following dataset, we want to calculate the AVERAGE mark of students in “Maths”.

how to use AVERAGEIF Function in google sheets to calculate average

  • First, we select Cell G5 where the AVERAGE value will appear. Then we apply the following formula in Cell G5.

=AVERAGEIF(C5:C16,"Maths",D5:D16)

how to use AVERAGEIF Function in google sheets to calculate average

Here, in the formula Cell Range C5:C16 is the Criteria Range from where the function AVERAGEIF will look for the cells for the Criteria subject “Maths”. And cell range D5:D16 is the Average Range from where the function will pick values corresponding to “Maths” and will AVERAGE them.


7. Employing AVERAGEIFS Function

The AVERAGEIFS function in Google Sheets returns AVERAGE of a range of cells based on multiple criteria.

Steps:

  • Suppose, from the following dataset, we want to calculate the AVERAGE mark of students in “English” but only for those who obtained more than 40 marks. In Cell G4 we input the word “English” as we want to calculate the AVERAGE of this subject.

Employing AVERAGEIFS Function for conditional averaging in google sheets

  • In Cell G6 apply the following formula and press

=AVERAGEIFS(D5:D16,C5:C16,G4,D5:D16,">40")

Here, D5:D16 is the Average Range from which the function AVERAGEIFS will pick values based on criterias and AVERAGE them. C5:C16 is the Criteria Range 1 from where the function will look for the Criteria 1 that is “G4”(“English”). D5:D16 is the Criteria range 2 from which the function will look for values corresponding to “English” and also “>40”.

  • Now if you want to calculate the AVERAGE mark of students in “Maths” but only for those who obtained more than 40 marks just simply type “Maths” in Cell G4 and see the magic!

Employing AVERAGEIFS Function for conditional averaging in google sheets


8. Combining COUNTIF and SUM Functions

Previously we have seen some ways of calculating AVERAGE where we used basic mathematics, that is we summed up values from a range of cells and then divided that sum by the number of values. We did the counting of the number of values manually for those methods but if we combine COUNTIF and SUM functions together, we don’t even need to do that!

Steps:

  • Presume, we want to calculate the AVERAGE mark of students in “Math” from the following dataset.

  • Select Cell C18 where the AVERAGE value will appear. Apply the following formula there and press Enter.

=SUM(C5:C16)/COUNTIF(C5:C16,"<>")

Combining COUNTIF and SUM Functions to find average in google sheets

Here, the function SUM sums up the values from Cell Range C5:C16. Then we are using the COUNTIF function for the conditional state. Cell Range C5:C16 is from where the function will pick values following the Criterion “<>” that is if there are any BLANK Cells the COUNTIF function will not count them.

  • Now, just Drag Right using the Fill Handle icon as follows to get the AVERAGE for other subjects also.

  • And that’s it!

Combining COUNTIF and SUM Functions to find average in google sheets

Read More: How to Ignore Blank Cells with AVERAGE Formula in Google Sheets


What to Do If AVERAGE Function Is Not Working in Google Sheets?

Sometimes the AVERAGE function may not show the expected result. This can occur for some silly reasons.


1. Format of the Texts

In the following dataset, we are trying to apply the AVERAGE function in Cell C18 for Cell Range C5:C16 but what it is showing is an Error.

average function not working in google sheets

Why is this error occurring? Select the Cell Range C5:C16 and then at the top of your toolbar, select Format.

Now see whether the number format is selected as “Automatic” or not. If the number format is selected as “Plain text” then the AVERAGE function won’t work.

average function not working in google sheets

Solution:

  • Simply select the Cell Range and then go to Format and set the number format as “Automatic”.

  • Now the AVERAGE function will surely gonna work.

average function not working in google sheets


2. Numbers in Words

If a particular Cell Range carries numbers in digits along with numbers in words then the AVERAGE function may work but will show wrong results.

average function not working in google sheets

Solution:

  • Just try to keep all the numbers in digits and the result will be accurate.


How to Ignore 0 While Calculating Average in Google Sheets?

If we want to ignore Zero “0” while calculating AVERAGE, the AVERAGEIF is simply an outstanding and easy to use function in Google Sheets. So now we’ll show how to find average in Google sheets ignoring zero.

Steps:

  • Suppose, we want to calculate the AVERAGE mark of the students in “Maths” excluding those who obtained 0 from the following dataset.

How to Ignore 0 While Calculating Average in Google Sheets

  • In Cell C18 apply the following formula and press Enter-

=AVERAGEIF(C5:C16,"<>0")

Here, Cell C5:C16 is the Criteria Range from where the AVERAGEIF function will pick the values to calculate AVERAGE, and “<>0” is the Criterion that commands the function not to pick those cells containing “0”.

  • Now simply Drag Right using the Fill Handle icon as shown in order to perform the same for the other two subjects as well.

How to Ignore 0 While Calculating Average in Google Sheets

  • And the function AVERAGEIF will show the AVERAGES for “Physics” and “Biology” excluding “0” containing cells.


Conclusion

The built in AVERAGE function along with some amazing features like Explore, AVERAGEIF or AVERAGEIFS in Google Sheets will surely gonna help us to do our calculations instantaneously. In this article, we have demonstrated 8 easy methods of how to find AVERAGE in Google Sheets and hope this may help you with your workings. Visit our site  officewheel.com for more related articles that might help you.


Related Articles

Adrey

Adrey

Hello! This is Raiyan Zaman Adrey, a fresh graduate in BSc in Civil Engineering from Bangladesh University of Engineering and Technology. From my high school I have been using Excel in which I was always interested and had fun and this led me to do more research on Excel and Google Sheets as both are kinda similar platform. I have always been enthusiastic, self-motivated, reliable and hard working person and for a long time, I am trying to improve myself more and more so that I can face any challenging situation and adapt myself to any environment.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo