Nested Function Limitations

[SteveG]

Is there a way to get around the limitation of 7 nested functions withi
a formula?

Thanks

Steve

 

[Frank Kabel]

Hi
no this is the maximum. there're some workarounds though depending on
the type of your formula:
- for IF statements usually a lookup table and VLOOKUP would work
- You may replace parts of the formula with a defined name

You may post your formula for which you have the problem with the
nested limit

 

[SteveG]

Frank,

Here it is. Thanks for any assistance you can provide.

=IF(G4=1,IF(J4="Pass","Customer Service","Failed Cer
#1"),IF(G4=2,IF(J4="Pass","DRS Profile","Failed Cer
#2"),IF(G4=3,IF(J4="Pass","AWD Navigation","Failed Cer
#3"),IF(G4=4,IF(J4="Pass","Escheatment","Failed Cer
#4"),IF(G4=5,IF(J4="Pass","Corporate Actions","Failed Cer
#5"),IF(G4=6,IF(J4="Pass","Investment Plans","Failed Cer
#6"),IF(G4=7,IF(J4="Pass","Tax Overview","Failed Cert #7"),"")))))))

Thanks again.

Steve

 

[Tom Ogilvy]

=IF(G4="","",IF(J4<>"Pass","Failed Cert #" & G4,CHOOSE(G4,"Customer
Service","DRS Profile","AWD Navigation","Escheatment","Corporate
Actions","Investment Plans","Tax Overview")))

 

[Frank Kabel]

Hi Steve
try the following:
1. Set up a separate worksheet as lookup table (assumption: call this
'lookup') with the following layout
A B C
1 Value Pass Fail
2 1 Cust. Service Failed Cert
3 2 DRProfile Failed Cert
4 .....
....

I assumed that J4 could either have the value 'Pass' or 'Fail'. If not
adapt column C to your needs (you could also add more columns to this)


2. Now on your other sheet use the following formula
=IF(ISNA(INDEX('lookup'!A$1:$C$20,MATCH(G4,'lookup'!$A$1:$A$20,0),MATCH
(J4,'lookup'!$A$1:$C$1,0))),"",INDEX('lookup'!A$1:$C$20,MATCH(G4,'looku
p'!$A$1:$A$20,0),MATCH(J4,'lookup'!$A$1:$C$1,0)))