Multiple "IF" and "And" function.

Alice_Wong

New Member
Joined
Sep 13, 2006
Messages
30
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.
 

Some videos you may like

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
Joined
Feb 16, 2002
Messages
8,458
Alice,

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

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819
=INDEX({"0-10%","10-20%","20-30%","30-40%","40-50%","50-60%","60-70%"},X1/0.1)

Hope it helps!
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,823
Hi Alice

Also

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

HTH
PGC
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top