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, 2022 in Others by Kithuzzz
• 38,010 points
391 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, 2022 by narikkadan
• 63,420 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, 2022 in Others by narikkadan
• 63,420 points
759 views
0 votes
1 answer

How to validate values entered in a multiline Excel cell?

Try: The formula in B1: =AND(BYROW(TEXTSPLIT(A1," "," ",1),LAMBDA(x,SUM((LEN(x)={3,7,4,4,5})ISNUMBER(-x)(MID(TAKE(x,-1),3,1)="."))))) Or, write a ...READ MORE

answered Jan 13, 2023 in Others by narikkadan
• 63,420 points
235 views
0 votes
1 answer

Google Spreadsheet/ Excel - how to find matching values in column A, having necessary values in column M

Try this: =COUNTIF($M$2:$M$5;A2) If the product is present in ...READ MORE

answered Feb 10, 2023 in Others by narikkadan
• 63,420 points
234 views
0 votes
1 answer
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, 2022 in Others by Edureka
• 13,670 points
1,239 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, 2022 in Others by Edureka
• 13,670 points
1,427 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, 2022 in Others by Edureka
• 13,670 points
699 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,335 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, 2022 in Others by narikkadan
• 63,420 points
714 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, 2022 in Others by narikkadan
• 63,420 points
462 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