Need help with complex IF-THEN fomulas/functions

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.....

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")))


--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Connie" <[email protected]> wrote in message
news:[email protected]...
>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.....

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!

"JulieD" wrote:

> 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")))
>
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "Connie" <[email protected]> wrote in message
> news:[email protected]...
> >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.....
>
>
>

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:
....
>>=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")))
....

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";""})

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!

"Harlan Grove" wrote:

> 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:
> ....
> >>=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")))
> ....
>
> 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";""})
>
>