6 Quick Ways to Calculate Average Percentage of Marks in Excel

Once I got the opportunity to check out my teacher Mr. Machiuer, calculated the average percentage of the Term Final Exam’s results one by one single-handedly. The task was tedious also the teacher was very furious as he had no way to ferret it out easily. So, I made a decision to rescue him. My determination was to learn several approaches to calculate the average percentage of marks in Excel. In the meantime, I was very lucky that I could help him and everyone appreciated me. In this article, I will show you the methods how I was able to calculate the average percentage of marks in Excel.

Putting some random marks of students, you will be able to calculate the average percentage as the calculator with VBA Macro. We will discuss the General calculation, the use of the SUM function, and the AVERAGE function as well as the Weighted Average and Pivot Table.

📁 Download Excel File

To practice please download the Excel file from the link below:

Learn to Calculate Average Percentage of Marks in Excel with These 6 Approaches

In this article, we are going to illustrate 6 catchy approaches to calculate the average percentage of Marks in Excel. Following that, we consider a dataset named Term Final Result containing Student Name, Physics, Math, and Chemistry. The dataset also has 5 columns as well as 11 rows. To construct the approaches, we will learn to use SUM and AVERAGE functions as well as VBA Macro while measuring average in percentage. We will further explain the calculation of the average from a Pivot Table along with the Weighted Average based on the importance of the subjects. So, let’s get started.

Summing up the cells containing marks of students and dividing by the number of subjects, you will be able to calculate the average in percentage within a minute. When we count the marks out of 100 then, automatically it is in the percentage. There is no necessity to convert into decimals divided by 100 and set the cells’ format as Percentage. Please follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select a cell i.e. F6 to calculate the average marks of Physics, Math, and Chemistry under the Average heading.
• Secondly, type the following formula in the F6 cell selecting the required cells.

• Therefore, obtain the average of 83.00 in the F6 cell under the Average heading. Here, 83.00 is the percentage itself as it is calculated out of 100.
• Finally, Use the Fill Handle tool to fill up the cells with formulas containing relative references.

📕 Read More: How to Calculate Average Percentage Change in Excel

In the previous method, we need to select cells by clicking cells. On the contrary, using the SUM function, you will be able to calculate the sum just by selecting the range. The general syntax of the SUM function is as follows,

Please follow the necessary steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Primarily, select a cell i.e. F6 to calculate the average marks under the Average heading.
• Secondly, write the following formula in the F6 cell containing the SUM function.

• Here in the formula, we calculated the sum of three subjects and then divided it by the number of subjects. To express in decimal, further, divide by 100.
• Therefore, obtain the average of 0.83 in the F6 cell under the Average heading. Use the Fill Handle tool to autofill the cells of the Average column.

• After that, selecting the range, go to the Home tab.
• Further, set the numbers as Percentage from the Number feature. Thus, the result shows 83.00% in the F6 cell and the rest accordingly.

📕 Read More: 7 Quick Ways to Calculate Average Percentage Increase in Excel

Using the SUM function we need to divide the sum by the number of subjects. But In MS Excel, we have the opportunity to calculate the average directly using the AVERAGE function. Please check the syntax of the AVERAGE function below.

Please check the necessary steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select a cell i.e. F6 to calculate the average marks under the Average heading.
• Secondly, insert the formula in the F6 cell containing the AVERAGE function.

• Finally, get the average of 83.00 in the F6 cell under the Average heading. Use the Fill Handle tool to autofill the remaining cells of the Average column.

📕 Read More: 2 Easy Ways to Compute Average Excluding 0 in Excel

Unlike the previous 3 methods, there is another method named the Weighted Average method. Based on the importance of subjects, we will be able to compute the average marks in percentage. Suppose weight varies by 30%, 40%, and 30% throughout a subject. Therefore the calculation is as follows,

Please check out the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, navigate a cell i.e. F6 to calculate the average marks under the Average heading.
• Then input the formula in the F6 cell considering the weight from C13, D13, and E13 cells.

