Excel How to take a weighted average where blank values cause proportional weighting shifts

0 votes

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:

enter image description here

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:

enter image description here

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?

Sep 26 in Others by Kithuzzz
• 20,660 points
65 views

1 answer to this question.

0 votes

This seems to work for me, though I think it may be made simpler by following the steps specified in the question:

=SUMPRODUCT(B2:F2*B3:F3)+SUMPRODUCT(SUMPRODUCT((B3:F3="")*B2:F2)*B3:F3)/COUNT(B3:F3)

enter image description here

If the blank cells are not actually blank but contain "" returned as the result of a formula, you could try

=SUMPRODUCT(B2:F2*N(+B3:F3))+
SUMPRODUCT(SUMPRODUCT((B3:F3="")*B2:F2)*N(+B3:F3))/COUNT(B3:F3)

As suggested by the brilliant @XOR LX in this answer

answered Sep 27 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
37 views
0 votes
1 answer
0 votes
1 answer

How to take a screenshot of a current Activity and then share it?

For me, I captured and then shared ...READ MORE

answered Feb 8 in Others by Soham
• 9,670 points
55 views
0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10 in Others by gaurav
• 22,040 points
296 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
140 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
236 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
106 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
69 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
63 views
0 votes
1 answer

In Excel, how to find a average from selected cells

If one has the dynamic array formula ...READ MORE

answered Oct 9 in Others by narikkadan
• 37,660 points
49 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP