Foyez Alam
You will find that knowing how to calculate the average percentage of marks in Excel is really handy. You may use this Excel tool to handle your report card recaps easily if you’re a teacher. The procedure for calculating the average percentage of marks in Excel is much the same as for manually calculating it. The issue is that manually calculating it will take a long time. As a result, some people who need to compute the average percentage of marks now turn to Microsoft Excel for assistance.
Here we took an Excel sheet that represents the final exam report of the students against 3 different subjects where each subject carries 100 marks. To represent the information I’ve taken 4 columns these are Name, Physics, Chemistry, and Mathematics respectively.
Download Practice Book
What is Average?
Average is a mathematical operation. Suppose, we have a set of numbers. If we divide the sum of the numbers by how many numbers we have, then we will get the average of those numbers. Here the average of 5,6,3,2,9 is 5.
To express a basic Average Formula, we can write,
Average = (Sum of numbers of a set) / (Total numbers in that set)What is Percentage?
The percentage is a ratio expressed as a fraction of 100. For example, we have a total of 21 balls in a basket and 7 are red among them. So, the percentage of red balls is 30%.
Top 4 Methods to Calculate Average Percentage of Marks in Excel
In this article, we are going to discuss the 4 easiest and most different methods to calculate the average percentage of marks in an Excel Sheet.
1. Calculate Average Percentage of Marks in Excel Using Basic Method
At the very beginning, let’s discuss the basic method to calculate the average percentage of marks in an Excel Sheet. To show examples, we have brought a simple dataset of six students and their respective scores in three tests.
- Now select cell F5
- Then simply put the basic formula to divide the summation of cells C5, D5 and E5 by 300 to get the average percentage of marks of Cris in fraction form in this Excel.
- Putting all the values correctly in the relative cell reference, now click on the ENTER button and get the average percentage of marks of Cris in fraction.
- After that, use Fill Handle to AutoFill the formula for the rest of the cells to get the average percentage of marks for remaining students in fraction form too.
- Finally, after selecting all the cells from F5 to F10, use the following keyboard shortcut.
CTRL + SHIFT + %Immediately after using that keyboard shortcut, you will be able to calculate all the average marks in a perfect percentage form in your Excel sheet.
Read More: How to Calculate Average Percentage Increase in Excel
2. Calculate Average Percentage of Marks in Excel Using SUM Function
You can easily calculate the average percentage of marks in an Excel Sheet by using the SUM function. To show examples, we have brought a simple dataset of six students and their respective scores in three tests.
- Now select cell F5
- Now put the SUM formula in cell F5, divide it 300 and click ENTER.
=SUM(C5:E5)/300Here, C5:E5 is the range where all three marks of Cris are stored, and the average will be executed on this by dividing it by 300.
- Now click on the ENTER button and get the average percentage of marks of Cris in fraction.
- After that, use Fill Handle to AutoFill the formula for the rest of the cells to get the average percentage of marks for remaining students in fraction form too.
- Open the Home tab >> go to General >> select Percentage
Immediately after clicking on the Percentage option, you will be able to calculate all the average marks in a perfect percentage form in your Excel sheet.
Read More: How to Calculate Average Percentage Increase for Marks in Excel Formula
Similar Readings
- How to Determine Triple Exponential Moving Average in Excel
- Calculate Exponential Moving Average in Excel
- How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)
- Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)
- How to Generate Moving Average in Excel Chart (4 Methods)
3. Calculate Average Percentage of Marks in Excel Using AVERAGE Function
The AVERAGE function is categorized under the Statistical functions in Excel. This function returns the average value of a given argument. From the description of the AVERAGE function, you might have understood that the basic use of this function is to find the average of several numbers in an Excel Sheet.
To show examples, we have brought a simple dataset of six students and their respective scores in three tests. For this, you need to insert an extra column of Average before the Average Percentage column.
- Here, we have to select cell F5.
- Then simply type the formula given below:
=AVERAGE(C5:E5)Here, C5:E5 is the range where all three marks of Cris are stored, and the average will be executed on this.
- Now click on the ENTER button and get the average percentage of marks of Cris in fraction.
- After that, use Fill Handle to AutoFill the formula for the rest of the cells to get the average percentage of marks for remaining students in fraction form too.
- Next, select cell G5 and simply type the formula given below:
=F5/100Here, F5/100 will show the average percentage of marks in fraction form.
- Now click on the ENTER button and get the average percentage of marks of Cris in fraction.
- After that, use Fill Handle to AutoFill the formula for the rest of the cells to get the average percentage of marks for remaining students in fraction form too.
- Open the Home tab >> go to General >> select Percentage
Immediately after clicking on the Percentage option, you will be able to calculate all the average marks in a perfect percentage form in your Excel sheet.
Read More: How to Calculate Average Percentage Change in Excel (3 Simple Ways)
4. Calculate Average Percentage of Marks in Excel Using Ribbon
Using AutoSum feature from Ribbon is another convenient method to calculate the average percentage of marks in Excel. For this, you need to insert an extra column of Total before the Average Percentage column.
- Now, select a cell from our dataset Here, we have selected cell F5.
Next, you need to follow the following steps.
- Open the Home tab >> go to Formulas >> select AutoSum
- Then you can see that cells C5, D5 and E5 are automatically taken to calculate the total marks of Cris in those three subjects.
- Now clicking on the ENTER button, you can see that the total marks of Cris in those subjects appear there.
- Then, use Fill Handle to AutoFill the formula for the rest of the cells to get the total marks for the remaining students in fraction form too.
- Next, select cell G5 and simply type the formula given below:
=F5/100Here, F5/100 will show the average percentage of marks in fraction form.
- Now click on the ENTER button and get the average percentage of marks of Cris in fraction.
- After that, use Fill Handle to AutoFill the formula for the rest of the cells to get the average percentage of marks for remaining students in fraction form too.
Next, you need to select all the cells from G5 to G10 and follow the following steps:
- Open the Home tab >> select % (Percentage Icon)
- Immediately after clicking on the % (Percentage Icon), we can see that the average percentage of marks for all the students appears in this Excel
Read More: How to Calculate Grade Percentage in Excel (3 Easy Ways)
Things to Remember
To calculate the average percentage of marks in Excel, you need to calculate the total marks or the average marks first. By then, you can easily calculate the average percentage o marks in Excel.
Practice Yourself
I’ve given a practice sheet in the workbook to practice these explained ways to calculate the average percentage of marks in Excel. You can download it from the link given above.
Conclusion
In this article, I have tried to explain 4 different methods to calculate the average percentage of marks in Excel. You can also hide multiple columns using the same methods. Last but not least, I will be extremely grateful if you comment down below any of your suggestions, ideas, or feedback.
Related Articles
- How to Average Letter Grades in Excel (4 Suitable Formulas)
- [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)
- How to Calculate Subject Wise Pass or Fail with Formula in Excel
- Make Result Sheet in Excel (with Easy Steps)
- How to Calculate Grades with Weighted Percentages in Excel
- Calculate Percentage of Marks in Excel (5 Simple Ways)
- How to Apply Percentage Formula in Excel for Marksheet (7 Applications)
- Running Average: How to Calculate Using Excel’s Average(…) Function
