|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
August 18th, 2008, 09:49 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
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
August 18th, 2008, 10:06 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.
|
|
#3
August 18th, 2008, 10:07 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.
|
|
#4
August 18th, 2008, 10:08 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.
|
|
#5
August 18th, 2008, 10:11 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.
|
|
#6
August 18th, 2008, 10:12 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Too many levels in the IF function?
|
|
#7
August 18th, 2008, 10:15 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.
|
|
#8
August 18th, 2008, 10:29 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.
|
|
#9
November 13th, 2009, 07:03 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
|
|
#10
November 13th, 2009, 07:42 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
