# Dynamic/Conditional Weighted Averages

#### Roballistic

##### New Member
Example_Submitted_2.2.2021.xlsx
ABCDEFGHIJKLMNOPQRSTU
1Weighting=>20.0%20.0%5.0%10.0%20.0%15.0%10.0%
2Work CompletedMetric A (#)Metric B (#)Metric C (#)Metric D (#)Metric E (%)Metric F (%)Metric G (%)Weighted Score
3January48211198.0%94.0%98.0%96.9%<= This Weighted Score calculates correctly when we have a value in every cell (C3:I3)
4February30100.0%98.0%84.8%<= This Weighted Score calculates incorrectly when we have no value in some cells (C4:F4, H4)
5March2512100.0%100.0%95.0%98.3%<= This Weighted Score calculates incorrectly when we have no value in some cells (D5, F5)
6April6055.0%
7May1055.0%QUESTION:
8June355.0%Is there a way to recalibrate the weight of each matric if 1 or more of the values are blank?
9July455.0%For example, Can Excel look at these 7 metrics and original distribution of weighting for each
10August8955.0%and then when it sees only 5 apply, it can recalculate weighting based on only 5 metrics??
11September2055.0%
12October1555.0%
13November5455.0%
14December2355.0%
15Total381313199.3%97.0%97.0%98.8%
16
Example
Cell Formulas
RangeFormula
J3:J15J3=(((1-C3/\$B3)*\$C\$1)+((1-D3/\$B3)*\$D\$1)+((1-E3/\$B3)*\$E\$1)+((1-F3/\$B3)*\$F\$1)+((G3*\$G\$1)+((H3*\$H\$1)+(I3*\$I\$1))))
B15:F15B15=SUM(B3:B14)
G15:I15G15=AVERAGE(G3:G14)

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### lrobbo314

##### Well-known Member

Book3
ABCDEFGHIJ
1Weighting=>0.20.20.050.10.20.150.1
2Work CompletedMetric A (#)Metric B (#)Metric C (#)Metric D (#)Metric E (%)Metric F (%)Metric G (%)Weighted Score
3January482110.980.940.980.87145833
4February3010.980.298
5March2512110.950.683
6April600
7May100
8June30
9July40
10August890
11September200
12October150
13November540
14December230
15Total38131300.993333330.970.970.98867323
Sheet3
Cell Formulas
RangeFormula
J3:J15J3=((SUMPRODUCT(((1-C3:F3/B3)*\$C\$1:\$F\$1)))+SUM(IF(NOT(ISBLANK(G3:I3)),G3:I3*\$G\$1:\$I\$1)))-SUMPRODUCT(ISBLANK(C3:F3)*\$C\$1:\$F\$1)
B15:F15B15=SUM(B3:B14)
G15:I15G15=AVERAGE(G3:G14)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

#### Roballistic

##### New Member

Book3
ABCDEFGHIJ
1Weighting=>0.20.20.050.10.20.150.1
2Work CompletedMetric A (#)Metric B (#)Metric C (#)Metric D (#)Metric E (%)Metric F (%)Metric G (%)Weighted Score
3January482110.980.940.980.87145833
4February3010.980.298
5March2512110.950.683
6April600
7May100
8June30
9July40
10August890
11September200
12October150
13November540
14December230
15Total38131300.993333330.970.970.98867323
Sheet3
Cell Formulas
RangeFormula
J3:J15J3=((SUMPRODUCT(((1-C3:F3/B3)*\$C\$1:\$F\$1)))+SUM(IF(NOT(ISBLANK(G3:I3)),G3:I3*\$G\$1:\$I\$1)))-SUMPRODUCT(ISBLANK(C3:F3)*\$C\$1:\$F\$1)
B15:F15B15=SUM(B3:B14)
G15:I15G15=AVERAGE(G3:G14)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Book3
ABCDEFGHIJ
1Weighting=>0.20.20.050.10.20.150.1
2Work CompletedMetric A (#)Metric B (#)Metric C (#)Metric D (#)Metric E (%)Metric F (%)Metric G (%)Weighted Score
3January482110.980.940.980.87145833
4February3010.980.298
5March2512110.950.683
6April600
7May100
8June30
9July40
10August890
11September200
12October150
13November540
14December230
15Total38131300.993333330.970.970.98867323
Sheet3
Cell Formulas
RangeFormula
J3:J15J3=((SUMPRODUCT(((1-C3:F3/B3)*\$C\$1:\$F\$1)))+SUM(IF(NOT(ISBLANK(G3:I3)),G3:I3*\$G\$1:\$I\$1)))-SUMPRODUCT(ISBLANK(C3:F3)*\$C\$1:\$F\$1)
B15:F15B15=SUM(B3:B14)
G15:I15G15=AVERAGE(G3:G14)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
Well, the problem with this approach is that if you look at J4 and review the row of responses we had, 100% and 98%, I would need the formula to recognize that there are only 2 values and thus would need to update weighting to 80% for Column G (G1) and 20% for Column I (I1). Same would happen for other rows, which is the hard part about all of this. But I think you were on the right track and thanks for the quick response!

#### Roballistic

##### New Member
Well, the problem with this approach is that if you look at J4 and review the row of responses we had, 100% and 98%, I would need the formula to recognize that there are only 2 values and thus would need to update weighting to 80% for Column G (G1) and 20% for Column I (I1). Same would happen for other rows, which is the hard part about all of this. But I think you were on the right track and thanks for the quick response!
I may have stated that slightly wrong. So let me try again:

Cell G1 = 0.20 or 20%
Cell I1 = .10 or 10%

After it is determined those are the only 2 columns with data for a given month, then the weighting I think should be manipulated in the formula to reflect:

Cell G1 would actually be 0.667 or 66.7%
Cell I1 would actually be 0.333 or 33.3%

Maybe my math is not working tonight, but I basically need the weighted %'s to move depending on what items we have or do not have within the formula for each month, while leaving Row 1's Weighted percentages as displayed.

Replies
2
Views
491
Replies
0
Views
763
Replies
4
Views
110
L
Replies
7
Views
298
Legacy 428781
L
Replies
10
Views
228

1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back