Excel formula: weighted median

0
votes

Team -

I have weights in cells B2:B70 and numbers in cells C2:C70. I am attempting to find a weighted median to populate cell C71. A weighted mean is being calculated using =SUMPRODUCT($B2:$B70,C2:C70)/SUM($B2:$B70) - but I cannot think of an elegant formula for a weighted median. Maybe I am being terribly slow.

Thanks in advance.

1
excel-formula
It will require vba, or at least two helper columns to create the array needed. - Scott Craner
Happy to use the helper column solution - Mohammed

1 Answers

0
votes

I think this will do what you want.

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