More than 7 IF? any substitute?

J

Jean

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!!
 
M

Martin Fishlock

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)
 
H

Harlan Grove

Jean said:
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,
Click to expand...

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

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.
 
H

Harlan Grove

Fred Smith said:
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.
Click to expand...
....

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.
 
Top