When the weights don’t sum to 1, as shown in the second example in the figure, the approach is the same except that we need to find the first entry in the cumulative weights column that is larger than 50% of the sum of the weights. We obtain the same result using the Real Statistics formula =MED(A4:A8,B4:B8). 50, the corresponding value in cell A, namely 4 in cell A7, is the weighted median. Since cell C7 is the first cell in column C whose value is larger than. This is done by inserting the formula =B4 in cell C4 and the formula =C4+B5 in cell C5 and then highlighting range C5:C8 and pressing Ctrl-D. To obtain the result, we first sort the data in ascending order and then create a cumulative sum of the weights as shown in column C. ExampleĮxample 2: Find the weighted median for the data in column A of Figure 2 based on the weights in column B. If R2 is omitted then the ordinary median is returned, i.e. Real Statistics Function: The weighted median can also be calculated using the function MED(R1, R2) where R1 contains the elements in S and R2 contains the elements in W. If all the weights are equal, the weighted median is simply the ordinary median. =SUMPRODUCT(E14:E18,F14:F18)/SUM(F14:F18)ĭefinition 2: Assuming that the elements in S are in ascending sorted order, the weighted median is defined to be x j whereįor some j, then the weighted median is instead defined to be ( x j + x j +1)/2. We obtain the same result using any of the following formulas: This approach is equivalent to dividing each of the weights by 50 to obtain weights that do sum to one (as shown in column B) and then summing the product of the weights and data values (shown in column C) to obtain the value 5.2 shown in cell C19. We next divide 260 by the sum of the weights (namely 50 as shown in cell F19) to obtain the weighted mean of 5.2 (as shown in cell G19). We calculate the weighted mean by multiplying each data element by its corresponding weight (as shown in column G) and then summing these values up to obtain the value of 260. ExampleĮxample 1: Find the weighted mean for the data in column E of Figure 1 based on the weights in column F. If R2 is omitted then the ordinary mean is returned. Real Statistics Function: The weighted mean can also be calculated using the function MEAN(R1, R2) where R1 contains the elements in S and R2 contains the elements in W. When the weights do add up to one, the formula for the weighted mean is simply the sum, namely =SUM(R1) in Excel. We typically think of the weights as having to add up to one although this is not necessary since dividing by the sum of the weights is equivalent to having the weights add up to one. Where R1 contains the elements in S and R2 contains the elements in W. Worksheet FunctionsĮxcel Function: The weighted mean is calculated in Excel using the worksheet formula In fact, this is also true when all the weights are the same. When w i =1 for all i, the weighted mean is the same as the mean. Definition 1: For any set of weights W = is defined by
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |