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.

 

[lenze]

Say your scores are in column C, Then you can add this formula to column D

=CHOOSE(C1,5,4,3,2,1) to reverse the order

Copy down column D. Select Column D and copy then Paste Special Values to eliminate the formula in Column D

 

[Gabrielle]

Wow, that is fantastic. THANK YOU. I wish I had found this excellent resource a long time ago, but now it's bookmarked and I'll be sure to visit often. Thanks again!

 

[Aladin Akyurek]

On 2002-10-09 17:19, Gabrielle wrote:
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.

Create a 2-list of correspondences and name it, say, aList.

The following formula would sum 5 scores according the required conversion...

=SUMPRODUCT((D2:H2=$A$1:$A$5)*($B$1:$B$5))

or using the name aList...

=SUMPRODUCT((D2:H2=INDEX(aList,0,1))*(INDEX(aList,0,2)))

See...

aaLikertConversion Gabrielle.xls
	A	B	C	D	E	F	G	H	I	J
1	1	5		Q1	Q2	Q3	Q4	Q5	Sum	Avg
2	2	4		1	2	3	4	2	18	3.6
3	3	3		5	4	3	2	4	18	3.6
4	4	2
5	5	1
6
Sheet2

 

[Gabrielle]

Thank you as well. I tried everything I could to recode data, including researching many relevant websites, and 2 of you have responded with different solutions, both of which are excellent. I appreciate your explanation.

 

[Gabrielle]

Sorry...one more question. Now that I've changed the formulas, I am getting the #VALUE! error. I looked in past discussion forums, and saw that CSE formulas should help, but this still doesn't resolve the errors. When I add data, of course, the errors go away, but since I want to share this scoring calculator with other people, I'd rather the errors not show. Ideas?

 

[Aladin Akyurek]

On 2002-10-10 10:58, Gabrielle wrote:
Sorry...one more question. Now that I've changed the formulas, I am getting the #VALUE! error. I looked in past discussion forums, and saw that CSE formulas should help, but this still doesn't resolve the errors. When I add data, of course, the errors go away, but since I want to share this scoring calculator with other people, I'd rather the errors not show. Ideas?

Care to post the formula that you adjusted to your data?

 

[Gabrielle]

First, I reversed the Likert scale questions for E2 to form AJ2 by following the instructions of
=CHOOSE(E2,5,4,3,2,1)

Then I tallied the questions resulting in subscores with this
=SUM(B2,AJ2,K2,P2,V2,Y2,AQ2,AD2)

 

[Aladin Akyurek]

On 2002-10-10 11:15, Gabrielle wrote:
First, I reversed the Likert scale questions for E2 to form AJ2 by following the instructions of
=CHOOSE(E2,5,4,3,2,1)

Then I tallied the questions resulting in subscores with this
=SUM(B2,AJ2,K2,P2,V2,Y2,AQ2,AD2)

Do some of the cells that you sum have a #VALUE! error?

 

[Gabrielle]

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.