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>