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):
Bookmarks