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
- Largest sum of consecutive values in row, but is it still consecutive in the cell of the last column?
- Dynamically Inserting a row and iterating over it in VBA?
- VBA class member function and class definition problem continues
- How can I perform COALESCE in power query?
- VBA class member function and class definition problem
- How can I set OptionButton font using VBA For Loop?
- VBA remove string custom salutation name from list table excel
- How to import many dataframes from R into a one Excel file in a simple way?
- How to add a missing column to the Transform Sample File
- Pull data from multiple workbooks based on a dynamic list
- Dependent dropdown list in Excel ,Data Validation and VBA
- Excel Formula: Filter Group based on Smallest Values
- Find column name of active cell within ListObject in Excel
- Strange characters found in XML file and PowerShell output after exporting from Excel: ​
- MID function inside COUNTIFS
- Finding Max of 3 Inputs VBA
- How to align duplicates on the same rows in Excel
- How to use .HTMLBody to hyperlink a cell value?
- Adding cell values, specific to each page, to each header
- Excel VBA macro for URL redirect detecting returns 200 instead of 301 sometimes
- Count with specific values in columns
- Convert IEEE Float hex string to an Excel VBA Single (float)
- Openpyxl or Pandas, which is better at reading data from a excel file and returning corresponding values
- Excel Function: Using a specific criteria to form an array to corresponding values
- How to pass metadata to Google Drive API upload with VBA
- IsError not capturing error returned by expression
- Excel VBA multi select drop down list
- Imported Excel file with dates, came out as numbers, trying to return to date format
- Creating Conditional Column based on another Table's Column
- Convert Range to a table