# forula for IF or True or False

#### jagankm

##### New Member
Hi very one

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

Regards
Jagan

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### lakshya2425

##### New Member
=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

##### MrExcel MVP
Hi, try:

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

#### konew1

##### Well-known Member
=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

##### MrExcel MVP, Moderator
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})

Replies
11
Views
116
Replies
4
Views
74
Replies
15
Views
50
Replies
5
Views
52
Replies
20
Views
120

1,108,624
Messages
5,523,968
Members
409,547
Latest member
AW2020