Need a UDF to calculate 'weighted median'

Hello everyone,

I am very new to vba and need a UDF to calculate 'weighted median'. You know 'weighted average', but 'weighted median' is different which is 'the 50% weighted percentile'.

Suppose there are two columns: column A for weight (%), and column B for age. To manually get the 'weighted median', I need to do two steps:

1) sort data from the highest to the lowest value based on column B.

2) from the first cell in column A, keep adding up the weights until it reached a total of 50%. The cell in column B which makes up the total weight of 50% is the value 'weighted median'.

Can a UDF to accomplish the above two steps to get the value I want? The UDF will allow me to get the 'weighted median' by specifying: =WTMEDIAN(A1:A20,B1:B20).

An example is attached here.

image001.png

What means "Sum up weights until the total weights become around 50%."

Is that closest to 50% (like 51% rather than 48%).

Hi zbor,

Thanks for your help. As 51% is closer to 50% than 48%, then 51% is preferred.

Here, try this:

Thanks, zbor. The separate steps work.

Is it possible that we can have a nice vba UDF to achieve the task? For example, it will give me the results if I type: =WTMedian(A1:A100,B1:B100).

It will be very interesting to see this important static implemented by a vba UDF.

Hi Alice, got a UDF here for you. Needed to introduce a sorting algorithm in it - but it seems to be working

Please Login or Register  to view this content.

Also see attached. Just hit F9 and see it working. I also did a check by manual calculations to see that it's working. See cell P12 in particular.

Thank you, quekbc. It seemed to be working. You are right, the sorting algorithm seems complicated to understand.

I appreciate if if anyone can really make a very nice UDF.

Hi, MickG, I really feel the macro looks great: to achieve the task with minimal lines of code. Thank you.

Before I make it as solved, can you also consider the case of text values and errors? Obviously we only deal with numeric values, but in some situations there may appear errors or text values.

Can you show an example ??

Hi MickG, please see the new example attached here.

For any row with either error or text value, just exclude them.

The two columns for weight (%) and age are not next to each other in my case. But it does not matter if you want to put them together.

Hi MickG, for any cases with error or text values, just exclude them. How can I add this to the original code?

Many thanks for your help. I'm still learning

Try this:-
NB:- You need to put all 3 columns in the function, in this case "A2 to C12"

Please Login or Register  to view this content.

Hi MickG, I'm so pleased that the macro works nicely. It is really useful to me.

The macro will be useful for other starters to learn as well

You're welcome