Conditional Formula - How to Ignore Blank Cells

[lkmw]

I have a datasheet that contains customer dimensions and a column for the min and max allowed. Inspectors then enter their measurements which can be anywhere from 1 to 7 datapoints per dimension (quantity required varies by customer). I want to have a final column that compares each of the datapoints entered to the min and max values and indicates "OK" if they all are in tolerance, or "NOT OK" if any one or more is below min or above max. I am avoiding the macro route just because I am a novice and others here would not be able to interpret or fix either. So, I started testing a forula which at this point just has conditions for datapoints below the min. It works fine if I have data in all 7 cells. But, if I have less than 7 datapoints, it returns "NOT OK" due to the blank cells. How can I get it to ignore blanks?

Thanks in advance for the help.

0297_LW.xls
	B	C	D	E	F	G	H	I	J	K	L
3	SPECIFICATION	MINIMUM	MAXIMUM	SUPPLIER MEASUREMENT RESULTS							RESULTS
4	.100 +/- .005	0.095	0.105	0.100	0.101	0.102	0.095	0.1015	0.096	0.097	OK
5	.095/.105	0.095	0.105	0.100							NOT OK
Sheet1

 

[NBVC]

Try:

=IF(OR(MIN(E4:K4)<C4,MAX(E4:K4)>D4),"Not Ok","Ok")

copied down

 

[Greg Truby]

=IF(OR(MIN(E4:K4)<C4,MAX(E4:K4)>D4),"Not ","") & "OK"

^edit DANG IT! Too slow! On the other hand, a very nice solution, NBVC _/edit

 

[lkmw]

Clever....thanks to both of you.

 

[lkmw]

Ok...I have NBVC's formula in my spreadsheet and it works fine. Except, now my issue is that I created a form that is an entire page long since the number of criteria we check varies from part to part. So, I copied the formula to all lines of the spreadsheet. Let's say there were only two lines of data for a particular part like my original post. I know get "OK" in lines 3 through the bottom of the sheet even though there is no data. How do I still handle blanks per my previous post, but have the "Results" cell stay blank if all 7 of the 'supplier measurement results' cells in a particlar row are blank?

0297_LW.xls
	A	B	C	D	E	F	G	H	I	J	K	L
6	ITEM	SPECIFICATION	MIN.	MAX.	SUPPLIERMEASUREMENTRESULTS							RESULTS
7	A	.100+/-.005	0.095	0.105	0.100	0.101	0.102	0.095	0.1015	0.096	0.097	Ok
8	B	.095/.105	0.095	0.105	0.100	0.095						Ok
9												Ok
10												Ok
11												Ok
12												Ok
13												Ok
14												Ok
15												Ok
16												Ok
Sheet1

Thanks

 

[Greg Truby]

Yes, we could give you the answer, but then you won't learn anything... What do you think you need to do? Hint: It will involve the <s>IsBlank()</s> Sum() function.

 

[lkmw]

I give up....searched through Excel's help on Sum function but still do not get it.

 

[NBVC]

How about?

=IF(SUM(E7:K7)=0,"",IF(OR(MIN(E7:K7)<C7,MAX(E7:K7)>D7),"Not Ok","Ok"))

or


=IF(COUNTBLANK(E7:K7)=7,"",IF(OR(MIN(E7:K7)<C7,MAX(E7:K7)>D7),"Not Ok","Ok"))

 

[lkmw]

Thank you, NBVC. I used the IF(SUM) part of the latest formula you suggested with the latter part of your original formula so that I get min and max and it seems to work fine....even when I go back and delete results entries from a row using various methods. A number of folks will be using the spreadsheet so it's always fun seeing what 'creative' ways they come up with for doing things.