Logical Functions

[saltnsnails]

I must admit. I am horrible at logical functions. I am trying to compose a
formula that will return a value of "1" if number is greater than 1, less
than 4, greater than 949, and less than 991. Here is what I have now but it
is not working: =IF(AND(G7>0,G7<4,G7>949,G7<991),1,0)

Any help would be awesome!

 

[Sandy Mann]

You text says: **if number is greater than 1** but your formula saud greater
than zero. Assuming that you mean >= 1 and <= the other numbers, try

=IF(OR(AND(G7>=1,G7<=4),AND(G7>=949,G7<=991)),1,0)

Note that if you just want zero or 1 then:

=--OR(AND(G7>=1,G7<=4),AND(G7>=949,G7<=991))

Will do that.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Tyro]

If you look at your IF statement you are saying G7 must be greater than 0
AND G7 must be less than 4 AND G7 must be greater than 949 AND G7 must be
less than 991. All of those conditions must be met for your formula to
return 1. G7 cannot be less than 4 and greater than 949 at the same time.
What you want can be done with
=IF(OR(AND(G7>0,G7<4),AND(G7>949,G7<991)),1,0) or
=IF(OR(AND(G7>1,G7<4),AND(G7>949,G7<991)),1,0)
You say the number must be greater than 1 but your formula says 0. So if the
number must be greater than 0, use the first formula, greater than 1, use
the second.

Tyro

 

[Ron Rosenfeld]

I must admit. I am horrible at logical functions. I am trying to compose a
formula that will return a value of "1" if number is greater than 1, less
than 4, greater than 949, and less than 991. Here is what I have now but it
is not working: =IF(AND(G7>0,G7<4,G7>949,G7<991),1,0)

Any help would be awesome!

You need to express what you want more clearly -- then you might find logical
functions easier.

You don't say if you want the number to meet all of the criteria, or any of the
criteria, or perhaps some pairs of the criteria.

There is no way a number can meet all those criteria simultaneously. So if
that's what you want, the formula is: 0

Also, your statement and formula disagree as to what you want to do with regard
to the number 1. Your statement is "number is greater than 1", whereas your
formula reads G7>0.

If you want a 1 if the number meets any one of those criteria, then you need
the OR function which will return a TRUE/FALSE if the number is:

greater than 1 OR
less than 4 OR
greater than 949 OR
less than 991

In other words, if the number meets and one of the criteria.

=OR(n>1,n<4,n>949,n<991)

IF you want a 1 or 0, you can just convert TRUE/FALSE to a number:

=--OR(n>1,n<4,n>949,n<991)

or use an IF statement:

=IF(OR(n>1,n<4,n>949,n<991),1,0)

IF you want something else, you need to be clear. And I believe that being
clear in your own head, will help with your difficulties with logical
functions.

--ron

 

[David Biddulph]

It is's less than 4 it can't also be greater than 949, so you'll never
satisfy the condition. What are you really trying to do?

 

[David Biddulph]

And of course if you look at Ron's =OR(n>1,n<4,n>949,n<991) you'll realise
that if you've tested for n>1, there's no point in checking for n>949, and
similarly if you've tested for n<991 there's no point in testing for n<4,
hence you can simplify to =OR(n>1,n<991), but then look further so if you
don't satisfy n>1 you must satisfy n<=1 and must therefore satisfy n<991, so
the answer to the OR function is always TRUE.

Hence, as Ron says, you need to think first about what it is that you're
trying to do. When you've sorted out precisely what the question is,
getting Excel to answer it is usually the easy part.