Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Too many levels in the IF function?

I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

....You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Too many levels in the IF function?

Unfortunately, the maximum number of nested IF statements in Excel is 64. When you add another level to your formula, it exceeds this limit and you get the error message you mentioned.

One solution to this problem is to use the CHOOSE function instead of nested IF statements. The CHOOSE function allows you to select a value from a list of options based on a numeric index.

Here's an example of how you could use the CHOOSE function to replace your nested IF statements:
  1. =CHOOSE(A1,M1,N1,O1,P1,Q1,R1,S1,T1,U1,U2,U3,U4,U5, U6,U7,U8,U9,U10)

In this formula, the first argument (A1) is the numeric index that determines which value to select. The remaining arguments are the list of options to choose from.

This formula is much simpler and easier to read than the nested IF statements, and it doesn't have the same limitation on the number of levels.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,480
Default Too many levels in the IF function?

Hi

In Excel versions below XL2007, there is a maximum of 7 levels of nesting.

One way around
=INDIRECT(CHAR(A1+77)&"1")

--
Regards
Roger Govier

"trexcel" wrote in message
...
I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it
this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method
to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Too many levels in the IF function?

For what you posted (A1 taking on positive integer values and referencing
consecutive columns starting at Column M in response), you can use this
formula...

=INDEX(M1:U1,1,A1)

You can expand the ending column (Column U) to whatever column you need it
to be.

Rick


"trexcel" wrote in message
...
I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it
this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method
to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 461
Default Too many levels in the IF function?

If that is your set up you can use the offset formula. I don't think there is
a way to get any more nested ifs regardless of file format.

With the offset function you are going to try and manipulate the columns you
want to span. So your formula would be.

=OFFSET(L1,0,A1)

This will start from L1 and pick the cell that is A1 to the right of L1. So
if you have 3 in A1, this will select O1

"trexcel" wrote:

I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 49
Default Too many levels in the IF function?

Firstly in your first formula you have 8 levels of Ifs, I was under the
impression that 6 was max. Use Array Formula instead using ctrl-alt-ent for
more
"trexcel" wrote in message
...
I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it
this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method
to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.



  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,480
Default Too many levels in the IF function?

Far more efficient than my response, Rick.

--
Regards
Roger Govier

"Rick Rothstein (MVP - VB)" wrote in
message ...
For what you posted (A1 taking on positive integer values and referencing
consecutive columns starting at Column M in response), you can use this
formula...

=INDEX(M1:U1,1,A1)

You can expand the ending column (Column U) to whatever column you need it
to be.

Rick


"trexcel" wrote in message
...
I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it
this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method
to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.


  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,344
Default Too many levels in the IF function?

Hi Mathew,

Actually Excel 2003 and earlier only let you enter up to 7 levels of
nesting, in 2007 I believe that is 64 levels. However, Excel 2003 and
earlier can calculate more than 7 level deep ifs.
--
Cheers,
Shane Devenshire


"Mathew P Bennett" wrote:

Firstly in your first formula you have 8 levels of Ifs, I was under the
impression that 6 was max. Use Array Formula instead using ctrl-alt-ent for
more
"trexcel" wrote in message
...
I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it
this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method
to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.




  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default if formula Nesting

I am trying to get the no. of months in order to calculate the depreciation for no. of months it is used and the month can be get from the date the problem is that if the assets is purchased in the month of July than it is 12 months and if in January it is 6 months, so when I need at least 11 nesting to fill out the correct month is there any way to increase the loop length in if formula.

The formula is :

