Excel: How to take a weighted average where blank values cause proportional weighting shifts
I have five categories. Each category has a weight in row 2 and a value in row 3. In cell G3 I'd like to have a formula that takes the weighted average of all of the categories.
The major caveat, some categories will not have values. If a category has a blank value, its weighting should be evenly distributed to the remaining categories that do have values, and then the weighted average should be taken. See Below for 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 have (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:
In the above scenario there are two categories with missing values and both have a weighting of 0.15. There are 3 categories with values. So each missing category will redistribute 0.15/3 = 0.05 weighting to the three remaining categories. So each category will have an increased weighting of 2*0.05 = 0.1 because of the 2 missing categories. The yellow box displays the correct weighted average.
One further caveat, it would be great if the formula could be dynamic enough to work for ranges of larger than 5 categories. Any ideas on how to implement something like this?
This seems to work for me - I have a feeling it could be simplified, but it just works through the steps outlined 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
- Move values from column to rows in Excel
- Application.FileDialog vs GetOpenFilename: What is the difference?
- Only display the values in a certain rank position from filtered and sorted list
- Convert table to reflect column names as row values
- Excel: Dynamic stacking or arrays n-number of times
- Convert Excel formula to Power Query (if countifs)
- Trying to hide rows in excel based on two values
- Combine every unique value from column A with duplicated values into a new sheet
- Import formula from variable - Excel VBA
- VBA Macro to detect unique entries and copy them from one workbook to another is copying a small fraction of duplicates along with the uniques
- Copy paste cells from button row
- Get the value of the second non-empty cell in a row
- SUMIF with UNIQUE criteria for multiple columns, adding all values with same name
- How can I use filter function in Excel to filter based on selections including those that are "ALL"?
- Openpyxl - I can't set cell format and sum values correctly in time format (h:mm)
- how to mask hash users into random values [email protected] in azure databricks
- Find/Replace Row number within Formula
- Extracting & summing data from a single cell in excel
- How to map table references in a JSON file to corresponding values in an Excel file using Python?
- Conditional Formatting to highlight current date on calendar
- Convert number in excel to M or B
- How to have instructional text in Excel?
- How to copy a range to several worksheets by using a list of worksheets to copy to
- VBA - exclude certain boxes from a general check
- How to count resolved comments only via VBA in Excel
- Excel VBA - how do I get the RowSource of a ComboBox to change depending on the value of another Combobox?
- check if a file is open in Python
- How to copy only plain text of cells in Excel?
- VBA - Capitalise range of cells before the last period
- Getting the next value of the same user if the type is "no type"