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(O556)=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 Devenshir
:
Previous Posts In This Thread:
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.
HiIn Excel versions below XL2007, there is a maximum of 7 levels of nesting.
H
In Excel versions below XL2007, there is a maximum of 7 levels of nesting
One way aroun
=INDIRECT(CHAR(A1+77)&"1"
-
Regard
Roger Govier
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
Ric
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 O
:
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 th
impression that 6 was max. Use Array Formula instead using ctrl-alt-ent fo
more
Re: Too many levels in the IF function?
try this ide
=INDEX(1:1,A1+12
-
Don Guillet
Microsoft MVP Exce
SalesAid Softwar
(e-mail address removed)
Far more efficient than my response, Rick.
Far more efficient than my response, Rick
-
Regard
Roger Govie
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
:
EggHeadCafe - Software Developer Portal of Choice
WebService Enabling SQL Server 2005 Methods
http://www.eggheadcafe.com/tutorial...d-49734beab0a1/webservice-enabling-sql-s.aspx