Need help with formulas.

[Southern23]

Hopefully I can explain this.

The numbers in column D are used as a percentage of the score (20 = 9.164%, and each 5 = 2.291%) They will be used in increments of 5 in column D. D12 will equal 55% of the total score; this is why each 5 = 2.291. 24 x 5 = 54.984 (55%)

For example: If 15 is input into D6, it will actually equal 6.873 and added together with the totals in D7 - D11.

I assume the formula will be in D12, but it will have to convert each cell into the percentage and then add them for the total of 55 or less.

Column C just shows the max score for each criteria.

Please help with this one. Thank you for any help.

I'm using Excel 2007.
<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=283><COLGROUP><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><TBODY><TR style="HEIGHT: 45.75pt; mso-height-source: userset" height=61><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 24pt; HEIGHT: 45.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=61 width=32></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 189pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 width=251 colSpan=3></TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=40></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl70 colSpan=2></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 rowSpan=3></TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=40></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl72 colSpan=2></TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=40></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: black 1pt solid" class=xl74 colSpan=2></TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 120pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl78 height=156 rowSpan=6></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=185></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81></TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 139pt; HEIGHT: 20.1pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 height=26 width=185>Criteria 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl64>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>(D6)</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 139pt; HEIGHT: 20.1pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 height=26 width=185>Criteria 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl64>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>(D7)</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 139pt; HEIGHT: 20.1pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 height=26 width=185>Criteria 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl64>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>(D8)</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 139pt; HEIGHT: 20.1pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 height=26 width=185>Criteria 4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl64>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>(D9)</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 139pt; HEIGHT: 19.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 height=26 width=185>Criteria 5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl64>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>(D10)</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 19.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=26></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 width=185>Criteria 6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl64>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>(D11)</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 19.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=26></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 139pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=185>PERCENT OF TOTAL GRADE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl65>55%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84>(D12)</TD></TR></TBODY></TABLE>

 

[rdw72777]

Just to be clear, if the score is 20 in cells D6 through D11, the value in D12 should be 55, right?

if it is, try this:

=SUM($D$6:$D$11)*($C$12*100)/SUM($C$6:$C$11)