+ Reply to Thread
Results 1 to 3 of 3

Weighted Averages

  1. #1
    Registered User
    Join Date
    02-01-2012
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    65

    Weighted Averages

    I have 3 sets of data that I am trying to average together. However, I don't want each set of data to be weighted the same. I understand how to do a weighted average, (data1*Weight%1+data2*Weight%2+...), my problem is that each data set isn't always represented.

    Using my spreadsheet as an example: Weighted Averages.xls

    Data set 1 is worth 70% (cell D2), data set 2 is worth 20% (cell G2), and data set 3 is worth 10% (cell J2).

    Person 1 is represented in each of the data sets (1, 2, and 3), however, person 2 is only represented in data sets 1 and 3. So when I go to calculate Person 2's weighted average it is calculating data set 2 as being 0. The way it is set up now if Person 2's weighted average is calculated, it will only calculate 70% of data set 1 and 10% of data set 2 leaving out the 20%. What it really should be doing is calculating data set 1 as 87.5% and data set 2 as 12.5%. Similarly in Person 4's weighted average, since data set 1 and data set 3 are blank, data set 2 should count as 100% of the weighted value.

    I hope that wasn't too confusing, if you need further explanation let me know. I am basically trying to find a way to perform this calculation using formulas that doesn't involve about a dozen nest =IF formulas. Thanks for helping.


    Sheet 2 has a basic table for what the percentages should be when certain data sets are missing (the base percentages need to be able to change):

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Weighted Averages

    (data1*Weight%1+data2*Weight%2+...)
    I think what this is ignoring is that a rigorous weighted average divides this sum by the sum of the weights. (data1*wt%1+data2*wt%2+...)/(wt%1+wt%2+...) If the weights are correctly normalized, then you can ignore the divisor, because it adds up to 1. In your case, it doesn't sound like you can assume the wts add up to 1.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    02-01-2012
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    65

    Re: Weighted Averages

    Thanks. Wasn't thinking all the way through. Got it now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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