AND/OR Statements

[jazdwit07]

Need help once again.
I am trying to enter an if statement using and/or. what i am trying to
do is find out if G2 has a "yes" and column I2's date is <=1/4/2005
then give me "ok", OR, if
G2 has a "no" and I2's date is <=1/4/2005 then give me "ck".
Is this doable?
thanks.

IF((AND(G2="yes",I2<=date(2005,1,4)),"ok",(IF((OR(G2="no",I2<=date(2005,1,4)),"ck")))

 

[Bob Phillips]

The words seem different to the formula, but is this what you want

=IF(OR(AND(G2="yes",I2<=DATE(2005,1,4)),OR(G2="no",I2<=DATE(2005,1,4))),"ck"
,"")


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I am trying to enter an if statement using and/or.
what i am trying to do is find out if G2 has a "yes"
and column I2's date is <=1/4/2005 then give me
"ok", OR, if G2 has a "no" and I2's date is <=1/4/2005
then give me "ck". Is this doable?
[....]
IF((AND(G2="yes",I2<=date(2005,1,4)),"ok",
(IF((OR(G2="no",I2<=date(2005,1,4)),"ck")))

To match the English description, simply change "OR"
to "AND" -- oh, and change "(AND" to "AND". The
false_value part of the first IF() function is an implicit
"OR". It reads: if "this AND that", then "ok", (or) else
if "this AND that", then "ok".

However, when you have mutually-exclusive conditions
(if G2 can only be "yes" or "no") or when you have a
common condition (I2 <= same date), you can usually
simplify the IF() clauses, often avoiding AND() and OR()
altogether. Also, things will be more readable if you
get rid of redundant parentheses, decide what to do if
neither "ok" nor "ck" condition is true, and always have
all false_values (otherwise, you might see "FASLE").

For example:

a. If G2 can be something other than "yes" and "no"

IF(I2 > DATE(2005,1,4), "",
IF(G2 = "yes", "ok", IF(G2 = "no"), "ck", "")))

b. If G2 can only be "yes" and "no"

IF(I2 > DATE(2005,1,4), "", IF(G2 = "yes", "ok", "ck"))

 

[Max]

Perhaps try also:
=IF(OR(I2={"",0}),"",IF(AND(G2="yes",I2<=DATE(2005,1,4)),"ok",IF(AND(G2="no"
,I2<=DATE(2005,1,4)),"ck","")))

(Presumed you wanted blanks: "" returned as the Value_if_FALSE)

The additional front check on I2: =IF(OR(I2={"",0}),"", ... )
is just a precaution taken to prevent empty cell/zero
from being evaluated as a "valid date" which might give spurious results

 

[jazdwit07]

hmmm....seems to get an error message using that string. by the way,
what do the brackets represent? thanks.

 

[Max]

.. seems to get an error message using that string ...

Not sure why you hit the error,
but here's a quick sample with the formula implemented:
http://cjoint.com/?beezJ3PTwp
jazdwit07_gen.xls

what do the brackets represent?

the curly braces {...} means it's an array

=OR(I2={"",0})
is just a shorter way of writing: =OR(I2="",I2=0)
(both expressions return the same results)

 

[jazdwit07]

hey, that's a pretty neat trick! thanks for visual.

 

[jazdwit07]

hey, that's a neat trick! thanks for the visual.

 

[jazdwit07]

thanks, bob. you're close but i think my explanation was a bit off.
how's this? if (G) = yes, and ( I ) is <=date, then give me Y; and if
(G) = yes and ( I ) is >than date, then give me, CK and finally if (G)
= no, then give me, nothing.
Thank you to all for your input!

 

[jazdwit07]

hi
thanks, bob. you are close, but i think i may have confused you (again)
with what i am trying to do. as i had a chance to study my columns
further, let me try to logically explain. i want to find out that if
column G has a YES and column I has a date that is <=1/4/2005, then,
give me Y; or if G has a YES and column I has a date that is >1/4/2005
then give me CK; but if G has NO then give me, nothing. how's that?

 

[jazdwit07]

hi
thanks, bob. you are close, but i think i may have confused you (again)
with what i am trying to do. as i had a chance to study my columns
further, let me try to logically explain. i want to find out that if
column G has a YES and column I has a date that is <=1/4/2005, then,
give me Y; or if G has a YES and column I has a date that is >1/4/2005
then give me CK; but if G has NO then give me, nothing. how's that?

 

[Max]

You're welcome !

 

[Max]

.. if column G has a YES and column I has a date that is <=1/4/2005,
then, give me Y;
or if G has a YES and column I has a date that is >1/4/2005
then give me CK; but if G has NO then give me, nothing.

On way, think we could try in say, J2:

=IF(OR(I2={"",0},G2="NO"),"",
IF(AND(G2="YES",I2<=DATE(2005,1,4)),"Y",
IF(AND(G2="YES",I2>DATE(2005,1,4)),"CK","")))

 

[Max]

Believe the subtle* difference between "ok" and "ck"
in the OP's description might have been missed as well <g>
*if the difference isn't a typo, that is

 

[Guest]

i think my explanation was a bit off.
if (G) = yes, and ( I ) is <=date, then give me Y;
and if (G) = yes and ( I ) is >than date, then give
me, CK and finally if (G) = no, then give me, nothing.

"A bit off"!? Like night and day! Again, KISS is the answer.
The logic above requires no OR(), AND() or BUT() ;-).

=IF(G1 <> "yes", "", IF(I1 <= DATE(...), "y", "ck")