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