Custom Score for Spreadsheet

Question

I have a Google form that has multiple choice answers. I want to score the document (the spreadsheet that is generated) by giving each answer a numerical value (not just right or wrong, but answer A=1 point, answer B is 2 points, etc) , then take the total and multiply by 4 (this will bring the range of the answers from 24-120, but because of the particular questions, the totals will rarely be above 100.

Example:

What is the distance from your house to event?

A. 40+ Miles
B. <40 Miles
C. <20 Miles
D. <10 Miles
E. At Home!

In this case, "At Home!" would be the most valuable answer (5 points), and the answer choice "40+ Miles" would be least valuable (1 point).

Misc Notes:

1. The order of the answer choices does not matter; it's not a test, just a way of formulating "value" to a situation.

2. There are a total of 6 questions as of now, and 2 of those questions are irrelevant to the scoring

3. There are 5 answer choices for every question. Instead of getting a total and multiplying by the values of the answer could also be in increments of 4 if it makes formulating easier.

Answer 1

Use a set of cells in each row, which are not filled in the form, to calculated the weighted scores.

Assuming that the response to your example question is in column 3, then use a formula like:

= if(A3="A",1,if(A3="B",2, if(A3="C",3, if(A3="D",4,if(A2="E",5,0)))))

or a simpler way is to put the answers and scores into a table in another sheet and use a vlookup function like:
=vlookup(A3, Example_question-answers, 2, 0)

Then add the scored values together (eg if you used columns AT thru AX, then AY = AT2+AU2+AV2+AW2+AX2)