Results 1 to 2 of 2

Thread: Calculating a Weighted Median

  1. #1

    Calculating a Weighted Median



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •