# Multiple "IF" and "And" function.

#### Alice_Wong

##### New Member
I have a column of data consist of percentage (0-100%), I would like to categorize them into different "bucket", i.e. 0-10%, 10-20%, 20-30%..., 90-100%.

I wrote this fuction:-
=IF(AND(X1>0%,X1<=10%),"0-10%",IF(AND(X1>10%,X1<=20%),"10-20%",IF(AND(X1>20%,X1<=30%),"20-30%",IF(AND(X1>30%,X1<=40%),"30-40%",IF(AND(X1>40%,X1<=50%),"40-50%",IF(AND(X1>50%,X1<=60%),"50-60%",IF(AND(X1>60%,X1<=70%),"60-70%")))))))

It works for data <=70%. When I continued to add the formula for 70-80%, it said the formula contain error.

Please help.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Brian from Maui

##### MrExcel MVP
Alice,

See the Help files for VLOOKUP and approximate matches. You can have only 7 nested IF's.

#### Alice_Wong

##### New Member
Thanks Brian. I get it now.

#### vane0326

##### Well-known Member
=INDEX({"0-10%","10-20%","20-30%","30-40%","40-50%","50-60%","60-70%"},X1/0.1)

Hope it helps!

#### pgc01

##### MrExcel MVP
Hi Alice

Also

=TEXT(FLOOR(X1,0.1),"0%")&" - "&TEXT(CEILING(X1,0.1),"0%")

HTH
PGC

Replies
4
Views
43
Replies
1
Views
45
Replies
7
Views
102
Replies
1
Views
19
Replies
10
Views
102

Threads
1,108,614
Messages
5,523,919
Members
409,542
Latest member
Shezz01