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]

Let me try to draw this out. I don't know how to put a picture of Excel in my post so I can't present the spreadsheet that way.

I'll use the image you have in your post to relate the same info mine has.
Let's assume the following:
Column labels:
A = Status
B - E = Standards 1, 2, 3, 4
F = Out Of Sample

So we're working on the status column for the formula.
The formula is checking column F to see if the audit is Out of Sample or not (Yes or No).
The idea is for the formula to see if all the standards are met, then return either OK, FIND, or OB (observation) in column A depending on if the audit is in sample or not.

Does this help?

 

[XLGibbs]

I reread your criteria again.....it seems this produces the desired results.

does this get you there?

I went on the fact that First there had to be 5 entries , then if all of the are not NO, then OK, then the comparitve criteria following that.

Counting the Yes,or No's is easier than all of the OR statements nested in...hope this gets you closer anyway.

=IF(COUNTA(A5:E5)=5,IF(COUNTIF(A5:E5,"no")=0,"OK",IF(AND(COUNTIF(A5:D5,"no")>0,E5="Yes"),"OB",IF(AND(E5="No",COUNTIF(A5:D5,"No")=0),"OK","FIND"))),"")

Book1
	A	B	C	D	E	F
1	no	no	no	no	no	FIND
2	yes	no	no	no	yes	OB
3	no	no	yes	no	no	FIND
4	yes	yes	yes	yes	no	OK
5	no	no	no	no	Yes	OB
Sheet1

 

[Caleab]

can someone tell me how to post a picure of my excel in here so that we can get this worked out?

 

[XLGibbs]

Downloand the HTML maker..

http://www.mrexcel.com/board2/viewtopic.php?t=92622

I am sure seeing it will clear it up..although my last solution had to be pretty close...although I posted it before your last statements...

 

[Caleab]

ok, getting
Error Number:=91
Error Desc:=Object Variable or With Block variable not set
You have to manually set a reference to the [VBIDE = VBE6EXT.olb]

Haven't been able to find out how to remedy that so putting a picture of my sheet can't happen just yet.

What else can I give to help out here? I almost am thinkng that it's being blown out a little more than necessary.

Gibbs - I think your last could be close, however it only needs to check the 4 columns that are the standards to decide if it's an OK or the negative alternative (OB or FIND depending on the last column's Y or N.)
It's very possible that I'm not reading the formula right and not converting it to my sheet correctly.

In my sheet, the columns are set as:
Column F - Status (OK, OB, or FIND)
Column G:J - the 4 standards (yes or no)
Column K - Out of Sample (yes or no)

in your test sheet, how did you have the columns labeled?

 

[XLGibbs]

My formula looks only in the first 4 of 5 to evaluate their contents, but only where all 5 cells have "something"...

If conditions are met for the first 4, then it looks to the 5th for determining results....I thought I had it though...without seeing your sheet..hard to say.

 

[Caleab]

Gibbs, could you label your columns for me?

I think part of the reason it's not workng for me is that I don't know which columns are which, except for the Status column because it's OK, FIND, etc.

 

[XLGibbs]

If you look at the formula in column F...

A:D are the status columns, Column E is the final Yes/No column depending on the evaluation of the first four...look close at which parts of the formula have A:D and which have A:E...