IF commands Longer then 7

[davethearchitect]

Hello,

I know there is away to do this, I just cant remember how to. I need a formula that is similar to the IF command but allows more then 7

Thanks
Dave

 

[RalphA]

In all the BASICs, such as BASIC, GWBASIC, QuickBASIC, VisualBASIC and VisualBASIC for Applications, the maximum number of nested functions is seven, plus the enveloping function, so, we can have a total of eight functions, seven of which are nested.

In Excel, we can concatenate as many as 30 statements, or formulas.

So, to get, say, ten IFs for numbers, one can use the following example, which only concatenates two formulas, each with only four nested IFs. Notice that one must make allowance for failure of the preceding IFs in each part, or formula, to ensure success:
In B1, enter:
=IF(A1=1,1,IF(A1=2,2,IF(A1=3,3,IF(A1=4,4,IF(A1=5,5,0))))) + IF(A1=6,6,IF(A1=7,7,IF(A1=8,8,IF(A1=9,9,IF(A1=10,10,0)))))

It is generally understood that the use of this type of solution is better addressed by using VBA, when the total formula gets a little bit longish

 

[Aladin Akyurek]

Hello,

I know there is away to do this, I just cant remember how to. I need a formula that is similar to the IF command but allows more then 7

Thanks
Dave

Describe the problem you want to take up... More often than not, a long chain of IF's can be replaced with a lookup formula.

 

[goldfish]

a lookup or a choose function can also work. You could always do something like:

=IF(A1=5,2,0)+IF(AND(NOT(A1=5),A2=5,3,0)+...

or

=IF(A1="test","Result One","")&IF(A1="fargo","Result Two","")&...

There are generally other acceptable ways to right a formula when one may perceive the need for more than 7 nests. Could you post the formula as you would want it if excel allowed more than 7?

 

[royUK]

maybe the CHOOSE FUnction or possibly VLookup with a table of choics. see

http://www.excel-it.com/excel_functions.htm

 

[Norie]

The limit for nested IFs is 7, but when you reach or approach that limit it's normally possible to use other functions.

What other functions to use it really up to what you actually want to achieve.

You might be able to use Roy's suggestion but there might be other functions you can use.

Ralph

I've never heard of a limitation on nested If's in VB or VBA, or any other basic languages.

 

[royUK]

Ralph, prior to Excel 2007 the limit for nesting Functions was 7, with 2007 it's now 64.

With VBa I don't believe there is any restriction, although with mltiple Ifs a Select Case statement is usually better.

 

[RalphA]

Norie, and everybody else that read my post above:

Accept my apologies for stating as a fact what I thought was true; it is not. I should have said, "In Excel, statements can be nested up to seven times".

 

[royUK]

Norie, and everybody else that read my post above:

Accept my apologies for stating as a fact what I thought was true; it is not. I should have said, "In Excel, statements can be nested up to seven times".

Not a problem we all make mistakes.