Need help with complex IF-THEN fomulas/functions

C

Connie

I am setting up a datasheet needing multi if/then statements. It's a
questionaire with 7 questions and points for each answer given. I already
have it set up for the total of these points, but now I need the answers
printed into one cell.
IF(b2>5<9, "this answer printed")
IF(b2>8<13, "then this is the correct answer")
IF(b2>12<15,"now this is the correct answer)
all else gives "this answer"

I have found that I can put each of these into their own cells (non
printing) and THEN have the desired cell find the one cell with something
printed, but what a chore. But is this my only option? Considered look up
charts if I have to. Never done one, though, so again another learning
experience.

Thanks for any help. This is fun, but frustrating.....
 
J

JulieD

Hi Connie

how about
=IF(OR(B2<=5,B2>=15),"this answer",IF(B2<9,"this answer
printed",IF(B2<13,"then this is the correct answer","now this is the correct
answer")))
 
C

Connie

WOW! Makes sense. But I'm getting an error message and can't see for looking.
Here's my formula:
=IF(OR(l2<9,l2>6),"Conservative",IF(l2>8,L2<12),"Moderately
Conservative",IF(l2>11,l2<17),"Moderate",IF(l2>16,l2<20),"Moderately
Aggressive",IF(l2>19,l2<22),"Aggressive","")))))

It doesn't seem to like my Conservative" in my second if-then. Does it think
it's a duplicate of the first answer?

If it doesn't answer any of my criteria, I want it to print nothing.

Thanks again!
 
H

Harlan Grove

Connie wrote...
WOW! Makes sense. But I'm getting an error message and can't see for looking.
Here's my formula:
=IF(OR(l2<9,l2>6),"Conservative",IF(l2>8,L2<12),"Moderately
Conservative",IF(l2>11,l2<17),"Moderate",IF(l2>16,l2<20),"Moderately
Aggressive",IF(l2>19,l2<22),"Aggressive","")))))

It doesn't seem to like my Conservative" in my second if-then. Does it think
it's a duplicate of the first answer? ....
"JulieD" wrote: ....
Click to expand...
....

Julie's formula was first checking for values *outside* a range, B2 <=
5 or B2 >= 15. You're checking for values within a range, in which case
you want to use AND rather than OR - all numbers are less than 9 or
greater than 6. Also, you'd need to use AND in each of the other IF
calls.

However, you'd be better off with a lookup.

=LOOKUP(L2,{-1E300;6.00000000000001;9;11;16;19;22},
{"";"Conservative";"Moderately Conservative";"Moderate";
"Moderately Aggressive";"Aggressive";""})
 
C

Connie

IT WORKED! Thank you!!! I only added the AND to each statement.

But you've peaked my interest in lookups. Let's see if my manual does a
better job at explaining these!
 
Top