# Need help with a formula (OR and AND)

#### e119687

##### New Member
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.

#### Attachments

• Pass-Fail example.xlsx
16.1 KB · Views: 5
Try,

A4, formula copied down:

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

@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.

-----

#### Attachments

• pass-fail within tolerance.xlsx
19 KB · Views: 1
=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")

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.

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!