# IF commands Longer then 7

#### davethearchitect

##### New Member
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### RalphA

##### Well-known Member
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

##### MrExcel MVP
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

##### Well-known Member
a lookup or a choose function can also work. You could always do something like:

Code:
``=IF(A1=5,2,0)+IF(AND(NOT(A1=5),A2=5,3,0)+...``
or
Code:
``=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?

#### Norie

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
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.

Replies
1
Views
56
Replies
5
Views
246
Replies
11
Views
484
Replies
3
Views
126
Replies
1
Views
347

1,190,921
Messages
5,983,595
Members
439,852
Latest member
balasat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back