MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Please Help on IF AND OR logic

  Asked By: Ernesta    Date: Aug 20    Category: MS Office    Views: 1516

I am trying to determine if a server is a good candidate for a
migration with the following code. This works just fine.
=IF(AND(AE25<=1,AF25<=4000,AN25<75),"Candidate","Not Candidate")
The problems occurs when I try to add an OR clause to the logic to
account for the dead collections.
I know the OR syntax is off because it is reading what I was
expecting the value if true and value if false results as additional
logical parameters.
Also I would like to show all three states if possible: Candidate, Not
Candidate and Check Collection
=IF(AND(AE2<=1,AF2<=4000,AN2<75),"Candidate",IF((OR(AD2="dead", AD2,"
Not Candidate"))))



6 Answers Found

Answer #1    Answered By: Aaminah Khan     Answered On: Aug 20

from the looks of things, you're not  really using "OR" at all.
Let's say you have:
AE2 = 1
AF2 = 4000
AN2 = 50
then your "AND(AE2< =1,AF2<=4000, AN2<75)" results  in True,
so returns "Candidate".

But if, say, AE2 = 2
then your "AND" returns FALSE, so then evaluates:
IF((OR(AD2= "dead", AD2," Not Candidate")) )
but if you look closely, the "OR" construct is incomplete.
"OR" should be like: "OR(expression,expression,expression,...)
each expression testing for true/false, but yours is testing the
CONTENTS of AD2 and the string "Not Candidate"..
from what I see, you're really simply using a nested if.

Try this:
=IF(AND(AE2<=1,AF2<=4000,AN2<75)," Candidate",
IF(AD2="dead",AD2," Not Candidate"))
(watch the wrap-around)

Answer #2    Answered By: Anne Powell     Answered On: Aug 20

Thanks for the reply, but it does not  address my problem. When I
pasted your formula on a row where the server's OS is listed as "
dead" the results  returned candidate not dead as expected.
Perhaps I am not clear in my explanation so bear with me as I try
this again.
Cell AD2 should contains the OS, but if we could not collect data
we have listed one of two reasons : Permission Denied or DEAD. If
either of these conditions are present then (AE2,AF2 and AN2 will
contain zeros . Once the first if statement is evaluated against the
zeros the result will always show  as Candidate.
I need to account for the absent collection as well as determine  if
the server  meets the other criterion. Which is why I wanted to
incorporate the OR argument as well. As I had stated in my
original mail, the way I formatted the OR argument was terribly
wrong ,but I didn't know how to incorporate it with the other
arguments and would like some guidance ( pretty please, with sugar
on top).
In reading  your reply I understand it to mean That I must have
more than One logical parameter with an OR argument.
Using the first formula >>>>> =IF(AND
(AE2<=2,AF2<=4000,AN2<150),"Candidate","Not Candidate")
What do I need to add  to test for Permission Denied or DEAD in cell
AD2 then return "some text" to show that the collection failed,
but if the collection did not fail then evaluate
AE2<=2,AF2<=4000,AN2<150 it this evaluates to true  then candidate
if any evaluates to false  then not candidate.
Should this be a if then else statement instead.

Answer #3    Answered By: Tate Thompson     Answered On: Aug 20

Does this formula do what you want?

=IF(OR(AD2="DEAD",AD2="Permission Denied"),"Not
Collected",IF(AND(AE2<=1,AF2<=4000,AN2<75),"Candidate","Not Candidate"))

Answer #4    Answered By: Charlie Evans     Answered On: Aug 20

Yes,that works  perfectly.....................

Answer #5    Answered By: Jawna Mohammad     Answered On: Aug 20

the problem is with the decision hierarchy.
The FIRST decision to make is whether it is "dead" or "permission denied".
THEN, decide if the other criteria is met.
the formula Hutch supplied should work.

Answer #6    Answered By: Venkat Rulez     Answered On: Aug 20

Yes, this takes me where I need to be.
I sincerely appreciate the guidance. Thank you both.

Didn't find what you were looking for? Find more on Please Help on IF AND OR logic Or get search suggestion and latest updates.