Weighted Average/SumProduct

[Vjangal]

Hi, I need help with the below
I would like for the weighted average function to ignore a blank or a zero value and calculate the rating for the remaining sections of data. I am working on a scorecard and the Overall scorecard will pick the rating from the individual scorecard and give out a combined rating. The sumproduct is not working. For example E6, F6 and G6 contain the percent allocation of the individual scorecards, if any one of them are 0% I want the formula to ignore and only compute for the remaining 2

H61, H81 and H101 contain ratings for the individual components

=((IF(H61="",0,$E$6*H61)+(IF(H81="",0,$F$6*H81)+(IF(H101="",0,$G$6*H101)))))

Much appreciated
thanks
vj

 

[pgc01]

hi vj
Welcome to the board

It's not clear to me what you need. You mean that your weights add up to 1 and you want to adjust them for the missing values?

Please post numerical examples for the cases of 0, 1 and 2 values equal to zero, with input and output values and the logic to get the results.

 

[Vjangal]

Thanks and here is the sample of the overall scorecard. This is the combined one which pulls the scores and goals from the individual scorecards. The X Y and Z show the percentage allocation of each of the scorecard

The formula I used is a straight weighted average in Goal / Score and the Rating columns. The Metric F should actually be Goal 7.5 and Score 7.70 but it is taking into account the Zero weight of 2 of the sub scorecards
CODE =($E$5*G54)+($F$5*G74)+($G$5*G94)


<TABLE style="WIDTH: 339pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=451 border=0 x:str><COLGROUP><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 694" width=19><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" span=3 width=89><TBODY><TR style="HEIGHT: 9pt; mso-height-source: userset" height=12><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 9pt; BACKGROUND-COLOR: transparent" width=46 height=12></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=19></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=55></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=89></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=89></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=89></TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=110 colSpan=2 height=17 x:str="Functions: ">Functions: </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=19></TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=55>X</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=89>Y</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=89>Z</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=89>Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=46 height=20></TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64></TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=19></TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=55 x:num="0.25">25%</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=89 x:num="0.75">75%</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=89 x:num="0">0%</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=89 x:num="1" x:fmla="=D3+E3+F3">100%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 138pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=184 colSpan=4 height=20>Metrics</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 201pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=267 colSpan=3>Performance</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 138pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=184 colSpan=4 height=20> </TD><TD class=xl44 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=89>Goal</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=89>Score</TD><TD class=xl44 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=89>Rating</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=46 height=20> </TD><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=83 colSpan=2>Metric A</TD><TD class=xl43 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=55> </TD><TD class=xl46 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>62.00</TD><TD class=xl49 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>83.33</TD><TD class=xl52 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; COLOR: green; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num>5</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=46 height=20> </TD><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=83 colSpan=2>Metric B</TD><TD class=xl43 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=55> </TD><TD class=xl47 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>12.00</TD><TD class=xl49 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>12.00</TD><TD class=xl53 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; COLOR: blue; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num>3</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=46 height=20> </TD><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=83 colSpan=2>Metric C</TD><TD class=xl43 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=55> </TD><TD class=xl47 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>72.00</TD><TD class=xl49 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>83.00</TD><TD class=xl53 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; COLOR: green; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num>5</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=46 height=20> </TD><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=83 colSpan=2>Metric D</TD><TD class=xl43 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=55> </TD><TD class=xl47 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>315.00</TD><TD class=xl49 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="306.52499999999998">306.53</TD><TD class=xl53 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; COLOR: blue; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num="2.75">3</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=46 height=20> </TD><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=83 colSpan=2>Metric E</TD><TD class=xl43 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=55> </TD><TD class=xl47 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>80.00</TD><TD class=xl49 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>63.50</TD><TD class=xl53 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; COLOR: red; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" x:num>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=46 height=20> </TD><TD class=xl41 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=83 colSpan=2>Metric F</TD><TD class=xl43 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=55> </TD><TD class=xl48 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ff99cc" x:num="1.875">1.88</TD><TD class=xl50 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ff99cc" x:num="1.925">1.93</TD><TD class=xl54 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; COLOR: red; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ff99cc; mso-ignore: style" x:num="0.75">1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl39 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 97pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ccffcc" width=129 colSpan=3 height=20>Overall Rating</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=55> </TD><TD class=xl45 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=89> </TD><TD class=xl42 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=89> </TD><TD class=xl51 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext; COLOR: blue; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc; mso-ignore: style" x:num="3.6124999999999998">3.61</TD></TR></TBODY></TABLE>

 

[Vjangal]

Hi, Can I try Vlookup here? would that help to ignore the zero values in the 3 sections?

 

