Akib Bin Rashid
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.
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.
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.
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,
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.
Then use the Fill Handle to AutoFill. Excel will calculate the class average for Math and English.
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
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.
Similar Readings
- Excel Formula to Find Displaced Moving Average (With Easy Steps)
- How to Calculate Average of Multiple Columns in Excel (6 Methods)
- [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)
- Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
- Moving Average Formula in Excel (8 Uses with Examples)
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.
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.
Then use the Fill Handle to AutoFill up to E14. You will get the average number for Math and English.
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.
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:
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.
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.
