The trick while using this formula is to use Control + Shift + Enter, instead of just using Enter. But when you have many items and weights, this method could be cumbersome and error-prone. There is shorter and better way of doing this using the SUM function.Ĭontinuing with the same data set, here is the short formula that will give you the weighted average using the SUM function: =SUM(B2:B8*C2:C8) This method is alright to use when you have a couple of items. Here the formula that will give you the right result: =SUM(B2*C2,B3*C3,B4*C4,B5*C5,B6*C6,B7*C7,B8*C8) To calculate the weighted average using the SUM function, you need to multiply each element, with its assigned importance in percentage. While the SUMPRODUCT function is the best way to calculate the weighted average in Excel, you can also use the SUM function.
AVERAGE OF PERCENTAGES MEAN HOW TO
Also read: How to Calculate Ratios in Excel? Calculating Weighted Average in Excel – SUM Function Here is the formula you can use: =SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)ĭividing the SUMPRODUCT result with the SUM of quantities makes sure that the weights (in this case, quantities) add up to 100%. You can calculate the weighted average price per product by using the SUMPRODUCT function. Suppose you are selling three different types of products as mentioned below: However, there may be cases, where the weights are not directly available, and you need to calculate the weights first and then calculate the weighted average. In the example covered so far, the weights were specified. Also read: How to Calculate Percentage Increase in Excel Example 3 – When the Weights Need to be Calculated The company’s accounting team then calculates the weighted average cost of capital that represents the cost of capital for the entire company. For example, if a company has raised capital using debt, equity, and preferred stock, then these will be serviced at a different cost. One practical example of different weights is when businesses calculate the weighted average cost of capital.
Hence, no matter what, the weights would always add up to 100%. In the above formula, the SUMPRODUCT result is divided by the sum of all the weights. Here is the formula that will get this sorted: =SUMPRODUCT(B2:B8,C2:C8)/SUM(C2:C8) The reason for this is that the weights don’t add up to 100%. In the above result, I have doubled all the weights, and it returns the weighted average value as 153.2. Now we know a student can’t get more than 100 out of 100, no matter how brilliant he/she is. If I use the same SUMPRODUCT formula, it will give me the wrong result. In the above case, the weights add up to 200%. Let’s have a look at the same example with different weights. But in real life scenarios, it may not always be the case. In the above case, the weights were assigned in such a way that the total added up to 100%. Here is an illustration to make it clear.Īlso read: How to Calculate Percentile in Excel Example 2 – When Weights Don’t Add Up to 100% Then it multiplies the second element of the first array with the second element of the second array. Here is how this formula works : Excel SUMPRODUCT function multiplies the first element of the first array with the first element of the second array. Here is the formula that will give you the weighted average in Excel: =SUMPRODUCT(B2:B8,C2:C8) In such a case, you can use the SUMPRODUCT function to get the weighted average of the score. A simple average can not be calculated here as the importance of different evaluations vary.įor example, a quiz, with a weight of 10% carries twice the weight as compared with an assignment, but one-fourth the weight as compared with the Exam.
In the above data, a student gets marks in different evaluations, but in the end, needs to be given a final score or grade. Suppose you have a dataset with marks scored by a student in different exams along with the weights in percentages (as shown below): Below are three different situations where you can use the SUMPRODUCT function to calculate weighted average in Excelīelow are three different situations where you can use the SUMPRODUCT function to calculate weighted average in Excel Example 1 – When the Weights Add Up to 100% There could be various scenarios where you need to calculate the weighted average. Calculating Weighted Average in Excel – SUMPRODUCT Function In this tutorial, you’ll learn how to calculate the weighted average in Excel:
0 kommentar(er)
