Trying to make a grading rubic worksheet. Need help with letter to number formatting

Hello All,

I'm a (fairly) new CATE teacher in SC. I have a daily grading rubric that uses 3 different grading criteria on 1 spreadsheet. One part of the rubric consists of 5 columns. Each column carries a numeric weight that is removed from a total amount of 100. (Column b = -5, column c = -10, column d = -15, ...) There are 8 different infractions, with an corresponding letter, that can be inserted in the cells of those columns. There may be no letters in each cell or there could be (unlikely) as many as all 8 letters in the cell. So, If I have a "w" in the b column I want the formula to subtract 5 from the total. But I may have a "w" and an "s" in the cell and would therefore want the formula to subtract 10, etc.
Each cell in the row would have a cumulative effect on the total.

EDIT: Just for clarification. The letter does not have an actual numeric value. It's place in the cells of each column carries that value; so a "w" in the b column carries a weight a -5 while a "w" in the c column carries a weight of -10. Wasn't sure if that point came across in my initial rambling.

Just in case a picture would help. cell H2 is where the formula would go.
Capture.JPG


I'm VERY new to Excel and I have searched for an answer to this quandary but am no closer to a solution. This may be an impossible task but I'm hopeful.

Thanks for taking your time reading this.

John

Try

=100+(LEN(B2)*B$1)+(LEN(C2)*C$1)+(LEN(D2)*D$1)+(LEN(E2)*E$1)+(LEN(F2)*F$1)

in G2 and copy down