Forum Discussion

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 first column data contains "A" for which I would the second column to be >7 and <10 for a YES.

How do I combine both IF statements so that I can just fill down? HELP PLEASE!

Target Art AWOL	Art FFT (Y7)
I	5.5	YES
A	7.2
I	4.8
I	4.5
I	5.2
I	5.8
I	6.2
I	7.2
I	5.5
A	7.5
I	6.2
I	5.8
I	7.2
I	7.5
A	5.8

As variant

=IF(OR(
     AND(C2="I",D2>4,D2<7),
     AND(C2="A",D2>7,D2<10)
   ),"YES","NO")

10 Replies

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
  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
    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
  Copper Contributor
  Feb 10, 2020
  SergeiBaklan Wow that was quick. Spot on thanks!