Stastics: How to Recode Data

Gabrielle

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

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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

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!

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

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.

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?

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?

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

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

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

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.

Replies
8
Views
418
Replies
1
Views
427
Replies
4
Views
339
Replies
0
Views
280
Replies
5
Views
127

1,217,673
Messages
6,137,917
Members
450,098
Latest member
Ikmal Sabri

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.

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