Dynamic/Conditional Weighted Averages

[Roballistic]

Example_Submitted_2.2.2021.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Weighting=>		20.0%	20.0%	5.0%	10.0%	20.0%	15.0%	10.0%
2		Work Completed	Metric A (#)	Metric B (#)	Metric C (#)	Metric D (#)	Metric E (%)	Metric F (%)	Metric G (%)	Weighted Score
3	January	48	2	1	1	1	98.0%	94.0%	98.0%	96.9%		<= This Weighted Score calculates correctly when we have a value in every cell (C3:I3)
4	February	30					100.0%		98.0%	84.8%		<= This Weighted Score calculates incorrectly when we have no value in some cells (C4:F4, H4)
5	March	25	1		2		100.0%	100.0%	95.0%	98.3%		<= This Weighted Score calculates incorrectly when we have no value in some cells (D5, F5)
6	April	60								55.0%
7	May	10								55.0%		QUESTION:
8	June	3								55.0%		Is there a way to recalibrate the weight of each matric if 1 or more of the values are blank?
9	July	4								55.0%		For example, Can Excel look at these 7 metrics and original distribution of weighting for each
10	August	89								55.0%		and then when it sees only 5 apply, it can recalculate weighting based on only 5 metrics??
11	September	20								55.0%
12	October	15								55.0%
13	November	54								55.0%
14	December	23								55.0%
15	Total	381	3	1	3	1	99.3%	97.0%	97.0%	98.8%
16
Example

Cell Formulas
Range	Formula
J3:J15	J3	=(((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:F15	B15	=SUM(B3:B14)
G15:I15	G15	=AVERAGE(G3:G14)

 

[lrobbo314]

How about this?

Book3
	A	B	C	D	E	F	G	H	I	J
1	Weighting=>		0.2	0.2	0.05	0.1	0.2	0.15	0.1
2		Work Completed	Metric A (#)	Metric B (#)	Metric C (#)	Metric D (#)	Metric E (%)	Metric F (%)	Metric G (%)	Weighted Score
3	January	48	2	1	1		0.98	0.94	0.98	0.87145833
4	February	30					1		0.98	0.298
5	March	25	1		2		1	1	0.95	0.683
6	April	60								0
7	May	10								0
8	June	3								0
9	July	4								0
10	August	89								0
11	September	20								0
12	October	15								0
13	November	54								0
14	December	23								0
15	Total	381	3	1	3	0	0.99333333	0.97	0.97	0.98867323
Sheet3

Cell Formulas
Range	Formula
J3:J15	J3	=((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:F15	B15	=SUM(B3:B14)
G15:I15	G15	=AVERAGE(G3:G14)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Roballistic]

How about this?

Book3
	A	B	C	D	E	F	G	H	I	J
1	Weighting=>		0.2	0.2	0.05	0.1	0.2	0.15	0.1
2		Work Completed	Metric A (#)	Metric B (#)	Metric C (#)	Metric D (#)	Metric E (%)	Metric F (%)	Metric G (%)	Weighted Score
3	January	48	2	1	1		0.98	0.94	0.98	0.87145833
4	February	30					1		0.98	0.298
5	March	25	1		2		1	1	0.95	0.683
6	April	60								0
7	May	10								0
8	June	3								0
9	July	4								0
10	August	89								0
11	September	20								0
12	October	15								0
13	November	54								0
14	December	23								0
15	Total	381	3	1	3	0	0.99333333	0.97	0.97	0.98867323
Sheet3

Cell Formulas
Range	Formula
J3:J15	J3	=((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:F15	B15	=SUM(B3:B14)
G15:I15	G15	=AVERAGE(G3:G14)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

How about this?

Book3
	A	B	C	D	E	F	G	H	I	J
1	Weighting=>		0.2	0.2	0.05	0.1	0.2	0.15	0.1
2		Work Completed	Metric A (#)	Metric B (#)	Metric C (#)	Metric D (#)	Metric E (%)	Metric F (%)	Metric G (%)	Weighted Score
3	January	48	2	1	1		0.98	0.94	0.98	0.87145833
4	February	30					1		0.98	0.298
5	March	25	1		2		1	1	0.95	0.683
6	April	60								0
7	May	10								0
8	June	3								0
9	July	4								0
10	August	89								0
11	September	20								0
12	October	15								0
13	November	54								0
14	December	23								0
15	Total	381	3	1	3	0	0.99333333	0.97	0.97	0.98867323
Sheet3

Cell Formulas
Range	Formula
J3:J15	J3	=((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:F15	B15	=SUM(B3:B14)
G15:I15	G15	=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]

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.