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

[xpost][/xpost]

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.

Cross posted on vbax http://www.vbaexpress.com/foru…TIF-now-working-correctly

I have not gotten any answers from vba express yet.

Thanks,

Jeff

Quote from jrdnoland

[xpost][/xpost]

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.

Cross posted on vbax http://www.vbaexpress.com/foru…TIF-now-working-correctly

I have not gotten any answers from vba express yet.

Thanks,

Jeff

Providing a little more information:

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.

Display More