Thanks:  0
Likes:  0

1. ## weighted median

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.

2. ## Re: weighted median

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.

3. ## Re: weighted median

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

4. ## Re: weighted median

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.

5. ## Re: weighted median

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

6. ## Re: weighted median

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

7. ## Re: weighted median

Wouldn't a SUMPRODUCT work as well?

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

8. ## Re: weighted median

Hello jk,

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

9. ## Re: weighted median

Originally Posted by 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
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

10. ## Re: weighted median

Thanks Richard

Originally Posted by Richard Schollar
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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•