How to do a weighted median?

**bnelson333** · 11-02-2006, 10:55 AM

Is there any way to do a weighted median in Excel?

Consider this example:

Project.....Duration.....Units
---------------------------
a.............10.............3.....
b.............20.............2.....
c.............30.............5.....

I'm trying to get a median of duration, not based on project, but weighted by units.

If I do a =MEDIAN(duration column), that will give me median by project. To get the more accurate number, I need to explode out the above chart to look like this:

Project.....Duration
------------------
a.............10.......
a.............10.......
a.............10.......
b.............20.......
b.............20.......
c.............30.......
c.............30.......
c.............30.......
c.............30.......
c.............30.......

Now if I do =MEDIAN(duration column) it will be weighted by units.

The big question is though, is there a way in Excel to do the calculation on the first table, without exploding it into the second table?

Thanks!

**Carim** · 11-02-2006, 11:05 AM

Hi,

With an array formula Control Shift Enter instrad of Enter

Please Login or Register  to view this content.

HTH
Carim

**bnelson333** · 11-02-2006, 11:16 AM

Thanks for the reply Carim. Unfortunately that doesn't seem to work.

In the tables I gave, median of duration on table 1 = 20. Median of duration on table 2 = 30 (this is the # I'm looking for), but the value from your formula is 220.

Even if I replace SUM with MEDIAN in your formula, it gives 40, not 30.

You're thinking how I am, but I just haven't been able to visualize the calculation needed to figure this out.

**Carim** · 11-02-2006, 11:59 AM

Sorry ...
I though you meant weighted average ...

Median : =Median(B1:B3)

HTH
Carim

**bnelson333** · 11-02-2006, 03:19 PM

That only gives me median of duration. I need median of duration, weighted by units.

**Maistrye** · 11-02-2006, 03:30 PM

Originally Posted by bnelson333

Thanks for the reply Carim. Unfortunately that doesn't seem to work.

In the tables I gave, median of duration on table 1 = 20. Median of duration on table 2 = 30 (this is the # I'm looking for), but the value from your formula is 220.

Even if I replace SUM with MEDIAN in your formula, it gives 40, not 30.

You're thinking how I am, but I just haven't been able to visualize the calculation needed to figure this out.

I don't know of any way to do it with excel functions without a helper column (at the least).

The only suggestion I can offer is that you will probably have to write your own function in VBA to do this. If you stick it with the worksheet, it will be portable provided whomever you give it to doesn't disable the macros.

Scott

**Carim** · 11-02-2006, 03:36 PM

For the sake of clarification, is the median you are looking for 25 ?

**Carim** · 11-03-2006, 04:25 AM

Hi,

Attached is a sample for weighted median.
Let me know if it is what you expected ...

Carim

**LARizz** · 07-12-2011, 11:48 AM

Carim,

I tried this and it doesn't work if you plug in any other numbers.

**jhinkel** · 01-31-2016, 08:28 PM

Is it really the case that Excel can't calculate a weighted median? I need to calculate the median wage for various job positions in an industry. For each job position, wages are in one column, and the number of people at each facility who are paid that wage are counted in another column. For example,

Col.A.......Col.B
4.........$11.00
3.........$12.00
8.........$10.00
2.........$12.00
1.........$17.00
5.......... $9.25

I have about 700 entries, so there is no way I am going to create helper columns for this, and I shouldn't have to. A weighted median seems like a very basic and commonly needed calculation that Excel should easily be able to do. Right?

**FDibbins** · 01-31-2016, 08:33 PM

jhinkel, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**jewelsharma** · 01-31-2016, 11:30 PM

Hi FDibbins,
Not sure if I did something wrong, but I was replying to OP's post.

**FDibbins** · 01-31-2016, 11:35 PM

jewelsharma I had already posed that the member needs to start a new thread, and not post questions on another member's thread, wait til they post their own thread, then respond on that 1

**jhinkel** · 02-01-2016, 01:59 PM

Hi, Thanks for the direction, and my apologies for posting before becoming more familiar with the rules.

I reposted my question in a new thread. My question about the weighted median in Excel is basically the same issue as the one being discussed here. I'm not sure this thread was ever resolved, so people interested in this post may also find the weighted median question in my post interesting: http://www.excelforum.com/excel-form...ed-median.html