Nest no more than seven functions???

[Shek5150]

I'm trying to enter the following forumula:

=IF(G5="","",IF(I5>91,"Superior",IF(I5>75,"High
Average",IF(I5>25,"Average",IF(I5>10,"Low Average",IF(I5>8,"Mildly
Impaired-Low Normal", IF(I5>6,"Mildly Impaired", IF(I5=5,"Mild to Moderately
Impaired",IF(I5>1,"Moderately Impaired","Severely Impaired")))))))))

However, MS Excel is not permitting it...and the only thing I seem to be
able to find in the help is "Nest No More Than Seven Functions."

Can someone tell me if this is why Excel won't accept my formula? If so, is
there a way around this...[[just for the record, it gets hung up on
"IF(I5>1,"Moderately Impaired","Severely Impaired")))))))))]]

Thanks, in advance for any help.

Steve

 

[Luke M]

You're correct, you can't nest more than 7 functions. First workaround is to
split the route of logic about halfway (say the >8 condition), as in:
=IF(G5="","",IF(I5>8,IF(I5>91,"Superior",IF(I5>75,"High
Average",IF(I5>25,"Average",IF(I5>10,"Low Average","Mildly Imparied-Low
Normal")))),IF(I5>6,"Mildly Impaired", IF(I5=5,"Mild to Moderately
Impaired",IF(I5>1,"Moderately Impaired","Severely Impaired"))))

Note that formula now has 6 nested functions 1 route, 5 the other route.

ALternative, if you don't mind using space, would be to create a lookup
table. You'd need to play around with integers vs. decimals, but it would
make your formula easier.

 

[Elkar]

Excel 2003 and earlier versions are limited to 7 levels of nested functions.
Excel 2007 increases the limit to 64.

You could use a LOOKUP function instead:

=IF(G5="","",LOOKUP(I5,{0,1,5,6,8,10,25,75},{"Severely Impaired","Moderately
Impaired","Mild to Moderately Impaired","Mildly Impaired","Mildly
Impaired-Low Normal","Low Average","Average","High Average"}))

HTH
Elkar

 

[Jim Thomlinson]

Use a lookup table something like this. In Cells A1:B10 add the following:
Score Description
999 Superior
91 High
75 Average
25 Low Average
10 Mildly Impared - Low Normal
8 Mildly Impared
6 Mild to Moderately Impared
5 Moderately Impared
1 Severly Impared

Now in Cell E1 Add the formula
=INDEX($B$2:$B$10,MATCH(D1,$A$2:$A$10,-1))
Put your score in cell D1 and the correponding rating will be desplayed by
the formula.

Here is another reference on the 7 nested funcitons limit...
http://www.cpearson.com/excel/nested.htm

 

[Bernie Deitrick]

Steve,

Here is one of the many ways around the 7 nest limitation:

=IF(G5="","",LOOKUP(I5,{1,5,6,8,10,25,75,91},{"Severely Impaired","Mild to Moderately
Impaired","Mildly Impaired","Mildly Impaired-Low Normal","Low Average","Average","High
Average","Superior"}))

Here is another:

=IF(G5="","",VLOOKUP(I5,$A$1:$B$8,2))
Where A1 to A8 has 1,5,6,8,10,25,75,91
and B1 to B8 has "Severely Impaired","Mild to Moderately Impaired","Mildly Impaired","Mildly
Impaired-Low Normal","Low Average","Average","High Average","Superior"


HTH,
Bernie
MS Excel MVP

 

[JE McGimpsey]

One alternative:

=IF(G5="","",LOOKUP(I5,{-1,"Severely Impaired";1.01,"Moderately
Impaired";5.01,"Mild to Moderately Impaired";6.01,"Mildly
Impaired";8.01,"Mildly Impaired-Low Normal";10.01,"Low
Average";25.01,"Average";75.01,"High Average";91.01,"Superior"}))

Better: Put the scores and evaluations in a table in a different part of
the worksheet, or a second worksheet, and use VLOOKUP() to return the
evaluations.