How to Calculate Grades with Weighted Percentages in Excel

Get FREE Advanced Excel Exercises with Solutions!

Sometimes professors give more weight to a certain exam or quiz which means those exams contribute some value than other exams. In that case, the weighted percentage will come into account. Using these weighted percentages, the professors give the final result of a student. This article will show how to calculate the grades with weighted percentages in Excel. I think you find this really interesting and effective in the long run.


Download Practice Workbook

Download the practice workbook below.


Overview of Weighted Average

The weighted average is defined as the average where a weight is assigned to every quantity to show its importance. Using this weighted average, professors can calculate the final grade of a student with different weighted percentages. The Weighted average is more accurate than the normal average because it takes importance into account. The weighted average is the summation of the product of weights and quantities divided by the summation of the weights.

Let’s consider some exams and their corresponding marks. Now, to get the final grade, you can use the AVERAGE function.

But the basic problem of this process is that it takes all the exams in a similar weight. In real life, it can’t be possible because the final exam’s weight is higher than the quiz and assignment. In that case, the weighted average is much more accurate than the normal average. Let’s assume, the assignment counts twice as much as a quiz and the final exam takes thrice as much as a quiz.

To calculate the weighted average, you need to apply the following formula.

In Excel, you can use the SUMPRODUCT function to calculate the product of weight and marks. Then use divide it by the summation of the weight by using the SUM function.

How to Calculate Grades with Weighted Percentages in Excel

So, the weighted average takes the importance of each quantity and gives the final result which is far more accurate than the normal average. That is why the professors and teachers utilize the weighted average to calculate the final grade.


3 Easy Methods to Calculate Grades with Weighted Percentages in Excel

To calculate grades with the weighted percentages in Excel, we have found three different methods through which you can have a clear idea. In this article, we utilize the SUMPRODUCT function, SUM function, and the combination of SUMPRODUCT and SUM functions. All of these methods are fairly easy to understand. All of these methods give us the final grades with weighted percentages in Excel. We take a dataset that includes some exams and their corresponding weight.


1. Using SUMPRODUCT Function

Our first method is based on using the SUMPRODUCT function. When teachers take several exams or assignments, it is a must to have some weight in each exam or assignment. Because each exam is different from others in terms of importance. The final exam carries the highest importance so, the weight of the final exam is higher than any other exams or assignments. To use the SUMPRODUCT function, follow the steps carefully.

Steps

  • First, merge the cells C14 and D14.
  • Then, select it and write down the following formula in the formula box.
=SUMPRODUCT(C5:C13,D5:D13)

How to Calculate Grades with Weighted Percentages in Excel

🔎 Breakdown of the Formula

SUMPRODUCT(C5:C13,D5:D13): The SUMPRODUCT function multiplies the first element (C5) of the first array with the first element (D5) of the 2nd array. Then, it multiplies the 2nd element (C6) of the first array with the 2nd element (D6) of the 2nd array. Follow the same procedures until the array ends. Finally, add all the products one after another. As the overall weight percentage is 100%, So, there is no need to divide it by the summation of the weight percentage.

  • Then, press Enter to apply the formula.
  • It will give the final grade of a certain student using the weighted percentage.

How to Calculate Grades with Weighted Percentages in Excel

Read More: How to Make a Grade Calculator in Excel (2 Suitable Ways)


Similar Readings


2. Applying SUM Function

Our next method is based on the SUM function. This method is more of a conventional method. Here, you need to multiply its element and weight percentages in the SUM function. The main task of the SUM function is to add them. Follow the steps.

Steps

  • First, merge the cells C14 and D14.
  • Then, select it and write down the following formula in the formula box.
=SUM(C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11,C12*D12,C13*D13)

How to Calculate Grades with Weighted Percentages in Excel

  • Then, press Enter to apply the formula.
  • It will give the final grade of a certain student using the weighted percentage.

How to Calculate Grades with Weighted Percentages in Excel

Read More: How to Calculate Percentage of Marks in Excel (5 Simple Ways)


3. Combining SUMPRODUCT and SUM Functions

Our third method is based on the combination of the SUMPRODUCT and SUM functions. This method can apply to every case to calculate the grades with weighted percentages in Excel. Sometimes, the total weighted percentage is higher or lower than 100%. In that case, we can utilize the combination of SUMPRODUCT and SUM functions. It will provide us with the perfect solution.

Steps

  • First, merge the cells C14 and D14.
  • Then, select it and write down the following formula in the formula box.
=SUMPRODUCT(C5:C13,D5:D13)/SUM(D5:D13)

How to Calculate Grades with Weighted Percentages in Excel

🔎 Breakdown of the Formula

SUMPRODUCT(C5:C13,D5:D13)/SUM(D5:D13): The SUMPRODUCT function multiplies the first element (C5) of the first array with the first element (D5) of the 2nd array. Then, it multiplies the 2nd element (C6) of the first array with the 2nd element (D6) of the 2nd array. Follow the same procedures until the array ends. Finally, add all the products one after another. After that, using the SUM function, calculate the summation of the range of cells D5 to D13 which returns the total weighted percentage. Finally, it returns the final grade by using the value from SUMPRODUCT and SUM functions.

  • Then, press Enter to apply the formula.
  • It will give the final grade of a certain student using the weighted percentage.

How to Calculate Grades with Weighted Percentages in Excel

Read More: How to Calculate Grade Percentage in Excel (3 Easy Ways)


Things to Remember

  • When the weighted percentage is equal to 100%, you can use the both SUMPRODUCT function and the combination of the SUM and SUMPRODUCT functions.
  • But when the weighted percentage is less than or greater than 100%, you must use the combination of the SUM and SUMPRODUCT functions to get the final grades of a student.

Conclusion

To calculate grades with weighted percentages in Excel, we have shown three effective methods through which you can get the final grade of a certain student. The weighted average takes the importance of each exam and finally gives the final results. To calculate this, we utilize three simple Excel functions. We covered all possible areas regarding this topic. If you have further questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo