Excel pass/fail which only triggers the fail after 2 values don't meet the requirements

user_13531426 0
Viewed 61

I'm trying to write a pass/fail check that returns a fail only after 2 values in the range fail to pass the check. I've wrote the start of the check however it already returns the "Fail" straight after the first failing value.

For example: Pass/Fail check if all values are above 20.

20
20
20
---
good

20
19
20
---
still good

28
10
19
---
fail (since 2 values fail to meet the required value)

In my sheet 5 values need to be checked which need to be in a certain range defined in a other location (XX1 and XX2 in formula). The formula I used so far is:

=IFS(AND(E37:E41>=MIN(XX1);E37:E41<=MAX(XX2));"Pass";TRUE;"Fail")
excel-formula excel
2 Answers

There are multiple options:

Using COUNTIFS and COUNTA:

=IF(COUNTIFS(E37:E41,">="&XX1,E37:E41,"<="&XX2)>COUNTA(E37:E41)-2,"Pass","Fail")`

If you need to also check that the average falls between XX1 and XX2, then use AND and AVERAGE along with the formula above.

=IF(AND(COUNTIFS(E37:E41,">="&XX1,E37:E41,"<="&XX2)>COUNTA(E37:E41)-2,AVERAGE(E37:E41)>=XX1,AVERAGE(E37:E41)<=XX2),"Pass","Fail")`
user_92458530

3 Conditions IF Statement

  1. AVERAGE(E37:E41)>=XX1
  2. AVERAGE(E37:E41)<=XX2
  3. COUNTIF(E37:E41,"<20")<2 i.e. not more than 1 value is lt 20 (or at least 4 values are gte 20).
=IF(AND(AVERAGE(E37:E41)>=XX1,AVERAGE(E37:E41)<=XX2,COUNTIF(E37:E41,"<20")<2),"Pass","Fail")
user_98140690
Related