More than 7 IF? any substitute?

[Guest]

Hey!
looks like excel 2003 limits nested IF for like 10, and i need to use
IF(IF(.....) 15 times. Do you guys know what it could be a substitute, excel
says use Lookup but my data is in a drop down list build by Validation Data,

any help is really welcomed!

Thanks a lot!!

 

[Guest]

Jean:

One way is to split the function up into 2 or three parts like:

=if(a1=1,10,if(a1=2,20),0)+if(a1=3,30,if(a1=4,40),0)+if(a1=5,50,if(a1=6,60),0)

or use a macro function as in

function ans(a)

select case a
case 1
ans=10
case 2
ans=20
case 3
ans=30
case 4
ans=40
case 5
ans=50
case 6
ans=60
case else
ans=0
end select
end function

and in the worksheet put =ans(1)

 

[Harlan Grove]

looks like excel 2003 limits nested IF for like 10, and i need to
use IF(IF(.....) 15 times. Do you guys know what it could be a
substitute, excel says use Lookup but my data is in a drop down
list build by Validation Data,

Unclear, but lookup is still the most likely way to accomplish this.
Hard to say for sure without more details from you.

 

[Fred Smith]

There should never be any occasion where you have to use more than 7 nested Ifs.
There are always much cleaner solutions, typically using Vlookup.

Post your If statements, and I'm sure you'll get some good suggestions.

 

[Harlan Grove]

There should never be any occasion where you have to use more than
7 nested Ifs. There are always much cleaner solutions, typically
using Vlookup.

....

Never?

=IF(Type="Animal",
IF(Vertibrate,
IF(WarmBlooded,
IF(Mammal,
IF(Carnivore,
IF(LargerThanHumans,
IF(Feline,
IF(African,
IF(Solitary,
"Leopard",
....

Not that you'd want to play 20 questions in Excel, but there are
hierarchically structured data that exceed 8 levels. Locating members
in such hierarchies would require more than 7 nested IFs.

There are work-arounds, but they're not necessarily cleaner.