Need help with a formula (OR and AND)

[e119687]

I am trying to obtain a Pass or Fail output in a single column based on two cells in two adjacent columns. This is numerical data. The data in each cell is a single value. Column A is the Pass/Fail output. Column B and C are values 1 and 2.
The value in column B must be within a certain tolerance limit. The value in column C must be within a certain tolerance limit. If either one (B or C) or both is not within tolerance, then the result is Fail. If both are within tolerance, then the result is Pass.

 

[bosco_yip]

Try,

A4, formula copied down:

=IF(((B4<B1)+(B4>B2))*((C4<C1)+(C4>C2)),"Fail","Pass")

 

[joeu2004]

@e119687.... There seems to be something wrong with your English description.

In the Excel file, you write:
If B4 is less than B1 or greater than B2 AND C4 is less than C1 or greater than C2, then this is a "Pass", otherwise it is a "Fail"

The Excel formula to implement that is:
=IF(AND(OR(B4<$B$1, B4>$B$2), OR(C4<$C$1, C4>$C$2)), "pass", "fail")

But the results for that in D4:D7 do not match the expected results in A4:A7.

-----

In contrast, in your posting, you write:
If both are within tolerance, then the result is Pass

I interpret that to mean:
If B4 is between B1 and B2 and C4 is between C1 and C2, return "pass"; otherwise return "false"

The Excel formula to implement that is:
=IF(AND($B$1<=B4, B4<=$B$2, $C$1<=C4, C4<=$C$2), "pass", "fail")

And the results for that in E4:E7 do indeed match A4:A7.

See the image below and the attached file.



-----

 

[joeu2004]

=IF(((B4<B1)+(B4>B2))*((C4<C1)+(C4>C2)),"Fail","Pass")

I believe the success of that formula is an illusion.

It appears to work only by coincidence because you did not use absolute references $B$1, $B$2, $C$1 and $C$2.

IMHO, the correct complementary logic is:

=IF(OR(B4<$B$1, B4>$B$2, C4<$C$1, C4>$C$2), "fail", "pass")

 

[e119687]

Try,

A4, formula copied down:

=IF(((B4<B1)+(B4>B2))*((C4<C1)+(C4>C2)),"Fail","Pass")

View attachment 84095

Thank you very much for this. I added the absolute references as noted by joeu2004, but this still failed to work 100% of the time. I am using the IF(AND solution provided by joeu2004 and it is working great.

 

[e119687]

I believe the success of that formula is an illusion.

It appears to work only by coincidence because you did not use absolute references $B$1, $B$2, $C$1 and $C$2.

IMHO, the correct complementary logic is:

=IF(OR(B4<$B$1, B4>$B$2, C4<$C$1, C4>$C$2), "fail", "pass")

Thankyou very much with this solution.

@e119687.... There seems to be something wrong with your English description.

In the Excel file, you write:
If B4 is less than B1 or greater than B2 AND C4 is less than C1 or greater than C2, then this is a "Pass", otherwise it is a "Fail"

The Excel formula to implement that is:
=IF(AND(OR(B4<$B$1, B4>$B$2), OR(C4<$C$1, C4>$C$2)), "pass", "fail")

But the results for that in D4:D7 do not match the expected results in A4:A7.

-----

In contrast, in your posting, you write:
If both are within tolerance, then the result is Pass

I interpret that to mean:
If B4 is between B1 and B2 and C4 is between C1 and C2, return "pass"; otherwise return "false"

The Excel formula to implement that is:
=IF(AND($B$1<=B4, B4<=$B$2, $C$1<=C4, C4<=$C$2), "pass", "fail")

And the results for that in E4:E7 do indeed match A4:A7.

See the image below and the attached file.

View attachment 84099

-----

Thank you so much for this quick reply. This solution works great!