How do I create formula to reverse code in excel?

[Hair loss by excel]

Hi
Have questionnaire and trying to automate the report.

Some questions are reversed (in the negative) so if the respondent enters a
'1' = strongly agree, I need that to be caluated as a 5 (5=strongly
disagree).

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

Respondents enter answers on a front sheet then the data is collected on a
second sheet. This second sheet adds up responses to various questions to
produce a scale.

So, e.g. if they enter a '1' on the front sheet I need that to show as a 5
on the second sheet.

Can anyone help? Please help - I'm pulling my hair out.
Maria - New Zealand

 

[Paul Corrado]

=VLOOKUP("answer",{1,5;2,4;3,3;4,2;5,1},2,FALSE)

 

[Fred Smith]

With the current answer in a1, use:

=6-a1

to reverse it.

 

[Hair loss by excel]

Hi Paul

Still not sure I've got it. What do you mean by "answer" 2,FALSE

I hav etried just pasting the formula you gave in to the spreedsheat - but
it comes up as NA.

 

[Myrna Larson]

Paul has just used a literal array instead of a reference (like A1:B5) to a
lookup table. The literal array contains only numbers.

Paul intended "answer" as a placeholder for the actual answer to the question
(or a reference to a cell containing the answer). The 2 and 0 mean exactly the
same thing they would if you have used a reference like A1:B5 for the table.