[Sal Paradise]

You are showing us where the problem is showing up, but not the sheet with the data that isn't being weighted properly. Please give us sample data, what you expect it to sum to, and what it actually is summing to.

 

[Vjangal]

Got it sorry, below is the 3 components data that is pulled into the top overall scorecard and the formula I used in the score and the goal is a long one which I am hoping can be shortened
LONG Formula(works) :=IF(AND($G$80="",$G$100=""),$G$60,IF(AND($G$60="",$G$80=""),$G$100,IF(AND($G$60="",$G$100=""),$G$80,$AD$18)))
in Cell AD18 the rest of the formula :=IF($G$60="",$G$80*$F$6+$G$100*$G$6,IF($G$80="",$G$60*$E$6+$G$100*$G$6,IF($G$100="",$G$60*$E$6+$G$80*$F$6,$G$60*$E$6+$G$80*$F$6+$G$100*$G$6)))

Metric Weight Goal Score Rating
X = 25%
<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=320 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>Metric 1</TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num="0.15">15%</TD><TD class=xl28 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num>315.00</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; COLOR: red; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ffff99; mso-ignore: style" width=64 x:num>278.70</TD><TD class=xl29 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white 1pt solid; BORDER-LEFT: white 1pt solid; WIDTH: 48pt; COLOR: red; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" width=64>1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>Metric 2</TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num="0.1">10%</TD><TD class=xl28 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num>80.00</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; COLOR: green; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ffff99; mso-ignore: style" width=64 x:num>63.50</TD><TD class=xl29 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white; BORDER-LEFT: white 1pt solid; COLOR: green; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" width=64>5</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>Metric 3</TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num="0.15">15%</TD><TD class=xl28 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num>7.50</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; COLOR: green; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ffff99; mso-ignore: style" width=64 x:num>7.70</TD><TD class=xl29 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white; BORDER-LEFT: white 1pt solid; COLOR: green; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" width=64>5</TD></TR></TBODY></TABLE>
Y = 75%
<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=320 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>Metric 1</TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num="0.15">15%</TD><TD class=xl28 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num>315.00</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; COLOR: red; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ffff99; mso-ignore: style" width=64 x:num>315.80</TD><TD class=xl29 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white 1pt solid; BORDER-LEFT: white 1pt solid; WIDTH: 48pt; COLOR: red; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" width=64>2</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>Metric 2</TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num="0.1">10%</TD><TD class=xl28 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num>80.00</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; COLOR: green; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ffff99; mso-ignore: style" width=64 x:num>63.00</TD><TD class=xl29 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white; BORDER-LEFT: white 1pt solid; COLOR: green; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" width=64>1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>Metric 3</TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num="0.15">15%</TD><TD class=xl28 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num></TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; COLOR: green; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ffff99; mso-ignore: style" width=64 x:num></TD><TD class=xl29 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white; BORDER-LEFT: white 1pt solid; COLOR: green; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" width=64></TD></TR></TBODY></TABLE>
Z = 0%
<TABLE style="WIDTH: 134pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=178 border=0 x:str><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" span=2 width=89><TBODY><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 67pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=89 height=18 x:num><TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>Metric 1</TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num="0.15">15%</TD><TD class=xl28 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num>3.15</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ffff99" width=64></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>Metric 2</TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num="0.1">10%</TD><TD class=xl28 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num>80.00</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ffff99" width=64> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=64 height=18>Metric 3</TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64 x:num="0.15">15%</TD><TD class=xl28 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64> </TD><TD class=xl29 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=64> </TD></TR></TBODY></TABLE></TD><TD class=xl28 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 67pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ffff99" width=89> </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 67pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=89 height=18 x:num></TD><TD class=xl28 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 67pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ffff99" width=89> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 67pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" width=89 height=18> Thanks
vj
</TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 67pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" width=89> </TD></TR></TBODY></TABLE>

 

[Sal Paradise]

It isn't clear what you're trying to do at all.

You are giving us cell references in your formulas, but we don't see the cells you're referring to. You're saying it's a weighted average, but not of what and what. You've said you want to exclude zeroes, but you haven't told us how you want them excluded or how the results would be expected to change.

 

[Jacophile]

Hi VJ, not clear what you are doing but if you have N sub-score cards, each with a weighting of w_i and if P of the score cards are blank, then you need to multiply the weighted average by N/(N-P) in order to achieve your goal. In other words the weighting of the remaining score cards needs to be w_i*N/(N-P) so that the sum of the w's from 1 to N-P is still 1.

So you need to think about what to do with the weightings, the zeros don't matter.

 

[Vjangal]

Thanks a lot, this seems to be working