Stastics: How to Recode Data

[Gabrielle]

I have been looking everywhere for this. In SPSS, it is very easy to recode data, but how do you do this in Excel? For example, researchers often "reverse" their questions on surveys with Likert scales to eliminate respondent errors. In those questions, you want 1=5, 2=4, 3=3, 4=2, 5=1. Is there a plug-in or some other formula that is out there? Please help! I am a doctoral student trying to calculate thousands of scores on 2 questionnaires.

 

[Aladin Akyurek]

On 2002-10-10 12:14, Gabrielle wrote:
Yes, that is the problem. How do I prevent the error from showing? When I add data, everything is okay, but until then I have the errors.

Use

=IF(LEN(E2),CHOOSE(E2,5,4,3,2,1),"") or

=IF(ISNUMBER(E2),CHOOSE(E2,5,4,3,2,1),"")

instead of just

CHOOSE(E2,5,4,3,2,1)

when converting.
This message was edited by Aladin Akyurek on 2002-10-10 12:21

 

[Benmore]

How do you recode into different variables in Excel? In SPSS I can recode a column of age data into a new column with an age range e.g. 20-24, 25-29, 30-34, etc.

 

[Aladin Akyurek]

How do you recode into different variables in Excel? In SPSS I can recode a column of age data into a new column with an age range e.g. 20-24, 25-29, 30-34, etc.

Care to provide a sample of age values and the associated results after recoding?

 

[Matt_B]

Hi,

I have a similar question and I thought I had worked it out but hadn't.....

I have a list of answers in excel in a single column which represent 4 answer choices from a drop down menu in an online survey. For analysis purposes I wish to assign these with a numerical value with a formula which will create this transformed variable in a new column.

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)

and I wish to give these values of 0, 1, 2 and 3 respectively.

I've seen similar formulas on this forum but couldn't work out how to apply them to this - any idea?

Matt

Additional info
So at the moment my data looks like this (below) and I'd like a list of corresponding numbers:

Rarely or none of the time (less than 1 day)
Rarely or none of the time (less than 1 day)
Occasionally or a moderate amount of time (3-4 days)

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

 

[Aladin Akyurek]

Hi,

I have a similar question and I thought I had worked it out but hadn't.....

I have a list of answers in excel in a single column which represent 4 answer choices from a drop down menu in an online survey. For analysis purposes I wish to assign these with a numerical value with a formula which will create this transformed variable in a new column.

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)

and I wish to give these values of 0, 1, 2 and 3 respectively.

I've seen similar formulas on this forum but couldn't work out how to apply them to this - any idea?

Matt

Additional info
So at the moment my data looks like this (below) and I'd like a list of corresponding numbers:

Rarely or none of the time (less than 1 day)
Rarely or none of the time (less than 1 day)
Occasionally or a moderate amount of time (3-4 days)

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

Enter these items...

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)

in say A2:A5 on a separate sheet. Select A2:A5 and name this range as, say, List.

Now we can invoke a formula like

=IF(E2="","",MATCH(E2,List,0)-1)

in order to assign the appropriate number to the string in E2.

 

[Kerry_J]

I have been looking everywhere for this. In SPSS, it is very easy to recode data, but how do you do this in Excel? For example, researchers often "reverse" their questions on surveys with Likert scales to eliminate respondent errors. In those questions, you want 1=5, 2=4, 3=3, 4=2, 5=1. Is there a plug-in or some other formula that is out there? Please help! I am a doctoral student trying to calculate thousands of scores on 2 questionnaires.

Hi Gabrielle,

A coworker of mine recently shared a way to do this which is so simple it makes you want to smack yourself for not thinking of it:
Have the formula as 6-A1 (if the values you want to reverse the values of the cell in A1, and it has integers from 1-5).
If you want to have some basic error checking you can do so with an If: =IF(AND(ISNUMBER(A1),A1<=5,A1>=1),6-A1,"")