# Thread: Calculating a Weighted Median

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  Reply With Quote

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```  Reply With Quote

#### Posting Permissions

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