• Next, Locking the C13, D13, and E13 cells with Dollar ($) sign, we disable the relative reference cell while auto-filling the formula.
• Lastly, achieve the result of 83.10% in the F6 cell. Similarly, auto-filling the others, get the final output.

📕 Read More: 7 Easy Ways to Calculate Average Rating in Excel

Converting the dataset into a Pivot Table, there is another way to calculate the average with percentages in Excel. So, first, we will learn to make a pivot table and then we will learn the approach to measure the average from the marks located in multiple cells. Please check out the required steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the entire dataset.
• Secondly, go to the Insert tab from the Top Ribbon and click on the PivotTable feature.

• Thirdly, a box named Create PivotTable appears. If you observe carefully, there will be the range pivot!$B$5:$E$11 of the Pivot worksheet in the Table/Range field.
• Fourthly, select New Worksheet to get the pivot table in the new worksheet and hit on the OK button.

• Fifthly, obtain the Pivot Table by clicking the column heading Student Name, Physics, Math, and Chemistry from the PivotTable Fields in the Left Side Ribbon.

• Subsequently, go to the Analyze tab and expand the Field, Items &Sets from the Calculation drop-down list.
• Then, select the Calculated Field from the list.

• Therefore, another pop-up box named Insert Calculated Field appears.
• Next, insert the following formula by selecting from the Fields section.
• After that, hit the OK button.

• In the end, the Average of every student appears in the Sum of Average column.

📕 Read More: 6 Ways to Solve When Average Formula is Not Working in Excel

With the help of the VBA Macro code, you will be able to calculate the average easily just by inputting the marks of subjects in the pop-up box. Predominantly, you will be able to use the excel file as a calculator. Please follow the necessary steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Check first if your Developer option is available or not.
• Secondly, to enable the Developer option, Right-Click on the mouse at the Top Ribbon and select Customize the Ribbon.

• Thirdly, click on the Developer option and hit the OK button.

• Now Developer mode is on.
• Then, click on the Visual Basic feature.
• Next, open the dedicated worksheet VBA or the Module from the Insert menu.

• After that, insert the following VBA code in the Module and hit the OK button.

Code

Sub Calculate_Average_Percentage_of_Marks()
Dim p As Integer
Dim q As Integer
Dim r As Integer
Dim average As Double
p = InputBox("Marks of Physics")
q = InputBox("Marks of Math")
r = InputBox("Marks of Chemistry")
average = (p + q + r) / 3
MsgBox "Average Percentage of Marks " & average & "%"
End Sub

• Thus, a pop-up box appears.
• Subsequently, insert the marks of Physics and click on the OK button.

• Another pop-up box appears and inserts the marks of Math. Then hit the OK button.

• Further, a pop-up box appears saying to input the marks of Chemistry. Inserting the marks of Chemistry, hit the Ok button.

• Therefore, finally, the output box appears saying the Average percentage of Marks is 83%.

📄  Important Notes

🖊️  Enable the Developer menu first to execute VBA code.

🖊️  The total weighted sum should be 100% while using the weighted average of marks.

🖊️  Prominently the average of the marks of students is the average of percentages. Otherwise, divided the calculation by 100 and select the cell format as Percentage.

📝 Takeaways from This Article

📌  SUM function to calculate the average of marks in percentage.

📌  AVERAGE function to measure the average of total marks in Excel

📌  Calculate the average from an Excel Pivot Table.

📌  Weighted Average calculation based on the importance of subjects.

📌  VBA Macro to use as an average counting calculator.

Conclusion

In this article, we expound on 6 handy approaches to calculating the average Percentage of marks in Excel. I hope you enjoyed your learning and will be able to calculate the average marks of students. Any suggestions, as well as queries, are appreciated. Don’t hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.

Related Articles

• 3 Approaches to Calculate 5 Star Rating Average in Excel
• 7 Ways to Calculate Average Attendance with Formula in Excel
• 3 Approaches to Calculate Average of Text in Excel
• 5 Ways to Calculate 7-Day Moving Average in Excel