IF function? to turn around answers in questionaire

[Windav]

Been beating my brow over this one - can't get it to work for me. I need help
from u guys...
I have an answer from a questionaire on a scale of 1 to 7, whole increments.
If 1 is typed it gives a score of 7, 2 --> 6, 3 --> 5, 4 --> 4, 5 --> 3, 6
--> 2 and 7 -->1.

How do I get it working so from one number is returned another?
I've tried for example
J21=2
=OM(J21=1;7) =OM(J21=2;6) =OM(J21=3;5) =OM(J21=4;4) =OM(J21=5;3)
=OM(J21=6;2) =OM(J21=7;1)
but answer: TRUE
not 6 as it should be. Is there another way?

 

[Pete_UK]

Try it this way:

=IF(AND(J21>0,J21<=7),8-J21,"invalid")

Hope this helps.

Pete

 

[Max]

This might suffice here
In say, K21: =IF(OR(J21<1,J21>7),"",8-J21)
Adapt to suit your separator settings (replace comma with semicolon)

Another way would be to use vlookup
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,300 Files:356 Subscribers:53
xdemechanik

 

[Bob Phillips]

=(J21>0)*(J21<8)*(8-J21)