This post explains how to calculate Weighted Average in Excel with percentages. In a standard arithmetic average where the sum of values is divided by the number of values, each data value is treated equally or carries equal importance or weight. However, in a weighted average, certain values carry higher importance than others. So a ‘weight’ is assigned to each value to signify its relative importance. Data values with a higher weight have a higher impact on the final average than values with a lower weight.
The ‘weights’ can be expressed as percentages or quantitative data, such as rates on a scale of 1 to 10. This article focuses on calculating and displaying the weighted average using percentages.
How to calculate Weighted Average in Excel with percentages
In Excel, there are two ways of calculating the weighted average with percentages: using the SUM function and using the SUMPRODUCT function. Let us have a look at these 2 methods in detail.
1] Calculate the Weighted Average in Excel using SUM Function
The SUM function is used to add two values in Excel. The syntax of the SUM function is:
SUM(number1,[number2],...)
Where,
- number1 is the first number to be added.
- [number2] is the second number to be added (and so on up to [number255]). The numbers could be numeric values, cell references, or an array of cells.
Now coming to the point, let us say we have a sample data set from a grading system (as shown in the image below), where a specific weight has been assigned to the assignments, quizzes, and exams to calculate the final score of a student.
These weights could either add up to 100% or not necessarily add up to 100%. Let us see how to use the SUM function to calculate the weighted average in both of these scenarios.
A] Calculating Weighted Average when the weights add up to 100%
The above image shows the data set where the weights add up to 100%. To calculate the weighted average of this data set in Excel, follow these steps:
- Place your cursor in cell B9 (where the weighted average needs to be shown).
- Write the following function in the Formula bar on top:
=SUM(B2*C2, B3*C3, B4*C4, B5*C5, B6*C6, B7*C7, B8*C8) - Press the Enter key.
In the above function, we have used the SUM function and the multiplication operator to calculate the average. What we are doing here is basically we are multiplying each data value by its weight and then adding the products to calculate the weighted average. Now since the weights add up to 100%, the basic SUM function will do the maths. However, if the weights DON’T add up to 100%, the calculation will differ slightly. Let’s see how.
B] Calculating Weighted Average when the weights don’t add up to 100%
To calculate the weighted average where the weights don’t add up to 100%, each data value is first multiplied by its own weight, and then the sum of these weighted values is divided by the sum of the weights. This is how you do it in Excel:
- Place your cursor in cell B9.
- Write the following function in the Formula bar:
=SUM(B2*C2, B3*C3, B4*C4, B5*C5, B6*C6, B7*C7, B8*C8)/SUM (C2:C8) - Press the Enter key.
Now as you can see, the weighted average comes exactly the same as in Case A.
Using the SUM function to calculate the weighted average is helpful when you have just a couple of values in the data set. However, if the data set consists of a large number of values (and their corresponding weights), it will be inconvenient to use the SUM function, since you have to supply multiple cell references in the formula to multiply each value by its weight. This is where the SUMPRODUCT function comes into place. You may use the SUMPRODUCT function to automate the multiplication by providing the ‘values’ array and the ‘weights’ array as arguments. Let us see how.
2] Calculate the Weighted Average in Excel using SUMPRODUCT Function
The SUMPRODUCT function returns the sum of the products of the corresponding elements of 2 or more arrays. The syntax of the SUMPRODUCT is:
=SUMPRODUCT(array1, [array2], [array3], ...)
Where,
- array1 is the first array of values
- [array2] is the second array of values (and so on up to [array 255]).
Now for the same example of the grading system, we can use the SUMPRODUCT function to calculate the weighted average as follows:
A] Calculating Weighted Average when the weights add up to 100%
- Place your cursor in cell B9.
- Write the following function in the Formula bar:
=SUMPRODUCT(B2:B8,C2:C8) - Press the Enter key.
Now here, the SUMPRODUCT function is multiplying the first element in the first array by the first element in the second array. Then it is multiplying the second element in the first array by the second element in the second array. After multiplying all the corresponding elements from the 2 arrays, the function is adding the products together to get the desired average.
B] Calculating Weighted Average when the weights don’t add up to 100%
Again, in the case of the SUMPRODUCT function, if the weights don’t add up to 100%, we need to divide the resultant value by the sum of the weights to get the weighted average. Here’s how to do it in Excel:
- Place your cursor in cell B9.
- Write the following function in the Formula bar:
=SUMPRODUCT(B2:B8,C2:C8)/SUM(C2:C8) - Press the Enter key.
Now as you can see in the image, the average comes to 80.85, which is the correct result.
This is all about how to calculate weighted average in Excel with percentages. I hope you find this helpful.
Also Read: How to use MIN, Max, and AVERAGE Functions of Excel.
How do you calculate a weighted average on 100%?
To calculate the weighted average where the sum of weights equals 100%, you should multiply each value by its weight, and then add all the resultant values. For example, for the data set a1(w1), a2(w2), a3(w3), the weighted average will be calculated as (a1*w1)+(a2*w2)+(a3*w3). In Excel, you may use the SUMPRODUCT function to calculate weighted averages.
What is weightage percentage?
Weightage percentage is the ‘weight’ expressed in percentage that determines the importance (high or low) of a value in comparison to other values in a given data set. These weights do not have any physical units, but they can be expressed as decimals or integers, apart from percentages.
Read Next: How to calculate Grade Point Average or GPA in Excel.
