# Thread: Formulas: If(AND( , OR(NOT(COUNTIF now working correctly

1. ## Formulas: If(AND( , OR(NOT(COUNTIF now working correctly

I'm trying to get this formula to work correctly, not sue what I'm missing? Perhaps a fresh set of eyes will help.

Here is the formula commented out, have also attached a spreadsheet.

'=IF(AND(C44<=2,OR(NOT(COUNTIF(D44:49,">0.9")),NOT(COUNTIF(D44:49,"<2")),E4 4<=2,OR(NOT(COUNTIF(F44:F49,">0.9")),NOT(COUNTIF(F44:F49,"<2")),G44>=2,H44> =4.5))),"Pass","Fail")

The formula seems correct but if a value in one of the ranges is out of the bounds set the formula is incorrect. Note the formula looks like it picked up a space in this context.

Thanks,
Jeff  Reply With Quote

2. Originally Posted by jrdnoland I'm trying to get this formula to work correctly, not sue what I'm missing? Perhaps a fresh set of eyes will help.

Here is the formula commented out, have also attached a spreadsheet.

'=IF(AND(C44<=2,OR(NOT(COUNTIF(D44:49,">0.9")),NOT(COUNTIF(D44:49,"<2")),E4 4<=2,OR(NOT(COUNTIF(F44:F49,">0.9")),NOT(COUNTIF(F44:F49,"<2")),G44>=2,H44> =4.5))),"Pass","Fail")

The formula seems correct but if a value in one of the ranges is out of the bounds set the formula is incorrect. Note the formula looks like it picked up a space in this context.

Thanks,
Jeff
Can someone at least point me in the right direction?  Reply With Quote

3. I cannot see what the problem that you allude to is, but should it be

=IF(AND(C44<=2,OR(NOT(COUNTIF(D44:D49,">0.9")),NOT(COUNTIF(D44:D49,"<2")),E 44<=2),OR(NOT(COUNTIF(F44:F49,">0.9")),NOT(COUNTIF(F44:F49,"<2")),G44>=2,H4 4>=4.5)),"Pass","Fail")  Reply With Quote

4. Originally Posted by Bob Phillips I cannot see what the problem that you allude to is, but should it be

=IF(AND(C44<=2,OR(NOT(COUNTIF(D44 49,">0.9")),NOT(COUNTIF(D44 49,"<2")),E44<=2),OR(NOT(COUNTIF(F44:F49,">0.9")),NOT(COUNTIF(F44:F49,"<2") ),G44>=2,H44>=4.5)),"Pass","Fail")
Thanks for looking Bob, I tried your formula and it's still not working. Looking at the spreadsheet D46 is > 0.9 so it should return False as a final condition.

These numbers are being generated in an analytical HPLC.

To be a Pass, all of these conditions must be true:

%RSD Response must be between <= 2.0
Peak Tailing must be between 0.9 and 2.0
%RSD Response must be <= 2.0
Average Resolution of Isopropanol must be >= 3.0
Average Resolution of n-propanol must be >= 4.5

Looking at the sheet you will see these in rows 42 and 43. This sheet is an exact replica of the one our technicians use.  Reply With Quote

5. Originally Posted by jrdnoland Thanks for looking Bob, I tried your formula and it's still not working. Looking at the spreadsheet D46 is > 0.9 so it should return False as a final condition.
Yes, all of D44:D49 are greater than 0.9, which is what your NOT(COUNTIF( condition is testing for, but that is part of an overall OR test which is true if any is true and E44 is less than 2 so the OR condition is true, hence pass.  Reply With Quote

6. Looking at your rules statement, is this what you want

=IF(AND(C44<2,COUNTIFS(D44:D49,">=0.9",D44:D49,"<=2")=6,E44<2,COUNTIFS(F44: F49,">=0.9",F44:F49,"<=2")=6,G44>=2,H44>=4.5),"Pass","Fail")  Reply With Quote

7. Originally Posted by Bob Phillips Looking at your rules statement, is this what you want

=IF(AND(C44<2,COUNTIFS(D44 49,">=0.9",D44 49,"<=2")=6,E44<2,COUNTIFS(F44:F49,">=0.9",F44:F49,"<=2")=6,G44>=2,H44>=4.5 ),"Pass","Fail")
Bob - Thank you; I got it to work using your formula. Here is the final formula:

`=IF(AND(C44<2,COUNTIFS(D44:D49,">=0.9",D44:D49,"<=2")=6,E44<2,COUNTIFS(F44:F49,">=0.9",F44:F49,"<=2")=6,G44>=2,H44>=4.5 ),"Pass","Fail")`  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•