weighted median

[sarahrosenberg]

I am trying to find a "weighted median." I have three columns. The first column shows a product SKU, the second column shows a shipment count for that SKU, and the third column shows a price for that SKU. I want the median price for shipments. If I use the Median formula on the price column, it will not take into consideration the number of shipments.

 

[nbrcrunch]

Try this: (Assuming that the 2nd column is B and the 3rd column is C)

Use Ctrl-Alt-Enter to make this an array formula:

=AVERAGE((B2:B6)/(A2:A6))

Obviously, replace the ranges with your real data ranges.

 

[sarahrosenberg]

Will that give me a weighted average or a weighted median?

 

[nbrcrunch]

Sorry, forgot to fix from copying in my xls


=AVERAGE((C1:C10)/(B1:B10))
Ctrl-Alt-Enter

To answer your question, you can do a manual of the same thing by dividing each Cost by shipments in a new column & then averaging the total of that column. It results in the same number as the single formula above.

 

[Weaver]

For a weighted median, I'd do a running total of the shipment count in a 4th column, then calculate what half the shipment total count is, then perform a lookup using this value on the running total.

Say your running total is in D2:D20, your prices are C2:C20 and the shipping counts B2:B20

=LOOKUP(SUM(B2:B20)/2,D2:D20,C2:C20)

Would give you the weighted median.

for the running total, =sum(B2:B$2) in D2 and copy down

 

[barry houdini]

Hello Weaver,

That sort of approach will only work if C2:C10 is sorted ascending, also you'd always get one of the values from C2:C10 as the result, whereas a genuine median (by excel's definition at least) would give you a value halfway between the midpoints given an even number of values, therefore possibly not a value in the list

This formula will give you the median on unsorted values

=MEDIAN(LOOKUP(ROW(INDIRECT("1:"&SUM(B2:B10)))-1,SUBTOTAL(9,OFFSET(B1,0,0,ROW(B2:B10)-ROW(B2)+1)),C2:C10))

confirmed with CTRL+SHIFT+ENTER

 

[jkgreen]

Wouldn't a SUMPRODUCT work as well?

=SUMPRODUCT(B2:B10,C2:C10)/SUM(B2:B10)

 

[barry houdini]

Hello jk,

That would be the correct formula for a weighted average, which isn't the same as a weighted median......

 

[Richard Schollar]

Hello Weaver,

That sort of approach will only work if C2:C10 is sorted ascending, also you'd always get one of the values from C2:C10 as the result, whereas a genuine median (by excel's definition at least) would give you a value halfway between the midpoints given an even number of values, therefore possibly not a value in the list

This formula will give you the median on unsorted values

=MEDIAN(LOOKUP(ROW(INDIRECT("1:"&SUM(B2:B10)))-1,SUBTOTAL(9,OFFSET(B1,0,0,ROW(B2:B10)-ROW(B2)+1)),C2:C10))

confirmed with CTRL+SHIFT+ENTER

That's clever Barry. Surprisingly straightforward when I started analysing it too

By the way, given your absence from at least the last two 'Thursday nights', you owe me two pints

 

[barry houdini]

Thanks Richard

By the way, given your absence from at least the last two 'Thursday nights', you owe me two pints

When's the next one then, I'll try to make it