IF(AND(COUNT one incident between a lower and upper limit

**dmad81** · 06-19-2012, 11:06 PM

Here's my formula challenge . .

I'm using this formula which gives me an undesired "Fail" indication if only one value is less than 10:
=IF(AND(COUNT(D27:D38)>=1,MIN(D27:D38)>=$F$48,MAX(D27:D38)<=$G$48),"Pass","Fail")

Here's what I need . . .

If all of the values are less than 10, I need a “Fail” indication.

If only one of the values in the range is >= 10 AND <=30, I want a “Pass” indication, OR if all of the values in the range are>= 10 AND <=30, I want a “Pass” indication.

Also, if any one value exceeds 30, then I want a “Fail” indication.

D
15.0
17.9
17.9
15.7
11.1
10.4
10.4
20.9
20.9
20.9
20.9
20.9

46 F G
47 NLT NMT
48 10 30

**JBeaucaire** · 06-19-2012, 11:27 PM

That one set of data doesn't really demonstrate the hurdles. Can you provide more including the "strange" ones that are the difficult ones you're dealing with?

Logic in order:
1) If any value > 30 ... FAIL IMMEDIATELY
2) If all the values are < 10 ... FAIL IMMEDIATELY
3) If there are then ANY values between 10 and 30 ... PASS, else FAIL

That's how I'm reading your verbal description.

Truth is, I'm not sure we even need #3, if #1 and #2 don't fail, then I think there's at least one value in the range is in between, so PASS.

=IF(OR(COUNTIF($D$27:$D$38,">30")>0,COUNTIF($D$27:$D$38,"<10")=COUNT($D$27:$D$38)),"Fail","Pass")

**dmad81** · 06-19-2012, 11:52 PM

BTW, how do you avoid the "too many arguments" error when selecting cell ranges? I have some cells that break into the range where the formula does not apply.

**dmad81** · 06-20-2012, 12:01 AM

Your equation worked. A cell break in the range does not adversely affect your formula.

**dmad81** · 06-19-2012, 11:44 PM

The 7.5 in this set gives an undesired Fail. This condition is the only challenge to a correct formula. . I think! I will try your formula next. Thanks.

15.0
17.9
17.9
15.7
11.1
7.5
10.4
20.9
20.9
20.9
20.9
20.9
Fail