• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

84099

-----
 

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")
 
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!
 
Back
Top