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

[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")),E44<=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

[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")),E44<=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?

[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:D49,">0.9")),NOT(COUNTIF(D44:D49,"<2")),E44<=2),OR(NOT(COUNTIF(F44:F49,">0.9")),NOT(COUNTIF(F44:F49,"<2")),G44>=2,H44>=4.5)),"Pass","Fail")

[jrdnoland]

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

[Bob Phillips]

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.

[Bob Phillips]

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

[jrdnoland]

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

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