Calculating a Weighted Median

kaybee · Feb 22, 2012

Hi All,

I'm new to VBA programming and need some help determining the weighted median (NOT weighted average...I know there is an existing function for this) for a data set.

Here's my problem - I have two columns and Column A is filled with values (1, 4, 6, 12) while Column B shows the weight of those values (2, 3, 1, 4) respectively. If I want to calculate the median accurately, I need to account for the weight, meaning I can't use the median function for solely the values in Column A. What I need to do is calculate the median from the full unweighted list of these values which would look like the following (using the sample values from above): 1, 1, 4, 4, 4, 6, 12, 12, 12, 12.

How can I write a VBA function that will take the values of Column A and copy them over to a new column using Column B as a reference for how many times it should be copied? Basically, how do I create the full unweighted list as shown above? From there, I could use a simple Median function to find the median of that column.

I've included a sample data file. I want to use Column A and Column B to get Column C.

Would appreciate any tips or sample code I could copy- much appreciated!

-Katherine

Bob Phillips · Feb 22, 2012

Code:

Public Function WeightedMedian()
Dim rngValues As Range
Dim rngWeights As Range
Dim rng As Range
Dim cnt As Long
Dim weight As Long
Dim idx As Long

    Set rngValues = Range(Range("A2"), Range("A2").End(xlDown))
    Set rngWeights = Range(Range("B2"), Range("B2").End(xlDown))
    idx = 2
    For Each rng In rngValues

        cnt = cnt + 1
        weight = rngWeights.Cells(cnt, 1)
        Cells(idx, "C").Resize(weight).Value = rng.Value
        idx = idx + weight
    Next
End Function

Calculating a Weighted Median

kaybee

New member

Attachments

Bob Phillips

Super Moderator