# 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

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?

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")

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(D4449,">0.9")),NOT(COUNTIF(D4449,"<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.

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.

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")

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

=IF(AND(C44<2,COUNTIFS(D4449,">=0.9",D4449,"<=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")`

#### Posting Permissions

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