You need to convert it to an array formula by pressing the CTRL+Shift+Enter key after entering the formula.ĭoing this will insert curly braces around the formula, showing that it is now an array formula. Notice that entering the above formula and pressing the return key will give you an error. To do this, you can use a range of cells inside the SUM function instead of entering individual cell references, as follows: =SUM(B2:B6*C2:C6)/SUM(C2:C6) However, it’s not very practical when you have a large number of values to average.Ī better way would be to use an array formula inside your SUM function. The formula is very simple and easy to understand. Note that this formula essentially performs the same calculation that we had described in the previous section. The SUM function in Excel simply adds up the values provided as its parameters. In other words, we can use the following formula (for our sample data): =SUM(B2*C2,B3*C3,B4*C4,B5*C5,B6*C6)/SUM(C2:C6) This formula takes the product of individual values with corresponding weights, sums them up and divides this result by the sum of weights. The first method uses the same general formula that we used in the previous section to find the weighted average. Using the General Formula to Calculate Weighted Average in Excel To demonstrate the methods, we will use the following dataset: Now let us see two ways to calculate the weighted average in Excel. We saw from the above example that the formula to calculate the weighted average of a set ( x 1, x 2, …, x n) with weights ( w 1, w 2, …., w n) is as follows: Weighted average=( x 1 w 1+ x 2 w 2+…+ x n w n)/( w 1+ w 2+. Two Ways to Calcuate Weighted Average In Excel However, the weighted average paints a much better picture of the student’s performance because it also considers the importance of each test score. In our example, the weighted average score of the student will be (40 * 0.1) + (80 * 0.3) + (80 * 0.6) = 76Īs you can see the standard average and weighted average are two different values. In other words, if x 1, x 2, …, x n are individual values of a variable and w 1, w 2, …., w n are their corresponding weights, then the weighted average of this variable can be calculated as follows: Weighted average = ( x 1 w 1+ x 2 w 2+…+ x n w n)/( w 1+ w 2+. The weighted average is calculated by multiplying each quantity (each test score in this case) with its respective weight, adding up these products, and dividing the sum by the sum of weights. If we calculate the weighted average, however, we could take into account the weights of individual tests. However, this result is not an accurate representation of the student’s performance, because the student did better in the mid-term exam, which was more important than the unit test.īut his/her bad performance in the unit test brought his score down lower to what it should be. In other words, the standard average would be (40+80+80) / 3 = 66.7. In the above example, a standard average would simply sum up the individual test scores and divide it by the number of tests.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |