Dataset Overview
We’ll use the following dataset to calculate the class average in Excel. The dataset contains 7 students with their marks in Physics, Math, and English. We will calculate the class average for each subject.
What is Class Average?
The class average represents the mean of the marks for all students in a particular subject. For instance, if there are 5 students in a class, and their Physics marks are 78, 56, 95, 77, and 87, the class average for Physics would be calculated as follows:
Class Average (Physics) = (78+56+95+77+87)/5 = 78.6.
Method 1 – Basic Method
To calculate the class average for Physics:
- Select cell C14.
- Enter the formula:
-
- This formula adds the values from cells C6 to C12 and divides the total by the number of students (in cell E3).
- Press ENTER to get the average value in cell C14.
- Use the Fill Handle to AutoFill up to E14 for Math and English averages.
Related Content: How to Calculate Average in Excel Excluding 0
Method 2 – SUM Function
Calculate the class average for Physics:
- Select cell C14.
- Enter the formula:
=SUM(C6:C12)/$E$3
-
- Here, SUM(C6:C12) adds the numeric values in the range.
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.
- Press ENTER.
- The result is 85.29 (Physics class average).
- AutoFill to compute Math and English averages.
Related Content: How to Calculate Average of Averages in Excel
Method 3 – AVERAGE Function
- Select cell C14.
- Enter the formula:
-
- This function calculates the average of the numeric values in C6:C12.
- Press ENTER to find the Physics class average.
- AutoFill to determine Math and English averages.
Read More: Average Attendance Formula in Excel
Method 4 – Using AutoSum
- Select cell C14.
- Go to Formulas Tab, select AutoSum and click on Average.
- Specify the cell range (C6:C12).
- Press ENTER to get the Physics class average.
- AutoFill for Math and English averages.
Read More: How to Average Negative and Positive Numbers in Excel
Method 5 – SUBTOTAL Function
In a list or database, the SUBTOTAL function provides subtotals based on various functions (such as AVERAGE, SUM, etc.).
- To calculate the class average for Physics:
- Select cell C14.
- Enter the formula:
-
- Here, the number 1 corresponds to the AVERAGE function.
- Excel will calculate the average of the values in the range C6:C12.
- Press ENTER to get the Physics class average.
- Use the Fill Handle to AutoFill up to E14 for Math and English averages.
Read More: How to Do Subtotal Average in Excel
Method 6 – Keyboard Shortcut
Suppose you want to calculate the class average for Physics:
- Select cell C14.
- Press ALT + M, U, A.
- This activates the AVERAGE function.
- Specify the cell range (C6:C12).
- Press ENTER to find the Physics class average.
- AutoFill to determine Math and English averages.
Practice Workbook
We have attached a practice worksheet.
Practice Workbook
You can download the practice workbook from here:
Related Articles
- How to Calculate Average, Minimum And Maximum in Excel
- How to Calculate Average True Range in Excel
- How to Calculate Average Percentage in Excel
- How to Calculate Average Percentage of Marks in Excel
- How to Calculate Average Revenue in Excel
- How to Calculate Average Quarterly Revenue in Excel
- How to Calculate Average Share Price in Excel
- How to Calculate Average Length of Stay in Excel
- How to Calculate Average Price in Excel
<< Go Back to Excel Average Formula Examples | How to Calculate Average in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
