Dynamic/Conditional Weighted Averages

Roballistic

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 14, 2008
Messages
3,152
Office Version
  1. 365
Platform
  1. Windows
How about this?

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.
 
Solution

Roballistic

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
How about this?

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.

How about this?

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
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top