How do I calculate a weighted median?

Anybody know how to do it in Excel?

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(A 2: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


On Jun 24, 7:38 pm, ac wrote:
Anybody know how to do it in Excel?

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)

"ac" wrote in message
...
Anybody know how to do it in Excel?

vezerid wrote...
....
=(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)))))
....

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))),A 2: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?

"Harlan Grove" wrote:

vezerid wrote...
....
=(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)))))
....

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))),A 2:A11))

Impressed as ever...

Regards,
Kostis

On Jun 24, 8:27 pm, Harlan Grove wrote:
vezerid wrote...

...=(SUMPRODUCT(A2:A11*(A2:A11=MAX(IF(A2:A11=MED IAN(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)))))

...

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))),A 2:A11))

ac wrote...
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?
....

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.