IF(AND(OR function

[poikl]

Could you please help me with this function so that it should work?
I would like to check in Cel-D2 if it equals either "AA", "AB" and Cel-E2 does not equal "Pass" or it (Cel-E2) equals N/A# then Cel-F2 should show "Problem" otherwise it (Cel-F2) should show "OK" ?

 

[Von Pookie]

Ok, let's see if we can accurately pin down the criteria for this:

1) If D2 and E2 are blank, then return a blank
2) If D2 is blank and E2=Shipped then return "Not Designated"
3) if D2=AA or D2=AB and E2 is not Pass or E2 is not Shipped or E2=#N/A then return "Problem"
4) Otherwise, return "OK"

Have I missed anything?

 

[poikl]

First, Thank you for helping me
What I need is something simple, you know, like =IF(AND(OR etc. (I don't prefer to use the fancy functions of INDEX etc. to show the following (only 2 corrections, "sorry".

1) If D2 and E2 are blank, then return a blank (this part is simple and i know how to code it.)
2) If D2 is blank and E2=Shipped "or E2 = Pass" <-[please note insert] then return "Not Designated"
3) if D2<>AA or D2<>AB (but anything else except blank), then it doesn't matter what is in E2 (i am not testing for that now) then return blank

4) if D2=AA or D2=AB and E2 is not Pass or E2 is not Shipped or E2=#N/A then return "Problem"
5) Otherwise, return "OK"

 

[Von Pookie]

1) If D2 and E2 are blank, then return a blank (this part is simple and i know how to code it.)

2) If D2 is blank and E2=Shipped "or E2 = Pass" <-[please note insert] then return "Not Designated"

3) if D2<>AA or D2<>AB (but anything else except blank), then it doesn't matter what is in E2 (i am not testing for that now) then return blank

4) if D2=AA or D2=AB and E2 is not Pass or E2 is not Shipped or E2=#N/A then return "Problem"

5) Otherwise, return "OK"

Ok...going with this criteria, I came up with this (although it hasn't really been tested):

=IF(AND($D$2="",$E$2=""),"",IF(AND($D$2="",OR($E$2="Shipped",$E$2="Pass")),"Not Designated",IF(AND(OR($D$2<>"AA",$D$2<>"AB"),$D$2<>""),"",IF(AND(OR($D$2="AA",$D$2="AB"),OR($E$2<>"Pass",$E$2<>"Shipped",ISNA($E$2))),"Problem","OK"))))

 

[poikl]

I was so happy to get your response but I'm sorry but it still not working!
When Cel-D2 has AA and Cel-E2 is N/A#, then F2 shows N/A# instead of "Problem"
Pleeease help

 

[Von Pookie]

Could you post a small sample of your sheet?

 

[Aladin Akyurek]

Or accept the "fancy" solution that needs to be extended to cover your re-newed specifications...

 

[poikl]

# Descrip Status Ref Cont.
1Z121 Gold NOT REQ AA Pass
1A122 Yellow NOT REQ P1
1Z127 Gold 2 NOT REQ AB #N/A
1Z135 Red NOT REQ AA Shipped
Its something like this.
aladyn, it's not that I didn't appreciate your help I really do but I just don't have such knowledge in EXCEL to follow the fancy resolution thats why I asked for something simple
Please help

 

[Von Pookie]

I had some spare time, and figured I'd play with this a little. This is what I came up with:

Book1.xls
	A	B	C	D	E	F
1	#	Descrip	Status	Ref	Cont.
2	1Z121	Gold	NOTREQ	AA	Pass	OK
3	1A122	Yellow	NOTREQ	P1		OK
4	1Z127	Gold2	NOTREQ	AB	#N/A	Problem
5	1Z135	Red	NOTREQ	AA	Shipped	OK
Sheet1

 

[Phil Fluffdry]

Could you please help me with this function so that it should work?
I would like to check in Cel-D2 if it equals either "AA", "AB" and Cel-E2 does not equal "Pass" or it (Cel-E2) equals N/A# then Cel-F2 should show "Problem" otherwise it (Cel-F2) should show "OK" ?

This board has been such a help to me I thought I'd jump in and try to help out. I've seen the other replies and understand why you're looking for something simpler. I think I've got it but I need the answer to two questions before I post it:

1) Is Cell-E2 a formula? I assume it is because of the possible values you listed like "Pass" and N/A# ("#N/A" is a possible result if the formula results in an error). If it is, would you please post the formula? This will help me complete the solution I have.

2) Are you going to use the same forumla in Cell-F2, F3, F4, F5, etc? If yes, I won't use the absolute cell references that another responder used so you'll be able to copy it into as many cells in column F as you like.

Thanks,

Phil

 

[Von Pookie]

Actually, I just used absolute columns; he can drag/fill it down the rows all he wants and it should automatically update