Consulting

Results 1 to 7 of 7

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

  1. #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
    Attached Files Attached Files
    Last edited by jrdnoland; 06-01-2020 at 04:18 AM.

  2. #2
    Quote Originally Posted by jrdnoland View Post
    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. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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")
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Quote Originally Posted by Bob Phillips View Post
    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. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by jrdnoland View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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")
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Quote Originally Posted by Bob Phillips View Post
    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
  •