Assigning a value to a letter.

My company sends out satisfaction surveys that have possible responses of A, B, C, or D. I would like to enter the customer responses into excell and have it assign numberical values to each number, then calculate overall satisfaction.

So, a survey card may read (six questions):

A, A, B, A, C, D

i want "A" to be worth 100%, "B" to be worth 70%, "C" to be worth 40%, and "D" to be worth 10%,

what i would like to do is input the survey results, one per field, and have a final field that calculates customer satisfaction. in this case, the overall satisfaction would be 70% (calculated from averaging 100, 100, 70, 100, 40, 10).

is there anything that can do this, or must i just input the percentages manually?

(i attached an example of what i'm trying to accomplish)

thanks JB! the formula works perfectly. ok, in order that you can teach me to fish and not just give me a fish, i am going to ask for further clarification. The formula that you gave me is as follows.

=SUMPRODUCT(($B3:$G3=$L$3:$L$6)*$M$3:$M$6) / COUNTA($B3:$G3)

i understand that the "COUNTA" section is there in order to create the ratio.

two things i don't understand.

1) (easy i'm sure) how is the "SUMPRODUCT" formula prefix different than "SUM" one? what does it do differently, and why is it needed here?

2) how does that whole middle of the formula work? "($B3:$G3=$L$3:$L$6)*$M$3:$M$6)" B3:G3 is obviously the data range, L3:L6 refers to the key, and M3:M6 is the corresponding value. what i don't understand is a) how does the "=" work in this area, why is it used here? b) why must the first half of this formula be multiplied by M3:M6?

i tried dissecting the equation to extrapolate the different parts for future use, but could not get any variation to work.

thanks again for your help