Forum Discussion

Keith Herring's avatar
Keith Herring
Copper Contributor
Jul 25, 2018
Solved

IF AND function

Greetings,

 

I need help with the following If/AND function\logic:

IF (cell) value = 0

   THEN return 20

ELSE IF value >= 0.1 AND <= 0.9

   THEN return 15

ELSE IF value >= 1 AND <= 1.5

   THEN return 10

ELSE IF value >= 1.6 AND <= 9.9

   THEN return 5

ELSE If value >= 10

   THEN return 0

 

=IF(P2=0,20,IF(AND((P2>=0.1),(P2<=0.9),15,IF(AND((P2>=1),(P2<=1.5),10,IF(AND((P2>=1.6),(P2<= 9.9),5,IF(P2>= 10,0)))

 

Thanks in advance for your help!

Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 25, 2018

    Hi Keith,

    =IF(P2=0,20,
   IF(AND(P2>=0.1,P2<=0.9),15,
   IF(AND(P2>=1,P2<=1.5),10,
   IF(AND(P2>=1.6,P2<= 9.9),5,
   IF(P2>= 10,0,"not in conditions"
)))))

    or

    =LOOKUP(P2,{-1,0.1,1,1.6,10},{20,15,10,5,0})

     

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 25, 2018

    Hi Keith,

    =IF(P2=0,20,
   IF(AND(P2>=0.1,P2<=0.9),15,
   IF(AND(P2>=1,P2<=1.5),10,
   IF(AND(P2>=1.6,P2<= 9.9),5,
   IF(P2>= 10,0,"not in conditions"
)))))

    or

    =LOOKUP(P2,{-1,0.1,1,1.6,10},{20,15,10,5,0})