I have five categories. There is a weight for each category in row 2 and a value in row 3. I want a formula that computes the weighted average of each category to go in cell G3.
The main qualification is that some categories won't have values. If a category is empty, the other categories with values should get an equal amount of its weighting before calculating the weighted average. Here are two examples:
In this photo, cell C3 is missing a value. There are 4 categories remaining that do have values. So its weighting of 0.2 will be redistributed four ways, so each of the remaining categories will have a (02./4 =0.05) increase in their weightings in the weighted average. The highlighted cell is the desired result.
I will further illustrate with one additional scenario:
Two categories in the aforementioned case contain missing data and a weighting of 0.15 each. Three categories each have values. Therefore, the three remaining categories will receive a new weighting of 0.15/3 = 0.05 for each missing category. The 2 missing categories will result in an enhanced weighting for each category of 2*0.05 = 0.1. The appropriate weighted average can be seen in the yellow box.
One more restriction: It would be ideal if the formula were flexible enough to accommodate ranges larger than 5 categories. Any suggestions on how to put something similar into practice?
