Large IF statement?

[Caleab]

This shouldn't be that hard, but I can't seem to land it correctly.

I have columns A-E.
Column A is where we are writing the formula.
The formula needs to check Columns B,C,D for a "YES" or "NO" value.
I need it basically to check to see if B,C,D have a NO, if not AND D="YES", put a certain value in the A column (in this case, make it "FIND")

I'll also use this to have it check for different variations of this.

Let me know if you need more info.


Thanks.

 

[Caleab]

Oops... i forgot part of what I needed :P

The other part is if K3=Yes
Same conditions should be looked for, however the value would be "OB" if K3=Yes

Sorry, I got to narrow minded on trying to get the first part working.

 

[fairwinds]

Try:

=IF(COUNTA(G3:K3)=5,IF(K3="YES","OB",IF(AND(G3<>"Yes",H3<>"Yes",I3<>"Yes",J3<>"Yes",K3="No"),"FIND","OK")),"")

if this is not what you want, you must list all conditions and how they correspond to each other in order to give a clearer picture.

 

[XLGibbs]

removed for ugliness

 

[fairwinds]

=IF(INDEX(G3:K3,1) = {"yes","yes","yes","yes","yes"},"OB","Find")

If any of the 5 values are No, it returns "find", if all 5 are yes then "OB"

...is that closer?

How do you mean? Did you test this before posting it?

 

[XLGibbs]

Wierd, yes...it worked when I ran through it...but now testing it it returns OB for any combination of text...my bad....I thought that seemed to easy.. Disregard that one!

But I swear it worked (or some variation of that did!) before I posted...maybe I will figure out again with the right combination...ugh.

Doh!

 

[Caleab]

I'm about to test Fairwinds' solution.

I'm sorry I've not been clear about what I needed. The original formula that Fairwinds provided worked great for when K3=No.

The other side of it is when K3=Yes. (K3 is identifying wether the audit is Out of Scope of the audit. G3:I3 are the standards. So the Yes or No is identifying if that audit met the standard.

So basically the formula needs to know wether K3 = Yes or No.
Based on that, do one of the following:
When K3=No and any one of G3:I3="No" put "FIND"
When K3=No and G3:I3="Yes" put "OK"
or
When K3=Yes and any one of G3:I3="No" put "OB"
When K3=Yes and G3:I3="Yes" put "OK"


So the only differences are the value of K3 and what it will place in the cell when one of the standards is not met (failed audit).

Does this help?

 

[Caleab]

Ok, this formula didn't do it. In trying to decipher exactly what it was doing, I didn't see how it would, but I'm just trying to learn the way to formulate what I need.

=IF(COUNTA(G3:K3)=5,IF(K3="YES","OB",IF(AND(G3<>"Yes",H3<>"Yes",I3<>"Yes",J3<>"Yes",K3="No"),"FIND","OK")),"")

Does it make it easier to say:
I need to combine these two formulas in the same cell. (I don't think so, because it's focused on the wrong main cell, but that's my ignorance =)

=IF(COUNTA(G2:K2)=5,IF(AND(G2<>"Yes",H2<>"Yes",I2<>"Yes",J2<>"Yes",K2="No"),"FIND","OK"),"")

=IF(COUNTA(G2:K2)=5,IF(AND(G2<>"Yes",H2<>"Yes",I2<>"Yes",J2<>"Yes",K2="Yes"),"OB","OK"),"")

 

[XLGibbs]

Try this one, adjust the cell references...my test was in row 5.

nothing in any one of 5 cells returns ""

any yes in first 4 columns returns ""

yes in 5th column returns "OB"

no in 5th column where all 5 have reply returns "find"


=IF(COUNTA(G5:K5)=5,IF(COUNTIF(G5:J5,"yes")=0,IF(K5="yes","OB",IF(K5="No","Find","")),""),"")

 

[Caleab]

Hmm that doesn't seem to fit the need either.

By reading the definitions you wrote it probably works the way yer saying but it's coming from the wrong angle.

nothing in any one of 5 cells returns ""

any yes in first 4 columns returns ""

yes in 5th column returns "OB"

no in 5th column where all 5 have reply returns "find"

This would work if nothing in the first 4 cells returns ""
Any No in the first 4 columns returns FIND if last column = No
Any No in the first 4 columns returns OB if last column = Yes

The goal is to have the cell return either OK if all Yes (regardless of last column) and either OB or FIND if there is a NO in the first 4 columns depending on if the last column is Yes or No.

 

[XLGibbs]

Are there choices other than yes or no? It works as below..the line with no yes in first 4 columns (but something), and if any are blank or yes it returns nothing.

If all 4 of first 4 columns have something it evaluates either Yes, or No...I thought this was what you needed...only returns a result of all 5 values are populated and the 5th is either yes or no...

Book1
	A	B	C	D	E	F
1	ha	ha h	ha	had	no	Find
2	no	no	no	yes	yes
3	no	no	no	no	no	Find
4	yes		no	no	no
5	no	no	no	no	Yes	OB
Sheet1