AND/OR Statements

J

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

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

joeu2004

[email protected] said:
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")))
Click to expand...

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

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
 
J

jazdwit07

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

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!
 
J

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?
 
J

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?
 
J

joeu2004

[email protected] said:
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.
Click to expand...

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