Forum Discussion

Teden2020's avatar
Teden2020
Copper Contributor
Feb 10, 2020
Solved

Help with IF this but IF that...

My columns are as below. I want it to give me a YES in column 3 if the first column is "I" and the second column is >4 and <7. Success with =IF(AND(C2="I",D2>4,D2<7),"YES","NO").   BUT some of the ...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 10, 2020

    Teden2020 

    As variant

    =IF(OR(
     AND(C2="I",D2>4,D2<7),
     AND(C2="A",D2>7,D2<10)
   ),"YES","NO")
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 10, 2020

Teden2020 

As variant

=IF(OR(
     AND(C2="I",D2>4,D2<7),
     AND(C2="A",D2>7,D2<10)
   ),"YES","NO")
Teden2020's avatar
Teden2020
Copper Contributor
Feb 10, 2020

SergeiBaklan Can I pick your brains further? What if didn't want just a simple NO? For example for an "I" the range should be within 4 and 7. Is there way you can get a "YES" for that range, a "LOW" if the number is too low and a "HIGH" if the number is too high? Getting too complicated?

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 10, 2020

    Teden2020 

    That could be

    =IF(C2="I",IF(D2>7,"High",IF(D2>4,"Yes","Low")),"No")

    or you'd like to combine with C2="A" in the same formula?

    • Teden2020's avatar
      Teden2020
      Copper Contributor
      Feb 10, 2020

      SergeiBaklan Excellent. Actually yes and is now the time to tell you there's an "F" too?!

       

      A should be  >7  <10

      I should be   >4   <7

      F should be  >0   <4

       

      Thanks for this!

       

      Tim

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Feb 10, 2020

        Teden2020 

        Tim, with this it's better not to hardcode all combinations, but create helper range somewhere, even on another sheet (you could hide it) like this

        and give names to all 3 columns of this range. Here are ParName, ParMin and ParMax. Formula in E2 will be

        =IFNA(
  IF(D2>INDEX(ParMax, MATCH(C2,ParName,0)),
     "High",
     IF(D2>INDEX(ParMin,MATCH(C2,ParName,0)),
        "Yes","Low")
   ),
"No")

        and drag it down.

Resources