Stastics: How to Recode Data

Gabrielle

New Member
Joined
Oct 8, 2002
Messages
21
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.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

New Member
Joined
Oct 8, 2002
Messages
21
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
ABCDEFGHIJ
115Q1Q2Q3Q4Q5SumAvg
22412342183.6
33354324183.6
442
551
6
Sheet2
 

Gabrielle

New Member
Joined
Oct 8, 2002
Messages
21

ADVERTISEMENT

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

New Member
Joined
Oct 8, 2002
Messages
21
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

New Member
Joined
Oct 8, 2002
Messages
21
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Oct 8, 2002
Messages
21
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,952
Members
413,954
Latest member
mrsandy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top