This submit explains how to calculate Weighted Average in Excel with percentages. In a normal arithmetic common the place the sum of values is split by the variety of values, every information worth is handled equally or carries equal significance or weight. Nevertheless, in a weighted common, sure values carry increased significance than others. So a ‘weight’ is assigned to every worth to signify its relative significance. Knowledge values with a better weight have a better impression on the ultimate common than values with a decrease weight.
The ‘weights’ may be expressed as percentages or quantitative information, equivalent to charges on a scale of 1 to 10. This text focuses on calculating and displaying the weighted common utilizing percentages.
How to calculate Weighted Average in Excel with percentages
In Excel, there are two methods of calculating the weighted common with percentages: utilizing the SUM perform and utilizing the SUMPRODUCT perform. Allow us to take a look at these 2 strategies in element.
1] Calculate the Weighted Average in Excel utilizing SUM Perform
The SUM perform is used to add two values in Excel. The syntax of the SUM perform is:
SUM(number1,[number2],...)
The place,
- number1 is the primary quantity to be added.
- [number2] is the second quantity to be added (and so forth up to [number255]). The numbers may very well be numeric values, cell references, or an array of cells.
Now coming to the purpose, allow us to say we now have a pattern information set from a grading system (as proven in the picture beneath), the place a particular weight has been assigned to the assignments, quizzes, and exams to calculate the ultimate rating of a scholar.
These weights may both add up to 100% or not essentially add up to 100%. Allow us to see how to use the SUM perform to calculate the weighted common in each of those eventualities.
A] Calculating Weighted Average when the weights add up to 100%
The above picture reveals the info set the place the weights add up to 100%. To calculate the weighted common of this information set in Excel, comply with these steps:
- Place your cursor in cell B9 (the place the weighted common wants to be proven).
- Write the next perform in the Method bar on prime:
=SUM(B2*C2, B3*C3, B4*C4, B5*C5, B6*C6, B7*C7, B8*C8) - Press the Enter key.
Within the above perform, we now have used the SUM perform and the multiplication operator to calculate the typical. What we’re doing right here is principally we’re multiplying every information worth by its weight after which including the merchandise to calculate the weighted common. Now because the weights add up to 100%, the essential SUM perform will do the maths. Nevertheless, if the weights DON’T add up to 100%, the calculation will differ barely. Let’s see how.
B] Calculating Weighted Average when the weights don’t add up to 100%
To calculate the weighted common the place the weights don’t add up to 100%, every information worth is first multiplied by its personal weight, after which the sum of those weighted values is divided by the sum of the weights. That is the way you do it in Excel:
- Place your cursor in cell B9.
- Write the next perform in the Method 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 may see, the weighted common comes precisely the identical as in Case A.
Utilizing the SUM perform to calculate the weighted common is useful when you may have simply a few values in the info set. Nevertheless, if the info set consists of a lot of values (and their corresponding weights), will probably be inconvenient to use the SUM perform, since you may have to provide a number of cell references in the system to multiply every worth by its weight. That is the place the SUMPRODUCT perform comes into place. You might use the SUMPRODUCT perform to automate the multiplication by offering the ‘values’ array and the ‘weights’ array as arguments. Allow us to see how.
2] Calculate the Weighted Average in Excel utilizing SUMPRODUCT Perform
The SUMPRODUCT perform returns the sum of the merchandise of the corresponding components of two or extra arrays. The syntax of the SUMPRODUCT is:
=SUMPRODUCT(array1, [array2], [array3], ...)
The place,
- array1 is the primary array of values
- [array2] is the second array of values (and so forth up to [array 255]).
Now for a similar instance of the grading system, we will use the SUMPRODUCT perform to calculate the weighted common as follows:
A] Calculating Weighted Average when the weights add up to 100%
- Place your cursor in cell B9.
- Write the next perform in the Method bar:
=SUMPRODUCT(B2:B8,C2:C8) - Press the Enter key.
Now right here, the SUMPRODUCT perform is multiplying the primary component in the primary array by the primary component in the second array. Then it’s multiplying the second component in the primary array by the second component in the second array. After multiplying all of the corresponding components from the two arrays, the perform is including the merchandise collectively to get the specified common.
B] Calculating Weighted Average when the weights don’t add up to 100%
Once more, in the case of the SUMPRODUCT perform, if the weights don’t add up to 100%, we’d like to divide the resultant worth by the sum of the weights to get the weighted common. Right here’s how to do it in Excel:
- Place your cursor in cell B9.
- Write the next perform in the Method bar:
=SUMPRODUCT(B2:B8,C2:C8)/SUM(C2:C8) - Press the Enter key.
Now as you may see in the picture, the typical comes to 80.85, which is the proper outcome.
That is all about how to calculate weighted common in Excel with percentages. I hope you discover this useful.
Additionally Learn: How to use MIN, Max, and AVERAGE Features of Excel.
How do you calculate a weighted common on 100%?
To calculate the weighted common the place the sum of weights equals 100%, it’s best to multiply every worth by its weight, after which add all of the resultant values. For instance, for the info set a1(w1), a2(w2), a3(w3), the weighted common might be calculated as (a1*w1)+(a2*w2)+(a3*w3). In Excel, you might use the SUMPRODUCT perform to calculate weighted averages.
What’s weightage proportion?
Weightage proportion is the ‘weight’ expressed in proportion that determines the significance (excessive or low) of a price in comparability to different values in a given information set. These weights don’t have any bodily models, however they are often expressed as decimals or integers, other than percentages.
Learn Subsequent: How to calculate Grade Level Average or GPA in Excel.
