# Weighted Average/SumProduct

#### Vjangal

##### New Member
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

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### pgc01

##### MrExcel MVP
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

##### New Member
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

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

##### Well-known Member
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

##### New Member
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
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>

##### Well-known Member
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

##### Board Regular
Hi VJ, not clear what you are doing but if you have N sub-score cards, each with a weighting of wi 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 wi*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

##### New Member
Thanks a lot, this seems to be working

Replies
1
Views
426
Replies
3
Views
184
Replies
7
Views
727
Replies
3
Views
188
Replies
1
Views
1K

1,191,086
Messages
5,984,531
Members
439,895
Latest member
ddkexcel

### 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