View original (French)

Excel formula BETWEEN

Solved
RAF26ML Posted messages 125 Registration date   Status Member Last intervention   -  
brucine Posted messages 24735 Registration date   Status Member Last intervention   -

Hello,

I am looking to create a formula that applies a result based on a range, let me explain:

If A1 is less than 5 then 0, if A1 is between 5 and 10 then 2, if A1 is between 10 and 20 then 3, if A1 is greater than 20 then 4.

Thank you for your help.

Best regards.

4 answers

  1.
    brucine Posted messages 24735 Registration date   Status Member Last intervention   4 154
     

    Hello,

    The condition may not be consistent with whether the bounds are strictly included or not.

    Exactly the same question was asked not long ago, it can be simply resolved by:

    =IF.CONDITIONS(A1>20;4;A1>10;3;A1>5;2;A1<=5;0)

    0
  2. RAF26ML Posted messages 125 Registration date   Status Member Last intervention  
     

    =IF(A1>=5, A1<=10, 2, IF(A1>=11, A1<=20, 3, IF(A1>=20, 4, 0)))

    Excel response: too many arguments

    thank you

    0
    1. brucine Posted messages 24735 Registration date   Status Member Last intervention   4 154
       

      Hello,

      If your Excel version has IF. CONDITIONS, you're complicating your life for no reason.

      If not, your syntax is incorrect because each of the first two conditions should be preceded by AND, IF(AND(A1>=5;A1<=10);2

      It's up to you to determine it, but your boundary cannot be both <=20 and >=20.

      Anyway, it's simpler to write it with the boundary you want:

      =IF(A1>20;4;IF(A1>10;3;IF(A1>5;2;0)))

      0
  3. RAF26ML Posted messages 125 Registration date   Status Member Last intervention  
     

    =IF(A1<5,0,IF(A1<10,2,IF(A1<20,3,4)))

    In fact, I needed to approach it differently...

    It works, thanks to the forum, I found it thanks to your advice..

    Have a good day.

    0
  4. danielc0 Posted messages 2175 Registration date   Status Member Last intervention   286
     

    Hello everyone,

    =(A1>5)*2+(A1>10)+(A1>20)

    Daniel

    0
    1. brucine Posted messages 24735 Registration date   Status Member Last intervention   4 154
       

      Hello,

      Yes, I never think of this kind of trick, it's definitely much more elegant.

      0