=if(month(O556)=7,12,if(month(O556)=8,11,if(month( O556)=9,10,if(month(O556)=10,9,if(month(O556)=11,8 ,if(month(O556)=12,7,if(month(O556)=1,6,if(month(O 556)=2,5,if(month(O556)=3,4,if(month(O556)=4,3,if( month(O556)=5,2,1)



ShaneDevenshir wrote:

Hi Mathew,Actually Excel 2003 and earlier only let you enter up to 7 levels of
18-Aug-08

Hi Mathew,

Actually Excel 2003 and earlier only let you enter up to 7 levels of
nesting, in 2007 I believe that is 64 levels. However, Excel 2003 and
earlier can calculate more than 7 level deep ifs.
--
Cheers,
Shane Devenshire


"Mathew P Bennett" wrote:

Previous Posts In This Thread:

On Monday, August 18, 2008 4:49 PM
trexce wrote:

Too many levels in the IF function?
I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

....You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.

On Monday, August 18, 2008 5:06 PM
Roger Govier wrote:

HiIn Excel versions below XL2007, there is a maximum of 7 levels of nesting.
Hi

In Excel versions below XL2007, there is a maximum of 7 levels of nesting.

One way around
=INDIRECT(CHAR(A1+77)&"1")

--
Regards
Roger Govier

On Monday, August 18, 2008 5:07 PM
Rick Rothstein \(MVP - VB\) wrote:

For what you posted (A1 taking on positive integer values and referencing
For what you posted (A1 taking on positive integer values and referencing
consecutive columns starting at Column M in response), you can use this
formula...

=INDEX(M1:U1,1,A1)

You can expand the ending column (Column U) to whatever column you need it
to be.

Rick


"trexcel" wrote in message
...

On Monday, August 18, 2008 5:08 PM
akphidel wrote:

If that is your set up you can use the offset formula.
If that is your set up you can use the offset formula. I don't think there is
a way to get any more nested ifs regardless of file format.

With the offset function you are going to try and manipulate the columns you
want to span. So your formula would be.

=OFFSET(L1,0,A1)

This will start from L1 and pick the cell that is A1 to the right of L1. So
if you have 3 in A1, this will select O1

"trexcel" wrote:

On Monday, August 18, 2008 5:11 PM
Mathew P Bennett wrote:

Firstly in your first formula you have 8 levels of Ifs, I was under the
Firstly in your first formula you have 8 levels of Ifs, I was under the
impression that 6 was max. Use Array Formula instead using ctrl-alt-ent for
more

On Monday, August 18, 2008 5:12 PM
Don Guillett wrote:

Too many levels in the IF function?
try this idea

=INDEX(1:1,A1+12)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software


On Monday, August 18, 2008 5:15 PM
Roger Govier wrote:

Far more efficient than my response, Rick.
Far more efficient than my response, Rick.

--
Regards
Roger Govier

"Rick Rothstein (MVP - VB)" wrote in

On Monday, August 18, 2008 5:29 PM
ShaneDevenshir wrote:

Hi Mathew,Actually Excel 2003 and earlier only let you enter up to 7 levels of
Hi Mathew,

Actually Excel 2003 and earlier only let you enter up to 7 levels of
nesting, in 2007 I believe that is 64 levels. However, Excel 2003 and
earlier can calculate more than 7 level deep ifs.
--
Cheers,
Shane Devenshire


"Mathew P Bennett" wrote:

EggHeadCafe - Software Developer Portal of Choice
WebService Enabling SQL Server 2005 Methods
http://www.eggheadcafe.com/tutorials...ing-sql-s.aspx


  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default if formula Nesting

One way...

=CHOOSE(MONTH(O556),6,5,4,3,2,1,12,11,10,9,8,7)

--
Biff
Microsoft Excel MVP


<Hammad Azmat wrote in message ...
I am trying to get the no. of months in order to calculate the depreciation
for no. of months it is used and the month can be get from the date the
problem is that if the assets is purchased in the month of July than it is
12 months and if in January it is 6 months, so when I need at least 11
nesting to fill out the correct month is there any way to increase the loop
length in if formula.

The formula is :

=if(month(O556)=7,12,if(month(O556)=8,11,if(month( O556)=9,10,if(month(O556)=10,9,if(month(O556)=11,8 ,if(month(O556)=12,7,if(month(O556)=1,6,if(month(O 556)=2,5,if(month(O556)=3,4,if(month(O556)=4,3,if( month(O556)=5,2,1)



ShaneDevenshir wrote:

Hi Mathew,Actually Excel 2003 and earlier only let you enter up to 7
levels of
18-Aug-08

Hi Mathew,

Actually Excel 2003 and earlier only let you enter up to 7 levels of
nesting, in 2007 I believe that is 64 levels. However, Excel 2003 and
earlier can calculate more than 7 level deep ifs.
--
Cheers,
Shane Devenshire


"Mathew P Bennett" wrote:

Previous Posts In This Thread:

On Monday, August 18, 2008 4:49 PM
trexce wrote:

Too many levels in the IF function?
I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it
this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method
to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.

On Monday, August 18, 2008 5:06 PM
Roger Govier wrote:

HiIn Excel versions below XL2007, there is a maximum of 7 levels of
nesting.
Hi

In Excel versions below XL2007, there is a maximum of 7 levels of nesting.

One way around
=INDIRECT(CHAR(A1+77)&"1")

--
Regards
Roger Govier

On Monday, August 18, 2008 5:07 PM
Rick Rothstein \(MVP - VB\) wrote:

For what you posted (A1 taking on positive integer values and referencing
For what you posted (A1 taking on positive integer values and referencing
consecutive columns starting at Column M in response), you can use this
formula...

=INDEX(M1:U1,1,A1)

You can expand the ending column (Column U) to whatever column you need it
to be.

Rick


"trexcel" wrote in message
...

On Monday, August 18, 2008 5:08 PM
akphidel wrote:

If that is your set up you can use the offset formula.
If that is your set up you can use the offset formula. I don't think there
is
a way to get any more nested ifs regardless of file format.

With the offset function you are going to try and manipulate the columns
you
want to span. So your formula would be.

=OFFSET(L1,0,A1)

This will start from L1 and pick the cell that is A1 to the right of L1.
So
if you have 3 in A1, this will select O1

"trexcel" wrote:

On Monday, August 18, 2008 5:11 PM
Mathew P Bennett wrote:

Firstly in your first formula you have 8 levels of Ifs, I was under the
Firstly in your first formula you have 8 levels of Ifs, I was under the
impression that 6 was max. Use Array Formula instead using ctrl-alt-ent
for
more

On Monday, August 18, 2008 5:12 PM
Don Guillett wrote:

Too many levels in the IF function?
try this idea

=INDEX(1:1,A1+12)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software


On Monday, August 18, 2008 5:15 PM
Roger Govier wrote:

Far more efficient than my response, Rick.
Far more efficient than my response, Rick.

--
Regards
Roger Govier

"Rick Rothstein (MVP - VB)" wrote in

On Monday, August 18, 2008 5:29 PM
ShaneDevenshir wrote:

Hi Mathew,Actually Excel 2003 and earlier only let you enter up to 7
levels of
Hi Mathew,

Actually Excel 2003 and earlier only let you enter up to 7 levels of
nesting, in 2007 I believe that is 64 levels. However, Excel 2003 and
earlier can calculate more than 7 level deep ifs.
--
Cheers,
Shane Devenshire


"Mathew P Bennett" wrote:

EggHeadCafe - Software Developer Portal of Choice
WebService Enabling SQL Server 2005 Methods
http://www.eggheadcafe.com/tutorials...ing-sql-s.aspx



  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default if formula Nesting

=MOD(18-MONTH(O556),12)+1
--
David Biddulph

<Hammad Azmat wrote in message ...
I am trying to get the no. of months in order to calculate the depreciation
for no. of months it is used and the month can be get from the date the
problem is that if the assets is purchased in the month of July than it is
12 months and if in January it is 6 months, so when I need at least 11
nesting to fill out the correct month is there any way to increase the loop
length in if formula.

The formula is :

=if(month(O556)=7,12,if(month(O556)=8,11,if(month( O556)=9,10,if(month(O556)=10,9,if(month(O556)=11,8 ,if(month(O556)=12,7,if(month(O556)=1,6,if(month(O 556)=2,5,if(month(O556)=3,4,if(month(O556)=4,3,if( month(O556)=5,2,1)



ShaneDevenshir wrote:

Hi Mathew,Actually Excel 2003 and earlier only let you enter up to 7
levels of
18-Aug-08

Hi Mathew,

Actually Excel 2003 and earlier only let you enter up to 7 levels of
nesting, in 2007 I believe that is 64 levels. However, Excel 2003 and
earlier can calculate more than 7 level deep ifs.
--
Cheers,
Shane Devenshire


"Mathew P Bennett" wrote:

Previous Posts In This Thread:

On Monday, August 18, 2008 4:49 PM
trexce wrote:

Too many levels in the IF function?
I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it
this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method
to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.

On Monday, August 18, 2008 5:06 PM
Roger Govier wrote:

HiIn Excel versions below XL2007, there is a maximum of 7 levels of
nesting.
Hi

In Excel versions below XL2007, there is a maximum of 7 levels of nesting.

One way around
=INDIRECT(CHAR(A1+77)&"1")

--
Regards
Roger Govier

On Monday, August 18, 2008 5:07 PM
Rick Rothstein \(MVP - VB\) wrote:

For what you posted (A1 taking on positive integer values and referencing
For what you posted (A1 taking on positive integer values and referencing
consecutive columns starting at Column M in response), you can use this
formula...

=INDEX(M1:U1,1,A1)

You can expand the ending column (Column U) to whatever column you need it
to be.

Rick


"trexcel" wrote in message
...

On Monday, August 18, 2008 5:08 PM
akphidel wrote:

If that is your set up you can use the offset formula.
If that is your set up you can use the offset formula. I don't think there
is
a way to get any more nested ifs regardless of file format.

With the offset function you are going to try and manipulate the columns
you
want to span. So your formula would be.

=OFFSET(L1,0,A1)

This will start from L1 and pick the cell that is A1 to the right of L1.
So
if you have 3 in A1, this will select O1

"trexcel" wrote:

On Monday, August 18, 2008 5:11 PM
Mathew P Bennett wrote:

Firstly in your first formula you have 8 levels of Ifs, I was under the
Firstly in your first formula you have 8 levels of Ifs, I was under the
impression that 6 was max. Use Array Formula instead using ctrl-alt-ent
for
more

On Monday, August 18, 2008 5:12 PM
Don Guillett wrote:

Too many levels in the IF function?
try this idea

=INDEX(1:1,A1+12)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software


On Monday, August 18, 2008 5:15 PM
Roger Govier wrote:

Far more efficient than my response, Rick.
Far more efficient than my response, Rick.

--
Regards
Roger Govier

"Rick Rothstein (MVP - VB)" wrote in

On Monday, August 18, 2008 5:29 PM
ShaneDevenshir wrote:

Hi Mathew,Actually Excel 2003 and earlier only let you enter up to 7
levels of
Hi Mathew,

Actually Excel 2003 and earlier only let you enter up to 7 levels of
nesting, in 2007 I believe that is 64 levels. However, Excel 2003 and
earlier can calculate more than 7 level deep ifs.
--
Cheers,
Shane Devenshire


"Mathew P Bennett" wrote:

EggHeadCafe - Software Developer Portal of Choice
WebService Enabling SQL Server 2005 Methods
http://www.eggheadcafe.com/tutorials...ing-sql-s.aspx



  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Too many levels in the IF function?

I am trying to create an IF formula that I can later paste into data
validation.
get the following error:

The specified formula cannot be entered because it uses more than ^$ levels of
nesting. here is mu Formula

=IF($B$1&$B$2=Control!$J$18,'FY16'!$CD9,IF($B$2&$B $1=Control!$J$19,'FY16'!$BN9,IF($B$2&$B$1=Control! $J$20,'FY16'!$BP9,IF($B$2&$B$1=Control!$J$21,'FY16 '!$BR9,IF($B$2&$B$1=Control!$J$22,'FY16'!$BT9,IF($ B$2&$B$1=Control!$J$23,'FY16'!$CB9,IF($B$2&$B$1=Co ntrol!$J$24,'FY16'!$BS9,IF($B$2&$B$1=Control!$J$26 ,'FY16'!$BV9,IF($B$2&$B$1=Control!$J$27,'FY16'!$BW 9,IF($B$2&$B$1=Control!$J$28,'FY16'!$BY9,IF($B$2&$ B$1=Control!$J$29,'FY16'!$BX9,IF($B$2&$B$1=Control !$J$30,'FY16'!$BO9,IF($B$2&$B$1=Control!$J$31,'FY1 6'!$BQ9,IF($B$2&$B$1=Control!$J$32,'FY16'!$CA9,IF( $B$2&$B$1=Control!$J$33,'FY16'!$CC9,IF($B$2&$B$1=C ontrol!$J$34,'FY16'!$BZ9,IF($B$2&$B$1=Control!$J$3 5,'FY16'!$BU9,IF($B$5+$B$4=10,'FY16'!$AB9,IF($B$2& $B$1=Control!$J$37,'FY16'!$L9,IF($B$2&$B$1=Control !$J$38,'FY16'!$N9,IF($B$2&$B$1=Control!$J$39,'FY16 '!$P9,IF($B$2&$B$1=Control!$J$40,'FY16'!$R9,IF($B$ 2&$B$1=Control!$J$41,'FY16'!$Z9,IF($B$2&$B$1=Contr ol!$J$42,'FY16'!$T9,IF($B$2&$B$1=Control!$J$46,'FY 16'!$W9,IF($B$2&$B$1=Control!$J$44,'FY16'!$T9,IF($ B$2&$B$1=Control!$J$45,'FY16'!$U9,IF($B$2&$B$1=Con trol!$J$47,'FY16'!$V9,IF($B$2&$B$1=Control!$J$48,' FY16'!$M9,IF($B$2&$B$1=Control!$J$49,'FY16'!$O9,IF ($B$2&$B$1=Control!$J$50,'FY16'!$Y9,IF($B$2&$B$1=C ontrol!$J$51,'FY16'!$AA9,IF($B$2&$B$1=Control!$J$5 2,'FY16'!$X9,IF($B$2&$B$1=Control!$J$53,'FY16'!$S9 ,IF($B$5+$B$4=20,'FY16'!$AT9,IF($B$2&$B$1=Control! $M$19,'FY16'!$AD9,IF($B$2&$B$1=Control!$M$20,'FY16 '!$AF9,IF($B$2&$B$1=Control!$M$21,'FY16'!$AH9,IF($ B$2&$B$1=Control!$M$22,'FY16'!$AJ9,IF($B$2&$B$1=Co ntrol!$M$23,'FY16'!$AR9,IF($B$2&$B$1=Control!$M$24 ,'FY16'!$AI9,IF($B$2&$B$1=Control!$M$26,'FY16'!$AL 9,IF($B$2&$B$1=Control!$M$27,'FY16'!$AM9,IF($B$2&$ B$1=Control!$M$28,'FY16'!$AO9,IF($B$2&$B$1=Control !$M$29,'FY16'!$AN9,IF($B$2&$B$1=Control!$M$30,'FY1 6'!$AE9,IF($B$2&$B$1=Control!$M$31,'FY16'!$AG9,IF( $B$2&$B$1=Control!$M$32,'FY16'!$AQ9,IF($B$2&$B$1=C ontrol!$M$33,'FY16'!$AS9,IF($B$2&$B$1=Control!$M$3 4,'FY16'!$AP9,IF($B$2&$B$1=Control!$M$35,'FY16'!$A P9,IF($B$5+$B$4=30,'FY16'!$BL9,IF($B$2&$B$1=Contro l!$M$37,'FY16'!$AV9,IF($B$2&$B$1=Control!$M$38,'FY 16'!$AX9,IF($B$2&$B$1=Control!$M$39,'FY16'!$AZ9,IF ($B$2&$B$1=Control!$M$40,'FY16'!$BB9,IF($B$2&$B$1= Control!$M$41,'FY16'!$BJ9,IF($B$2&$B$1=Control!$M$ 42,'FY16'!$BA9,IF($B$2&$B$1=Control!$M$44,'FY16'!$ BD9,IF($B$2&$B$1=Control!$M$45,'FY16'!$BE9,IF($B$2 &$B$1=Control!$M$46,'FY16'!$BG9,IF($B$2&$B$1=Contr ol!$M$47,'FY16'!$BF9,IF($B$2&$B$1=Control!$M$48,'F Y16'!$AW9,IF($B$2&$B$1=Control!$M$49,'FY16'!$AY9,I F($B$2&$B$1=Control!$M$50,'FY16'!$BI9,IF($B$2&$B$1 =Control!$M$51,'FY16'!$BK9,"0")))))))))))))))))))) ))))))))))))))))))))))))))))))))))))))))))))))
  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,045
Default Too many levels in the IF function?

On Wed, 2 Oct 2013 01:33:54 -0700 (PDT), wrote:

I am trying to create an IF formula that I can later paste into data
validation.
get the following error:

The specified formula cannot be entered because it uses more than ^$ levels of
nesting. here is mu Formula


Below is your formula re-written with line feeds so as to be able to interpret it better:

It seems you are testing forthree different conditions
e.g: B1&B2
B2&B1
B5+B4

And you want to test in a particular order.

In addition, there is no regular relationship between the cell addresses of either the values your are checking, or the return values.

If you have a version of Excel prior to 2007, the nesting limit is 7; if it is 2007 or later, the nesting limit is 64 and this formula exceeds that.

One way to handle this would be to split this formula into multiple cells.
If you don't have to worry about versions prior to 2007, you could split where the condition changes, and then return the value of the first cell that returns a non-FALSE

eg

A1: =IF($B$1&$B$2=Control!$J$18,'FY16'!$CD9)

A2: =IF($B$2&$B$1=Control!$J$19,'FY16'!$BN9,
IF($B$2&$B$1=Control!$J$20,'FY16'!$BP9,
IF($B$2&$B$1=Control!$J$21,'FY16'!$BR9,
IF($B$2&$B$1=Control!$J$22,'FY16'!$BT9,
IF($B$2&$B$1=Control!$J$23,'FY16'!$CB9,
IF($B$2&$B$1=Control!$J$24,'FY16'!$BS9,
IF($B$2&$B$1=Control!$J$26,'FY16'!$BV9,
IF($B$2&$B$1=Control!$J$27,'FY16'!$BW9,
IF($B$2&$B$1=Control!$J$28,'FY16'!$BY9,
IF($B$2&$B$1=Control!$J$29,'FY16'!$BX9,
IF($B$2&$B$1=Control!$J$30,'FY16'!$BO9,
IF($B$2&$B$1=Control!$J$31,'FY16'!$BQ9,
IF($B$2&$B$1=Control!$J$32,'FY16'!$CA9,
IF($B$2&$B$1=Control!$J$33,'FY16'!$CC9,
IF($B$2&$B$1=Control!$J$34,'FY16'!$BZ9,
IF($B$2&$B$1=Control!$J$35,'FY16'!$BU9) <-- enough )'s to close

A3: =IF($B$5+$B$4=10,'FY16'!$AB9)

A4: =IF($B$2&$B$1=Control!$J$37,'FY16'!$L9,
IF($B$2&$B$1=Control!$J$38,'FY16'!$N9,
IF($B$2&$B$1=Control!$J$39,'FY16'!$P9,
IF($B$2&$B$1=Control!$J$40,'FY16'!$R9,
IF($B$2&$B$1=Control!$J$41,'FY16'!$Z9,
IF($B$2&$B$1=Control!$J$42,'FY16'!$T9,
IF($B$2&$B$1=Control!$J$46,'FY16'!$W9,
IF($B$2&$B$1=Control!$J$44,'FY16'!$T9,
IF($B$2&$B$1=Control!$J$45,'FY16'!$U9,
IF($B$2&$B$1=Control!$J$47,'FY16'!$V9,
IF($B$2&$B$1=Control!$J$48,'FY16'!$M9,
IF($B$2&$B$1=Control!$J$49,'FY16'!$O9,
IF($B$2&$B$1=Control!$J$50,'FY16'!$Y9,
IF($B$2&$B$1=Control!$J$51,'FY16'!$AA9,
IF($B$2&$B$1=Control!$J$52,'FY16'!$X9,
IF($B$2&$B$1=Control!$J$53,'FY16'!$S9) <-- enough )'s to close

etc.

Then you can use a formula like:

This formula must be **array-entered**:

=INDEX($A$1:$A$10,MATCH(TRUE,A1:A10<FALSE,0))

(replace A1:A10 with the range where you have entered your series of formulas)
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.


IF you need to deal with versions prior to 2007, then you will have to split the IF sequences into smaller chunks.

Another method of handling this would be to write a User Defined Function in VBA, but I think you can just break up your formula.


-----------------------------------------------------------------------
IF($B$1&$B$2=Control!$J$18,'FY16'!$CD9,
IF($B$2&$B$1=Control!$J$19,'FY16'!$BN9,
IF($B$2&$B$1=Control!$J$20,'FY16'!$BP9,
IF($B$2&$B$1=Control!$J$21,'FY16'!$BR9,
IF($B$2&$B$1=Control!$J$22,'FY16'!$BT9,
IF($B$2&$B$1=Control!$J$23,'FY16'!$CB9,
IF($B$2&$B$1=Control!$J$24,'FY16'!$BS9,
IF($B$2&$B$1=Control!$J$26,'FY16'!$BV9,
IF($B$2&$B$1=Control!$J$27,'FY16'!$BW9,
IF($B$2&$B$1=Control!$J$28,'FY16'!$BY9,
IF($B$2&$B$1=Control!$J$29,'FY16'!$BX9,
IF($B$2&$B$1=Control!$J$30,'FY16'!$BO9,
IF($B$2&$B$1=Control!$J$31,'FY16'!$BQ9,
IF($B$2&$B$1=Control!$J$32,'FY16'!$CA9,
IF($B$2&$B$1=Control!$J$33,'FY16'!$CC9,
IF($B$2&$B$1=Control!$J$34,'FY16'!$BZ9,
IF($B$2&$B$1=Control!$J$35,'FY16'!$BU9,
IF($B$5+$B$4=10,'FY16'!$AB9,
IF($B$2&$B$1=Control!$J$37,'FY16'!$L9,
IF($B$2&$B$1=Control!$J$38,'FY16'!$N9,
IF($B$2&$B$1=Control!$J$39,'FY16'!$P9,
IF($B$2&$B$1=Control!$J$40,'FY16'!$R9,
IF($B$2&$B$1=Control!$J$41,'FY16'!$Z9,
IF($B$2&$B$1=Control!$J$42,'FY16'!$T9,
IF($B$2&$B$1=Control!$J$46,'FY16'!$W9,
IF($B$2&$B$1=Control!$J$44,'FY16'!$T9,
IF($B$2&$B$1=Control!$J$45,'FY16'!$U9,
IF($B$2&$B$1=Control!$J$47,'FY16'!$V9,
IF($B$2&$B$1=Control!$J$48,'FY16'!$M9,
IF($B$2&$B$1=Control!$J$49,'FY16'!$O9,
IF($B$2&$B$1=Control!$J$50,'FY16'!$Y9,
IF($B$2&$B$1=Control!$J$51,'FY16'!$AA9,
IF($B$2&$B$1=Control!$J$52,'FY16'!$X9,
IF($B$2&$B$1=Control!$J$53,'FY16'!$S9,
IF($B$5+$B$4=20,'FY16'!$AT9,
IF($B$2&$B$1=Control!$M$19,'FY16'!$AD9,
IF($B$2&$B$1=Control!$M$20,'FY16'!$AF9,
IF($B$2&$B$1=Control!$M$21,'FY16'!$AH9,
IF($B$2&$B$1=Control!$M$22,'FY16'!$AJ9,
IF($B$2&$B$1=Control!$M$23,'FY16'!$AR9,
IF($B$2&$B$1=Control!$M$24,'FY16'!$AI9,
IF($B$2&$B$1=Control!$M$26,'FY16'!$AL9,
IF($B$2&$B$1=Control!$M$27,'FY16'!$AM9,
IF($B$2&$B$1=Control!$M$28,'FY16'!$AO9,
IF($B$2&$B$1=Control!$M$29,'FY16'!$AN9,
IF($B$2&$B$1=Control!$M$30,'FY16'!$AE9,
IF($B$2&$B$1=Control!$M$31,'FY16'!$AG9,
IF($B$2&$B$1=Control!$M$32,'FY16'!$AQ9,
IF($B$2&$B$1=Control!$M$33,'FY16'!$AS9,
IF($B$2&$B$1=Control!$M$34,'FY16'!$AP9,
IF($B$2&$B$1=Control!$M$35,'FY16'!$AP9,
IF($B$5+$B$4=30,'FY16'!$BL9,
IF($B$2&$B$1=Control!$M$37,'FY16'!$AV9,
IF($B$2&$B$1=Control!$M$38,'FY16'!$AX9,
IF($B$2&$B$1=Control!$M$39,'FY16'!$AZ9,
IF($B$2&$B$1=Control!$M$40,'FY16'!$BB9,
IF($B$2&$B$1=Control!$M$41,'FY16'!$BJ9,
IF($B$2&$B$1=Control!$M$42,'FY16'!$BA9,
IF($B$2&$B$1=Control!$M$44,'FY16'!$BD9,
IF($B$2&$B$1=Control!$M$45,'FY16'!$BE9,
IF($B$2&$B$1=Control!$M$46,'FY16'!$BG9,
IF($B$2&$B$1=Control!$M$47,'FY16'!$BF9,
IF($B$2&$B$1=Control!$M$48,'FY16'!$AW9,
IF($B$2&$B$1=Control!$M$49,'FY16'!$AY9,
IF($B$2&$B$1=Control!$M$50,'FY16'!$BI9,
IF($B$2&$B$1=Control!$M$51,'FY16'!$BK9,"0")))))))) )))))))))))))))))))))))))))))))))))))))))))))))))) ))))))))
  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Too many levels in the IF function?

I am using Excel 2007 and I am able to enter up to 8 levels of nesting, no more.


  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Too many levels in the IF function?

On Thursday, April 3, 2014 11:49:50 AM UTC-6, wrote:
I am using Excel 2007 and I am able to enter up to 8 levels of nesting, no more.


Save the file as an excel workbook (top most option) assuming you have the latest excel and you can enter up to 65 'IF' statements...as opposed to 6-8 whatever it is for excel version 2003
  #17   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Too many levels in the IF function?

VÃ*o 03:49:00 UTC+7 Thứ Ba, ngÃ*y 19 tháng 8 năm 2008, trexcel đã viết:
I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.


Try separate the formula into a few ones and then rejoin them.
  #18   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Too many levels in the IF function?

On Tuesday, August 19, 2008 at 2:19:00 AM UTC+5:30, trexcel wrote:
I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.


  #19   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Too many levels in the IF function?

On Tue, 14 Jun 2016 00:56:41 -0700 (PDT),
wrote:

Vào 03:49:00 UTC+7 Th? Ba, ngày 19 tháng 8 n?m 2008, trexcel ?ã vi?t:
I am trying to create an IF formula that I can later paste into data
validation. However to keep this question simple at first, I will ask it this
way.

Try this formula in a blank spread sheet, in cell B1:

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,0))))))))

That works but if you add another "level":

=IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,IF(A1=9,U1: U10,0)))))))))

...You get the following error:

The specified formula cannot be entered because it uses more levels of
nesting than are allowed in the current file format.

Can we change the file format? Is there another totally different method to
achieve this? Do you have any remedy for this at all?

Thanks for your consideration.


Try separate the formula into a few ones and then rejoin them.


First:
1. I don't get the error in Excel 2010.
2. There may be a typo in your formula: IF(A1=9,U1:U10,0)
I would use "IF(A1=9,U1,0)" instead of using the *range* "U1:U10" as a
result. It seems to work as intended anyway, but it seems unnecessary
to use a *range* here.

One alternative: Create a user-defined function in VBA.

Or use an extra column, and get the final result from C1:

B1: =IF(A1=1,M1,IF(A1=2,N1,IF(A1=3,O1,IF(A1=4,P1,IF(A1 =5,Q1,0.99)))))
C1: =IF(B1<0.99,B1,IF(A1=6,R1,IF(A1=7,S1,IF(A1=8,T1,I F(A1=9,U1,0)))))

Using the value of "0.99" as a flag.

The flag could be text if you want to use the 'exact" function for the
test in C1, though the test would need to be equivalent to
"=if(not(exact(B1,"flag")),...".

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can I increase nested function 'IF' in EXCEL upto 10 levels pandurrr Excel Worksheet Functions 8 May 9th 23 07:45 PM
What function do I use to summarize data levels on three sheets? Learningfast Excel Worksheet Functions 2 December 29th 07 04:12 AM
sum sub levels. Jerome Excel Discussion (Misc queries) 5 April 2nd 06 03:24 AM
How to setup a nested if function with 10 levels? Haritt Excel Worksheet Functions 4 March 9th 06 12:58 AM
IF function with 14 nested levels Dan Fingerman Excel Worksheet Functions 3 September 5th 05 05:57 PM


All times are GMT +1. The time now is 03:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"