Nested IF formula

[MWaller]

Please can someone help me with a nested if formula. Understand the concept just cannot get it to work. I’m wanting to band a set of data into categories as follows:
Less than 20 = small
20 - 59 = medium
60 - 99 = large
100 plus = extra large

If someone could post me the foruano for this I would be sooooo grateful. Assuming my data starts in b2

Thank you in advance.

 

[MWaller]

*formula not foruano ?

 

[philwojo]

Try this:

=if(b2<20,"small",if(and(b2>=20,b2<=59),"medium",if(and(b2>=60,b2<=99),"large","extra large")

 

[philwojo]

Sorry missed a parenthesis, try this version:

=IF(B2<20,"small",IF(AND(B2>=20,B2<=59),"medium",IF(AND(B2>=60,B2<=99),"large","extra large")))

 

[Marcílio_Lobão]

MWaller, Good afternoon.

Try to use this as you requested:

=IF(B2>=100,"Extra Large",IF(B2>=60,"Large",IF(B2>=20,"Medium","Small")))

This is not the most efficient method for your case.

Try using the VLOOKUP or LOOKUP

Hope this helps.

 

[MWaller]

Thank you so much Philwojo! Worked exactly how I needed it to

 

[jtakw]

Hi,

Did you try Marcilio's formula?
It's shorter and requires less functions/processing.
Here's a LOOKUP just for variety:

Book1
	B	C	D
1			Extra Large
2	100
Sheet36

Cell Formulas
Range	Formula
D1	=LOOKUP(B2,{0,20,60,100},{"Small","Medium","Large","Extra Large"})