Trouble with Nested IF Function

**gvishnu** · 01-25-2013, 03:15 PM

Hi,

I am using the below formula to make the range of numbers in Column 1 (R) fall into certain buckets in column 2 (S).
The numbers range from 0-30.
I had originally pivoted them and them grouping them by intervals of 5. However now the data is needed in the below format
0 =
1-3 =
4-5 =
6-10 =
11-15 =
16-20 =
>20 =

The last four is easy. Its the second and 3rd interval that is throwing me from using a pivot.

The formula seems to work for some and throws errors for others.

=IF(R3="0","New",IF(R3>0<4,"1-3","4-5"),IF(R3>5<11,"6-10","11-15"))

Can you please help me.

Thanks
GV

**Teethless mama** · 01-25-2013, 03:23 PM

=LOOKUP(R3,{0,1,4,6,11,16,21},{"New","1-3","4-5","6-10","11-15","16-20",">20"})

**gvishnu** · 01-25-2013, 03:51 PM

Hi Teethless Mama....I tried the lookup formula but it is throwing #N/A error for all my records. Not sure how to fix it. Can you please help

**Dunc3142** · 01-25-2013, 03:58 PM

This works using if statements and adding the strings together.
Just copy and paste it into your sheet.

=IF(R1<1,"NEW","")&IF(AND(R1>0,R1<4),"1-3","")&IF(AND(R1>3,R1<6),"4-5","")&IF(AND(R1>5,R1<11),"6-10","")&IF(AND(R1>10,R1<16),"11-15","")&IF(AND(R1>15,R1<21),"16-20","")&IF(R1>20,">20","")

**gvishnu** · 01-25-2013, 04:09 PM

Thank you very much Teethless mama and Dunc3142.

Both your formulas work...I discovered that the issue is because the numbers that I am looking up in the first column are from a vlookup. Because of this the above formulas were throwing an error.

To avoid further hassle I have just pasted that data into another tab and ran the formulas off them.