weighted median

sarahrosenberg

Board Regular
Joined
Aug 27, 2002
Messages
190
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi I realize this is probably a dead thread but I could use some help with the weighted, unsorted median. When I use the suggested "=MEDIAN(LOOKUP(ROW(INDIRECT("1:"&SUM(B2:B10)))-1,SUBTOTAL(9,OFFSET(B1,0,0,ROW(B2:B10)-ROW(B2)+1)),C2:C10))" it returns the top value (the value in the first row) in the shipped count column. I originally used it on my own data and it pulled the top value there as well, then I made a set of sample data that matched the original posters question and it again returned the value in the first row.
 
Upvote 0
Hello super_noob, welcome to MrExcel

The formula you quoted is an "array formula" so it needs to be entered with a specific key combination - it sounds like you haven't done that.

Select the cell with the formula, press F2 key to select formula then hold down CTRL and SHIFT keys while pressing ENTER. If done correctly you will see curly braces like { and } around the formula in the formula bar.....and you should get correct results
 
Upvote 0
Greetings,

Will the weighted median formula from this thread work with non-integer numbers?

=MEDIAN(LOOKUP(ROW(INDIRECT("1:"&SUM(B2:B10)))-1,SUBTOTAL(9,OFFSET(B1,0,0,ROW(B2:B10)-ROW(B2)+1)),C2:C10))
 
Upvote 0
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

---------------------
Seems to work, until sum(b2:b10) exceeds the number of rows in the spreadsheet, so when you are working with millions of items, such as stock trading volumes on actively traded stocks, the formula doesn't work.
 
Upvote 0
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
Hi, I found this old - yet helpful - thread. I'm trying to calculate a weighted median but your formula doesn't seem to work for me and I'm suspecting it may be because my C2:C10 equivalent column is comprised of fractional weights. Could this be the case and, if so, might you please have a solution? Many thanks, Lawrence
 
Upvote 0
Hi, I found this old - yet helpful - thread. I'm trying to calculate a weighted median but your formula doesn't seem to work for me and I'm suspecting it may be because my C2:C10 equivalent column is comprised of fractional weights. Could this be the case and, if so, might you please have a solution? Many thanks, Lawrence
Sorry, I meant my B2:B10 column, not C2:C10.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top