Re:- Maximum number of nested "if" statements allowed?

[Yanno]

I am using Excel to provide an automatic answer selection for a complex chart. Excel will not accept the formula below, and the problem seems to be related to the maximum number of nested "IF" statements.
I currently have 8 nested "IF" staements, which I want to extend to 32, to cover 4 Charts..... Is there any way this is possible?
=IF(AND(E5="CHART 1",A10<200,B10<=1,),"SEP",IF(AND(E5="CHART 1",A10>=200,A10<1000,B10<=1,),"CAT III",IF(AND(E5="CHART 1",A10>=1000,B10<=1,),"CAT IV",IF(AND(E5="CHART 1",C10<25,B10>1,),"SEP",IF(AND(E5="CHART 1",C10>=25,C10<50,B10>1,),"CAT I",IF(AND(E5="CHART 1",C10>=50,C10<200,B10>1,),"CAT II",IF(AND(E5="CHART 1",C10>=200,C10<1000,B10>1,),"CAT III",IF(AND(E5="CHART 1",C10>=1000,B10>1,),"CAT IV"))))))))

 

[Nimrod]

Looks like a very complicated formula ... telling me what your attempting to achieve and I might be able to figure out a shorter method

 

[cjcobra]

Excel's (97) max is 8. Not sure if it's the same in VB.

 

[Aladin Akyurek]

On 2002-06-05 05:20, Yanno wrote:
I am using Excel to provide an automatic answer selection for a complex chart. Excel will not accept the formula below, and the problem seems to be related to the maximum number of nested "IF" statements.
I currently have 8 nested "IF" staements, which I want to extend to 32, to cover 4 Charts..... Is there any way this is possible?
=IF(AND(E5="CHART 1",A10<200,B10<=1,),"SEP",IF(AND(E5="CHART 1",A10>=200,A10<1000,B10<=1,),"CAT III",IF(AND(E5="CHART 1",A10>=1000,B10<=1,),"CAT IV",IF(AND(E5="CHART 1",C10<25,B10>1,),"SEP",IF(AND(E5="CHART 1",C10>=25,C10<50,B10>1,),"CAT I",IF(AND(E5="CHART 1",C10>=50,C10<200,B10>1,),"CAT II",IF(AND(E5="CHART 1",C10>=200,C10<1000,B10>1,),"CAT III",IF(AND(E5="CHART 1",C10>=1000,B10>1,),"CAT IV"))))))))

I believe

=IF(E5="CHART 1",IF(B10<=1,VLOOKUP(A10,{0,"SEP";200,"CAT III";1000,"CAT IV"},2),VLOOKUP(C10,{0,"SEP";25,"CAT I";50,"CAT II";200,"CAT III";1000,"CAT IV"},2)))

would cover the formula with max nested IFs. Whether it can be extended to cover the 4 charts depends on the lookup structure they might have.

 

[Mark W.]

On 2002-06-05 05:58, cjcobra wrote:
Excel's (97) max is 8. Not sure if it's the same in VB.

The maximum number of nested IFs for all versions of Excel is 7. This is stated explicitly in the Excel Help topic for "IF worksheet function"...

"Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests."

The outermost IF in the example above would not be counted as a nested IF because it's not contained within another IF function. An explanation of nesting can be found in the Excel Help topic for "About multiple functions within functions, or nesting".

 

[rselemani]

Re: - Maximum number of nested "if" statements allowed?

i'm trying to use visual basic editor it is very powerful

 

[deletedalien]

Re: - Maximum number of nested "if" statements allowed?

i did this and works like a charm:

=IF(A1="AC",Surgery,) &IF(A1="AS",Surgery,) &IF(A1="CR",Surgery,) &IF(A1="EC",Surgery,) &IF(A1="ED",Surgery,) &IF(A1="ED",Surgery,) &IF(A1="EP",Surgery,) &IF(A1="GA",Surgery,) &IF(A1="GS",Surgery,) &IF(A1="HG",Surgery,) &IF(A1="HN",Surgery,) &IF(A1="OM",Surgery,) &IF(A1="PS",Surgery,) &IF(A1="SC",Surgery,) &IF(A1="TS",Surgery,) &IF(A1="BJ",Imaging,) &IF(A1="BS",Imaging,) &IF(A1="MB",Imaging,) &IF(A1="MR",Imaging,) &IF(A1="MI",Imaging,) &IF(A1="NM",Imaging,) &IF(A1="OR",Imaging,) &IF(A1="RD",Imaging,) &IF(A1="RD",Imaging,) &IF(A1="SI",Imaging,) &IF(A1="SA",Imaging,) &IF(A1="CC",Clinics,) &IF(A1="CL",Clinics,) &IF(A1="DM",Clinics,) &IF(A1="FP",Clinics,) &IF(A1="GM",Clinics,) &IF(A1="GE",Clinics,) &IF(A1="GP",Clinics,) &IF(A1="HZ",Clinics,) &IF(A1="IM",Clinics,) &IF(A1="LT",Clinics,) &IF(A1="NE",Clinics,) &IF(A1="OB",Clinics,) &IF(A1="ON",Clinics,) &IF(A1="OP",Clinics,) &IF(A1="PC",Clinics,) &IF(A1="PD",Clinics,) &IF(A1="RC",Clinics,) &IF(A1="SM",Clinics,) &IF(A1="UC",Clinics,) &IF(A1="UR",Clinics,) &IF(A1="WH",Clinics,)

not sure if its something that might help you,

i know there aren't that many variables here but hey....

 

[SpillerBD]

Re: - Maximum number of nested "if" statements allowed?

Old nesting limit 7.
New Limit in 2013... 64
Page 129, Excel 2013 In Depth

 

[deletedalien]

Re: - Maximum number of nested "if" statements allowed?

Apparently the limit is 64 Since 2007....

[h=2]Remarks[/h]

• Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. (See Example 3 for a sample of nested IF functions.) Alternatively, to test many conditions, consider using the LOOKUP, VLOOKUP, HLOOKUP, or CHOOSE functions. (See Example 4 for a sample of theLOOKUP function.)
  
  Source:
  
  https://support.office.com/en-US/article/if-function-0f1a0f81-f6e1-46e4-9cd4-9e65445fc3ab