Multiple "IF" and "And" function.

[Alice_Wong]

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.

 

[Brian from Maui]

Alice,

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

 

[Alice_Wong]

Thanks Brian. I get it now.

 

[vane0326]

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

Hope it helps!

 

[pgc01]

Hi Alice

Also

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

HTH
PGC