Reverse scoring for likert scale

[jmcateer]

Hello,

I am analyzing data from a survey (1= Not Interested, 5= Very Interested) in which some questions have reverse scoring (5=Not Interested, 1= Very Interested). I am seeking to have a formula for a column to automatically reverse the scoring. How do I do this?

Best,
John

 

[DRSteele]

Welcome to the forum.

If you are up to date with your software, in Excel 365 you can use the cool new function SWITCH, like this (where one of your 1 through 5's is in A1):

=SWITCH(A1,1,5,2,4,3,3,4,2,5,1)

And if you're still using old software, this can do it:

=MAX(IF(A1={1;2;3;4;5},{5;4;3;2;1}))

 

[Eric W]

You can also use:

=6-A1

Do you need some kind of check in the formula to decide which rows need to be reversed, and which ones don't?

 

[DRSteele]

Doi! Man! did I ever guild the lily there.

 

[jmcateer]

Welcome to the forum.

If you are up to date with your software, in Excel 365 you can use the cool new function SWITCH, like this (where one of your 1 through 5's is in A1):

=SWITCH(A1,1,5,2,4,3,3,4,2,5,1)

And if you're still using old software, this can do it:

=MAX(IF(A1={1;2;3;4;5},{5;4;3;2;1}))

Thank you very much Dr. Steele,

The numbers are actually in Q3-Q7, T3-T7. In this case would the formula look like: =MAX(IF(Q3={1;2;3;4;5},{5;4;3;2;1})).
I tried this and it did not work.