How do I calculate a weighted median?
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?
|