Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: weighted median

  1. #1
    Board Regular
    Join Date
    Aug 2002
    Location
    Nashville, TN USA
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Jan 2003
    Location
    CA, Bay Area
    Posts
    2,062
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by nbrcrunch; Jan 7th, 2011 at 08:50 AM.
    Give a man a fish & you've fed him once. Teach a man to fish & you feed him for a lifetime. Give a man a fish a day & he becomes a spoiled adult still living with his parents at 35.

  3. #3
    Board Regular
    Join Date
    Aug 2002
    Location
    Nashville, TN USA
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: weighted median

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

  4. #4
    Board Regular
    Join Date
    Jan 2003
    Location
    CA, Bay Area
    Posts
    2,062
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Give a man a fish & you've fed him once. Teach a man to fish & you feed him for a lifetime. Give a man a fish a day & he becomes a spoiled adult still living with his parents at 35.

  5. #5
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  6. #6
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by barry houdini; Jan 7th, 2011 at 10:48 AM.

  7. #7
    Board Regular
    Join Date
    Nov 2008
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: weighted median

    Wouldn't a SUMPRODUCT work as well?

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

  8. #8
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: weighted median

    Quote Originally Posted by barry houdini View Post
    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
    Richard Schollar

    Using xl2013

  10. #10
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: weighted median

    Thanks Richard

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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