How do I calculate a weighted median?

  • Thread starter Thread starter ac
  • Start date Start date
Not so sure, but from a website defining weighted median:

To calculate the weighted median of a set of numbers you need to find
the median and if this number does not exist in the recordset take the
average of the values above and below the median instead.

Weighted Median of 1,2,3,4,5 is 3 (Median is also 3)
Weighted Median of 1,2,3,4,5,6 is 3.5 (Median is also 3.5)
Weighted Median of 1,2,4,4,4,7,7,8,8,8 is 5.2 (((4+4+4) + (7+7))/5)
(Median is 5.5)

If this is so, then the following *array* formula will calculate the
weighted median of the numbers in A2:A11:

=(SUMPRODUCT(A2:A11*(A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11),A2:A11))))
+SUMPRODUCT(A2:A11*(A2:A11=MIN(IF(A2:A11>=MEDIAN(A2:A11),A2:A11)))))/
(SUMPRODUCT(--(A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11),A2:A11))))
+SUMPRODUCT(--(A2:A11=MIN(IF(A2:A11>=MEDIAN(A2:A11),A2:A11)))))

Array formula: commit with Shift+Ctrl+Enter

HTH
Kostis Vezerides
 
David Hager posted this UDF way back

Function WeightedMedian(ValueRange As Range, WeightRange As Range)

Dim MedianArray()

On Error GoTo WrongRanges

ArrayLength = Application.Sum(WeightRange)
ReDim MedianArray(1 To ArrayLength)

Counter = 0
ArrayCounter = 0

For Each ValueRangeCell In ValueRange

LoopCounter = LoopCounter + 1
FirstArrayPos = ArrayCounter + 1
ArrayCounter = ArrayCounter + Application.Index(WeightRange,
LoopCounter)

For n = FirstArrayPos To ArrayCounter

MedianArray(n) = ValueRangeCell.Value

Next

Next

WeightedMedian = Application.Median(MedianArray)
Exit Function

WrongRanges:
WeightedMedian = CVErr(2016)
End Function

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
vezerid said:
=(SUMPRODUCT(A2:A11*(A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11),A2:A11))))
+SUMPRODUCT(A2:A11*(A2:A11=MIN(IF(A2:A11>=MEDIAN(A2:A11),A2:A11)))))/
(SUMPRODUCT(--(A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11),A2:A11))))
+SUMPRODUCT(--(A2:A11=MIN(IF(A2:A11>=MEDIAN(A2:A11),A2:A11)))))
Click to expand...
....

Or

=AVERAGE(IF((A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11),A2:A11)))
+(A2:A11=MIN(IF(A2:A11>=MEDIAN(A2:A11),A2:A11))),A2:A11))
 
Thanks. I should have been more specific. I have a column of weights and a
column of data. I want the weighted median of the data. Is there a way to do
this using the separate range of weights?
 
Impressed as ever...

Regards,
Kostis

...>=(SUMPRODUCT(A2:A11*(A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11),A2:A11))))

...

Or

=AVERAGE(IF((A2:A11=MAX(IF(A2:A11<=MEDIAN(A2:A11),A2:A11)))
+(A2:A11=MIN(IF(A2:A11>=MEDIAN(A2:A11),A2:A11))),A2:A11))
Click to expand...
 
ac said:
Thanks. I should have been more specific. I have a column of weights and a
column of data. I want the weighted median of the data. Is there a way to do
this using the separate range of weights?
Click to expand...
....

More clarification needed. I'm guessing your mean something like the
data being in a single column range named D, weights in an adjacent
single column range named W with each row having the data value and
its corresponding weight. If so, then the weighted mean would involve
sorting the 2-column range on the D column, then calculating the
running sum of the W column and finding the median of the running
sums, and interpolating to find the D value.

For example, given the original D-W table

3 1
2 1
4 2
1 1
4 2
4 2
1 2
6 1
3 2
5 1

Sorting on D gives

1 1
1 2
2 1
3 1
3 2
4 2
4 2
4 2
5 1
6 1

Then adding a 3rd column with the running sum of W gives

1 1 1
1 2 3
2 1 4
3 1 5
3 2 7
4 2 9
4 2 11
4 2 13
5 1 14
6 1 15

The median of the running sum of W is 8, and the interpolated D value
is 3.5. If this is what you mean, then I think the udf Bob Phillips
gave would be the best approach.
 
Back
Top