limit on embedded IFs ?
 

is there a maximum # of embedded IF statements allowed in excel 2002? if so,
how can i sort a list of numeric values into groups?

i have a column of numbers that i want to seperate into groups. i.e.:
1-50=group1, 51-150=group2, 151-300=group3,...1000-1300=group14, 1300=group15
i tried:
=IF(A2<50,1,IF(A2<150,2,IF(A2<300,3,IF(A2...IF(A2< 1300,14,15)))))))))))))))
i've checked the formula closely and it seems like it should work but excel
says there's an error and highlights the 9th "IF"
thanks in advance for your help

limit on embedded IFs ?
 

Hi!

The limit is 7 nested functions (any function, not just IF's).

Create a 2 column table like this:

..............C...........D
1...........0............1
2..........51...........2
3.........151..........3
4.........301..........4
etc

As you can see, use the lower boundary of each group in the table.

Then use a formula like this:

=VLOOKUP(A2,C1:D4,2)

Biff

"dkingston" wrote in message
...
is there a maximum # of embedded IF statements allowed in excel 2002? if
so,
how can i sort a list of numeric values into groups?

i have a column of numbers that i want to seperate into groups. i.e.:
1-50=group1, 51-150=group2, 151-300=group3,...1000-1300=group14,
1300=group15
i tried:
=IF(A2<50,1,IF(A2<150,2,IF(A2<300,3,IF(A2...IF(A2< 1300,14,15)))))))))))))))
i've checked the formula closely and it seems like it should work but
excel
says there's an error and highlights the 9th "IF"
thanks in advance for your help

limit on embedded IFs ?
 

worked like a charm.
thanks biff!

"Biff" wrote:

Hi!

The limit is 7 nested functions (any function, not just IF's).

Create a 2 column table like this:

..............C...........D
1...........0............1
2..........51...........2
3.........151..........3
4.........301..........4
etc

As you can see, use the lower boundary of each group in the table.

Then use a formula like this:

=VLOOKUP(A2,C1:D4,2)

Biff

"dkingston" wrote in message
...
is there a maximum # of embedded IF statements allowed in excel 2002? if
so,
how can i sort a list of numeric values into groups?

i have a column of numbers that i want to seperate into groups. i.e.:
1-50=group1, 51-150=group2, 151-300=group3,...1000-1300=group14,
1300=group15
i tried:
=IF(A2<50,1,IF(A2<150,2,IF(A2<300,3,IF(A2...IF(A2< 1300,14,15)))))))))))))))
i've checked the formula closely and it seems like it should work but
excel
says there's an error and highlights the 9th "IF"
thanks in advance for your help

limit on embedded IFs ?
 

You're welcome. Thanks for the feedback!

Biff

"dkingston" wrote in message
...
worked like a charm.
thanks biff!

"Biff" wrote:

Hi!

The limit is 7 nested functions (any function, not just IF's).

Create a 2 column table like this:

..............C...........D
1...........0............1
2..........51...........2
3.........151..........3
4.........301..........4
etc

As you can see, use the lower boundary of each group in the table.

Then use a formula like this:

=VLOOKUP(A2,C1:D4,2)

Biff

"dkingston" wrote in message
...
is there a maximum # of embedded IF statements allowed in excel 2002?
if
so,
how can i sort a list of numeric values into groups?

i have a column of numbers that i want to seperate into groups. i.e.:
1-50=group1, 51-150=group2, 151-300=group3,...1000-1300=group14,
1300=group15
i tried:
=IF(A2<50,1,IF(A2<150,2,IF(A2<300,3,IF(A2...IF(A2< 1300,14,15)))))))))))))))
i've checked the formula closely and it seems like it should work but
excel
says there's an error and highlights the 9th "IF"
thanks in advance for your help