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?

Sep 26, 2022 in Others 514 views

## 1 answer to this question.

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)
```

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

• 63,720 points

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

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

## 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

## 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

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

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

## Convert Rows to Columns with values in Excel using custom format

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

## IF - ELSE IF - ELSE Structure in Excel

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