# Assigning value to text in formula

#### Matt_B

##### New Member
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.

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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
look at SUMPROD.

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

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

Replies
5
Views
395
Replies
0
Views
574
Replies
5
Views
815
Replies
3
Views
1K
Replies
0
Views
2K

1,206,711
Messages
6,074,465
Members
446,071
Latest member
gaborfreeman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back