inverting data

[Guest]

Please help, I'm stuck! I conducted a survey where respondants each gave
answers to 30 questions using a Likert scale i.e. they answered either
1,2,3,4 or 5 to each question. The data is in the following form:
Question #
q1 q2 q3 q4 q5
1 2 4 2 1 2
2 4 1 2 1 4
3 4 4 2 1 4
4 2 3 2 2 5
5 2 3 4 5 3

My problem is that half of the questions were asked in a negative direction
to avoid possible response bias. For the answers to these questions, I need
to reverse the answers. If the repondant answered 1, I want to record 5, if 2
then 4, if 3 then 3, if 4 then 2, if 5 then 1. Is there any easy way to do
this in Excel for a 30x93matrix of data?
I'd appreciate some help, I can't get my head around it at the moment!

Regards,
Andrew

 

[Gord Dibben]

One method.

=LOOKUP(A1,{1,2,3,4,5},{5,4,3,2,1})

Drag/copy across and down to get your new matrix as a reverse of the current
one.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

How do you know which ones to convert?

If they're all in a nice contiguous range (say Question 16 to question 30), you
could:

put 6 in an empty cell
edit|copy
select the range to convert
edit|paste special|check Subtract

Then put -1 in that formerly empty cell
edit|copy
select the same range to convert
edit|paste special|check multiply

You could also just use helper columns of cells and use a formula like:
=6-A2
and drag down the helper column.