More than 7 IF? any substitute?
 

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

More than 7 IF? any substitute?
 

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)

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jean" wrote:

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

More than 7 IF? any substitute?
 

Jean wrote...
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.

More than 7 IF? any substitute?
 

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.

--
Regards,
Fred


"Jean" wrote in message
...
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!!

More than 7 IF? any substitute?
 

"Fred Smith" wrote...
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.