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

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 class average in Excel.


Practice Workbook

This is the dataset that I am going to use to illustrate how to calculate 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 basically 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.


6 Methods on How to Calculate Class Average in Excel

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 Methods)


2. SUM Function to Calculate Class Average in Excel

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 class average.
         Output: 85.29
         Explanation: 85.29 is the class average for Physics.

Then press 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 Percentage Change in Excel (3 Simple Ways)


3. AVERAGE Function to Calculate Class Average in Excel

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


Similar Readings:


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


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: Running Average: How to Calculate Using Excel’s Average(…) Function


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.


Conclusion

In this article, I have tried to illustrate all the possible and easy methods on how to calculate 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.
Excel with us.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo