forula for IF or True or False

jagankm

New Member
Joined
Apr 12, 2008
Messages
20
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
 

Some videos you may like

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
Joined
Oct 2, 2014
Messages
9
=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
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows
Hi, try:

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

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
=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
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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})
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top