Traying to create income ranges using nested IF with AND

[Nathag]

I am trying to create the following income ranges in a document.

Less than $5000
$5,000 - $9,999
$10,000 - $19,999
$20,000 - $29,999
$30,000 - $39,999
$40,000 - $49,999
$50,000 - $59,999
$60,000 - $69,999
$70,000 and Over

The data set is too large for grouping ranges in a pivot table, so I thought I might add a helper column to the data range directly by adding a column next to the total income field. I then thought to myself, "Self, wouldn't it be great if I could just use a nested IF statement with AND for those ranges that require it and just copy down?"

No such luck. I tried the following formula and do not quite understand what I did wrong. Total income is in column AF and I am just randomly starting with row 702.

=IF(AF702<5000,"Less than $5,000", IF(AND(AF702>=5000,AF702<10000),"$5,000 - $9,999", IF(AND(AF702>=10000,AF702<20000),"$10,000 - $19,999", IF(AND(AF702>=20000,AF702<30000),"$20,000 - $29,999", IF(AND(AF702>=30000,AF702<40000),"$30,000 - $39,999", IF(AND(AF702>=40000,AF702<50000),"$40,000 - $49,999", IF(AND(AF702>=50000,<60000),"$50,000 - $59,999", IF(AND(AF702>=60000,<70000),"$60,000 - $69,999", IF(AF702>=70000,"$70,000 and Over","N/A")))))))))

Does Excel not allow IF statements without the AND to be mixed in with IF(AND statements? What am I doing wrong here?

Any help is appreciated!

 

[Caribeiro77]

Why don't you create an helper column and just use a simple VLOOKUP, like this:

Book1
	A	B	C	D	E
1	Value	Text
2	0	Less than $5000			59999
3	5000	$5,000 - $9,999			$50,000 - $59,999
4	10000	$10,000 - $19,999
5	20000	$20,000 - $29,999
6	30000	$30,000 - $39,999
7	40000	$40,000 - $49,999
8	50000	$50,000 - $59,999
9	60000	$60,000 - $69,999
10	70000	$70,000 and Over
Sheet1

Cell Formulas
Range	Formula
E3	=VLOOKUP(E2,$A$2:$B$10,2)

 

[Nathag]

Of course, my approach may not be the only approach. Maybe my formula is wrong? Maybe it is impossible? Maybe it can be done with pivot tables and I am unaware of the technique for placing tens of thousands of unique values into ranges?

I'm eager to learn.

 

[Nathag]

Let me try that and get back to you. Thanks!

 

[Mike LH]

Hi,


One way.


=LOOKUP(AF702,{0,5000,10000,20000,30000,40000,50000,60000,70000},{"Less than $5000","$5000-$9999","$10,000-$19,9999","$20,000-$29,999","$30,000-$39,999","$40,000-$49,999","$50,000-$59,999","$60,000-$69,999","$70,000 and over"})

 

[Nathag]

Caribeiro77 - that VLOOKUP trick worked. I forgot about the ability to match approximate without going over. Never even occurred to me to use VLOOKUP for the ranges. Learning something new in Excel everyday. Thank you!!!

 

[Caribeiro77]

Glad it helped...