How to Calculate Class Average in Excel (6 Easiest Methods)

Get FREE Advanced Excel Exercises with Solutions!

Excel is one of the most useful tools in our daily life. We certainly depend on Excel for various purposes. Excel makes our tasks easier than ever. For example, calculating the class average manually is surely a long and tedious task. But in Excel, we can do it without much hassle. In this article, I am going to show you 6 methods on how to calculate a class average in Excel.


We are going to use the following dataset to calculate the class average in Excel. Here we have 7 students with their marks in Physics, Math, and English. We will calculate the class average for each subject.

how to calculate class average in Excel


What is Class Average?

Class Average refers to the mean of the marks of all students in a class in a particular subject. For example, if there are 5 students in a class and their marks in Physics are 78, 56, 95, 77, and 87, then the class average of Physics will be (78+56+95+77+87)/5 = 78.6.


How to Calculate Class Average in Excel: 6 Easiest Methods

In this article, we are going to show you the 6 easiest methods to calculate a class average in Excel. Check the section below for more details.

1. Basic Method on How to Calculate Class Average in Excel

We will calculate the class average using the basic method in this section. Suppose you want to calculate the class average for Physics. To do so,
First, select C14 and write down the formula below.

=(C6+C7+C8+C9+C10+C11+C12)/$E$3

how to calculate class average in Excel

This formula will give you the average of the values of the cells from C6 to C12. Here, we have added all the values in the range C6:C12 and then divided the total by the total Number of Students which is in cell E3. We have used Absolute Cell Reference for cell E3, that’s because we want to keep the cell fixed to use the Fill Handle to AutoFill the formula for the rest of the cells.
Then press ENTER. You will get the average value in cell C14.

how to calculate class average in Excel

Then use the Fill Handle to AutoFill up to E14. You will get the average for Math and English.

Related Content: How to Calculate Average in Excel Excluding 0


2. SUM Function to Calculate Class Average in Excel

The SUM Function adds all the numbers in the selected range and returns the total value. We can easily calculate class average in Excel using this SUM Function.
Here, we will first add all the marks using the SUM Function. And then we will divide the total marks by the Number of Students.
Let’s assume that we need to calculate the class average for Physics. To do so,
First, select cell C14. Then write down the formula,

=SUM(C6:C12)/$E$3
how to calculate class average in Excel

Formula Breakdown:

SUM(C6:C12) —>  It will sum the numeric values ranges from C6:C12.
         Output: 597
SUM(C6:C12)/$E$3 —> Total Sum is divided by the Number of Students to Calculate the class average.
         Output: 85.29
         Explanation: 85.29 is the class average for Physics.

Then press the ENTER key. You will get the class average for Physics.

how to calculate class average in Excel
Then use the Fill Handle to AutoFill. Excel will calculate the class average for Math and English.

how to calculate class average in Excel

Related Content: How to Calculate Average of Averages in Excel


3. AVERAGE Function to Calculate Class Average in Excel

The AVERAGE function is most probably the best answer to the question of how to calculate class average in Excel. To calculate the class average of Physics using this function,
First, select cell C14. Then write down the following formula

=AVERAGE(C6:C12)
This formula will return the average of the numeric values within the range C6:C12.

how to calculate class average in Excel

Then press ENTER. Excel will calculate the class average for Physics.

Then use the Fill Handle to AutoFill up to E12. You will get the class average for Math and English.

how to calculate class average in Excel using AVERAGE

Read More: Average Attendance Formula in Excel


4. Using AutoSum to Calculate Class Average in Excel

Now I will discuss how to calculate class average in Excel using AutoSum. To calculate the class average for Physics using AutoSum,
Select C14. Then, go to Formulas Tab >> select AutoSum >> select Average.

how to calculate class average in Excel

This will activate the AVERAGE function. Just put the cell range of which you want to calculate the average. Here, it is C6:C12.

how to calculate class average in Excel

Then press ENTER. Excel will get you the class average for Physics.

Then use the Fill Handle to AutoFill up to E14. You will get the average number for Math and English.

how to calculate class average in Excel using AVERAGE

Read More: How to Average Negative and Positive Numbers in Excel


5. SUBTOTAL Function to Calculate Class Average in Excel

In a list or database, the SUBTOTAL function returns a subtotal. While applying this function, we have to choose the number for the AVERAGE function (that is 1)  from the function list and then enter the range of values in the second argument. Look how the function list with the reference number pops up.

how to calculate class average in Excel

Then you will put the range. For instance, in our case, the final formula with the SUBTOTAL function to calculate the class average for Physics in cell C14 will be:

=SUBTOTAL(1,C6:C12)

Here, 1 refers to the AVERAGE function. So Excel will calculate the average of the range C6:C12.

Now press ENTER. Excel will get you the class average for Physics.

Now use Fill Handle to AutoFill up to E14. You will get the class average for Math and English.

Read More: How to Do Subtotal Average in Excel


6. Keyboard Shortcut on How to Calculate Class Average in Excel

Now I will explain how to calculate class average in Excel using the keyboard shortcut. Suppose, you want to calculate the class average for Physics. To do so,
First, select C14. Then press ALT + M, U, A.
This will activate the AVERAGE Function. Just put the cell range of which you want to calculate the average. Here, it is C6:C12.

Then press ENTER. Excel will get you the class average for Physics.

how to calculate class average in Excel

And finally, just use the Fill Handle to AutoFill up to E12. You will get the class average for Math and English. Your final output will look like this,


Practice Workbook

Practice makes a man perfect. To internalize the methods on how to calculate class average in Excel, you should practice a lot. That’s why I have attached a practice worksheet for you all.


Practice Workbook


Conclusion

In this article, I have tried to illustrate all the possible and easy methods how to calculate a class average in Excel. I hope this article will be fruitful for you. Lastly, if you have any suggestions or remarks, please drop them in the comment box.


Related Articles


<< Go Back to Excel Average Formula Examples | How to Calculate Average in Excel | How to Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo