Assigning value to text in formula

[Matt_B]

Hello,

I was wondering if anyone could help with this issue. I have results from a questionnaire containing 20 questions. For each of the questions the possible answers are:

Rarely or none of the time (less than 1 day)
Some or a little of the time (1-2 days)
Occasionally or a moderate amount of time (3-4 days)
All of the time (5-7 days)

I need to assign these values 0, 1, 2 and 3 respectively. Apart from four questions which have reverse scoring so would be the other way around.

Each participant's answers occupy cells
FW to GF and GH to GQ

I'd like to SUM to recoded cells. I realise that I could find and replace but I need to keep the raw data in its original form.

I've used LOOKUP previously to reverse score but this has the text factor in as well.

I hope that makes sense and I wondered if anyone can suggest what to do?

(Excel 2010)

Matt

 

[BrianExcel]

look at SUMPROD.

I think that is in the direction you are looking for...

 

[Matt_B]

thanks for getting back to me. I've been trying the following formula which I was convinced would turn my text into numbers but having some trouble in that any response gives the answer '3'. Does this formula look sensible?

=LOOKUP(FW2,{" Rarely or none of the time (less than 1 day)"," Some or a little of the time (1-2 days)"," Occasionally or a moderate amount of time (3-4 days)"," All of the time (5-7 days)"},{"0","1","2","3"})

I was going to apply this to 8 columns and the output I want is a sum of all 8 so this will be a ridiculously long formula.


If there's a better way to do this I'd love it!

Matt