+ Reply to Thread
Results 1 to 4 of 4

Weighted median for large dataset with many varying weights

  1. #1
    Registered User
    Join Date
    10-23-2019
    Location
    California
    MS-Off Ver
    Office 365
    Posts
    2

    Question Weighted median for large dataset with many varying weights

    Hi everyone! I am trying to figure out how to calculate a weighted median, and I can't for the life of me figure it out. I have searched the internet and most suggestions actually lead you to a weighted average or a simple median. The solution that seemed most promising was in a thread on this forum called "Weighted Percentile Calculation Help" posted by g3diamondback back in 2014, which uses an INDEX formula to essentially create a new list of numbers where numbers repeat depending on their weight - however I don't think it works in my situation because all my weights are non-whole numbers, so you can't really create a new set of numbers with one cell per number where one number counts for 239.6794891 appearances in the data set.

    Here is a sample of the data:

    fam_inc: family income, the variable I would like to get the median of
    weight: the weight for each income, which I would like to apply to the median calculation

    fam_inc weight
    5149 33.67798233
    5106 188.9760132
    5072 3.108463287
    4994 611.1603394
    4982 48.08955765
    4975 51.73936081
    4940 51.73936081
    4893 699.7153931
    4877 500.3041077
    4864 51.73936081
    4783 47.6829071
    4694 51.73936081
    4612 9.833632469
    4604 1.244086146
    4543 85.56001282
    4519 47.6829071
    4499 58.2733078
    4429 44.60183716
    4420 51.73936081

    Thank you for your help!

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Weighted median for large dataset with many varying weights

    Hi -

    Assuming your Family Income is in Column A and the Weight is in Column B, can't you simply use the following formula as an Array formula (ctrl-shift-enter)?

    =MEDIAN((A2:A20*B2:B20))

    Based on the data above, the median of the product of the two columns is 242864.5596

    Is that the expected outcome?
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Weighted median for large dataset with many varying weights

    OK - I went back and reread your post. So the weights represent the number of occurrences of each value in the data set. Looking at a statistics website, the way to calculated the location of the median value is to take the number or occurrences + 1 and divide by 2. (n+1)/2. So, I created the third column C that accumulates the weights. Then, I take the sum of all the weights + 1 and divide by 2. This gives me the location within the accumulated weights of the median value. Then I use INDEX and MATCH to find the corresponding values of Family Income and simply average those and divide by 2. Based on your data set, the Median Family income based on "weighted" occurrences, is 4916.5.

    The formula looks like:

    =(INDEX($A$3:$A$21,MATCH((SUM(B3:B21)+1)/2,$C$3:$C$21,1))+INDEX($A$3:$A$21,MATCH((SUM(B3:B21)+1)/2,$C$3:$C$21,1)+1))/2

    The first INDEX/MATCH finds the first value of income, and the second INDEX/MATCH finds the next one.

    Attached is my spreadsheet.

    Hope this helps.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-20-2020
    Location
    Grimstad, Norway
    MS-Off Ver
    16.40
    Posts
    1

    Re: Weighted median for large dataset with many varying weights

    This was a nice thread to find. I have implemented weighted means, SDs and percentiles (according to the NIST standard, equivalent to percentile.exc) in an excel sheet based on the tip about INDEX. I hope others will find it useful too.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Weighted Median
    By jhinkel in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-13-2020, 09:09 AM
  2. Calculating a Weighted Median
    By kaybee in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-14-2019, 06:35 AM
  3. Replies: 3
    Last Post: 10-20-2017, 12:40 AM
  4. How to do a weighted median?
    By bnelson333 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-01-2016, 01:59 PM
  5. Weighted Median
    By Sammz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-01-2015, 07:56 AM
  6. [SOLVED] Need a UDF to calculate 'weighted median'
    By alice2011 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-29-2015, 09:59 AM
  7. Replies: 3
    Last Post: 01-23-2014, 10:13 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1