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!
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