Excel formula BETWEEN
Solvedbrucine Posted messages 24573 Registration date Status Membre 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 réponses
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)
=IF(A1>=5, A1<=10, 2, IF(A1>=11, A1<=20, 3, IF(A1>=20, 4, 0)))
Excel response: too many arguments
thank you
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)))
=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.