forula for IF or True or False

[jagankm]

Hi very one
I am new to excel so please help me out for following


If the value cell A1 is 1 to 5 then value in cell B1 shall be 1,If the value in cell A1 is 6 to 15 then the value in cell B1 shall be 2, I the value in cell A1 is 16 to 30 than value in cell B1 shall be 3, If the value in cell A1 is 31 to 50 than the value in cell B1 shall be 4, if the value in cell A1 is 51 to 100 than the value in cell B1 shall be 5, If the value in cell A1 is greater than 100 then value in cell B1 shall be 6

Seems very complicated to me so please help me out

Regards
Jagan

 

[lakshya2425]

=if(and(a1>=1,a1<=5),1,if(and(a1>=6,a1<=15),2,if(and(a1>=16,a1<=30),3,if(and(a1>=51,a1<=50),4,if(and(a1>=51,a1<100),5,6)))))

 

[FormR]

Hi, try:

=LOOKUP(A1,{1,6,16,31,51,101},{1,2,3,4,5,6})

 

[konew1]

=if(and(a1>=1,a1<=5),1,if(and(a1>=6,a1<=15),2,if(and(a1>=16,a1<=30),3,if(and(a1>=51,a1<=50),4,if(and(a1>=51,a1<100),5,6)))))

If A1 is not ever going to be <1 then it can be simplified to
=if(a1<=5,1,if(a1<=15,2,if(a1<=30,3,if(a1<=50,4,if(a1<100,5,6)))))
If it could be <1 then
=if(a1<1,"less than 1", if(a1<=5,1,if(a1<=15,2,if(a1<=30,3,if(a1<=50,4,if(a1<100,5,6))))))

 

[Peter_SSs]

FormR's solution is more versatile if there are other results to return, but given the specific numbers you want returned, this should suffice.

=MATCH(A1,{1,6,16,31,51,101})