Weighted Mean and Median

Weighted Mean

Definition 1: For any set of weights W = {w₁, w₂, …, w_n}  where each w_i ≥ 0 and w_i > 0 for at least one i. the weighted mean (also called the weighted average) of the data set S = {x₁, x₂, …, x_n} is defined by

where w = the sum of the w_i. When w_i =1 for all i, the weighted mean is the same as the mean. In fact, this is also true when all the weights are the same.

Worksheet Functions

Excel Function: The weighted mean is calculated in Excel using the worksheet formula

=SUMPRODUCT(R1,R2)/SUM(R2)

where R1 contains the elements in S and R2 contains the elements in W. 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. When the weights do add up to one, the formula for the weighted mean is simply the sum, namely =SUM(R1) in Excel.

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. If R2 is omitted then the ordinary mean is returned (as described in Measures of Central Tendency).

Example

Example 1: Find the weighted mean for the data in column E of Figure 1 based on the weights in column F.

Figure 1 – Weighted mean

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. 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).

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 obtain the same result using any of the following formulas:

=SUMPRODUCT(E14:E18,F14:F18)/SUM(F14:F18)

=SUMPRODUCT(A14:A18,B14:B18)           

=MEAN(E14:E18,F14:F18)

=MEAN(A14:A18,B14:B18)

Weighted Median

Definition 2: Assuming that the elements in S are in ascending sorted order, the weighted median is defined to be x_j where

If, however

for some j, then the weighted median is instead defined to be (x_j + x_j+1)/2. If all the weights are equal, the weighted median is simply the ordinary median.

Worksheet Function

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 R2 is omitted then the ordinary median is returned, i.e. MEDIAN(R1) (as described in Measures of Central Tendency).

Example

Example 2: Find the weighted median for the data in column A of Figure 2 based on the weights in column B.

Figure 2 – Weighted median

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. 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. Since cell C7 is the first cell in column C whose value is larger than .50, the corresponding value in cell A, namely 4 in cell A7, is the weighted median. We obtain the same result using the Real Statistics formula =MED(A4:A8,B4:B8).

Weights that don’t sum to one

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. For the second example (in columns E, F, G), the sum of the weights is 50 as shown in cell F9, and so half of this value is 25. The first entry in column G that is larger than 25 occurs in cell G7 (32.5 > 25), and so the corresponding data value, namely 4 (in cell E7) is the weighted median.

When the cumulative sum of the weights reaches a value that is exactly half of the sum of the weights, then we essentially have two medians and so we take the average of these two values. This is shown in the third and fourth examples. For the third example, this occurs in cell K5 (with value .50), while for the fourth example, this occurs in cell O6 (with value 12.5 which is exactly half of the sum of the weights shown in cell N9). Thus, the median is the average of the data value in this row and the following row, i.e. the average of 3 and 4, namely 3.5.

Weighted Percentile

Weighted percentiles whose percentile value is p (0 ≤ p ≤ 1) are computed in a similar manner to the weighted median. In fact, the weighted median is a special case of the weighted percentile with p = .5.

For example, for the data in columns I and J of Figure 2, the weighted percentile for any p < .40 is 1 (the minimum). The weighted percentile for p = .40 is 2, i.e. the mean of 1 and 3. The weighted percentile for p = .45 is 3 and the weighted percentile for p = 1.00 is 7 (the maximum).

Worksheet Function

The weighted median function, MED(R1, R2, p), actually takes a third argument p (default .5) which returns the weighted percentile for p. When p is omitted the weighted median is returned. 

E.g. Referring to Figure 2, the formula =MED(I4:I8,J4:J8,.45) returns the value 3.

Examples Workbook

Click here to download the Excel workbook with the examples described on this webpage.

Reference

Wikipedia (2020) Weighted median
https://en.wikipedia.org/wiki/Weighted_median