Nested IF limit or Open parentheses limit

[Fred]

I have a multi-worksheet cost estimate spreadsheet that has been
developed and enhanced over a number of years. The latest request was
for date cells (Year & Month) to auto-fill with the current year and
month as a prompt for the Project Manager to set them to the correct
value. After some playing around with formats and date calculations
(many thanks to Cpearson's site) I got that sorted out.

I was left with one problem on the capital depreciation worksheet, in
that it returned #Value and not 0 or a correct value as I expected.
Aha, I though, simply put in ISERROR and away we go. That's where I ran
into problems and attach the original base formula below.

What I tried was inserting the Year and Month functions, due to changes
required in cell formats to accommodate the auto completing of
Year/Month info. This works, but resulted in "an error" and the only
way, I could find, round that was to reduce the number of IF's, i.e.
the number of years over which purchases could be depreciated. Given
that we require a minimum of 4 years I am no longer covered, and there
are future requirements to permit up to 25 years depreciation, for
property.

Can anyone help me out of this problem please ?

N15 will end up with 0 or the depreciation value

Current formula results in 0 or the depreciation figure over the
appropriate number of years (up to 6)

N12 is the current year, custom format Number, 0 decimal places
J15 is the years of purchase, custom format Number, 0 decimal places
K15 is the month of purchase, custom format Number, 0 decimal places
I15 is the value of the purchase, Currency, 0 decimal places
L15 is the number of years to depreciate over, Number, 0 decimal
places, numeric value between 1 and 6 years

=
IF(N$12=$J15,(1-($K15-1)/12)*$I15/$L15,
IF(N$12=$J15+$L15,($I15/$L15)*($K15-1)/12,
IF(AND(N$12=$J15+1,N$12<=$J15+$L15),$I15/$L15,
IF(AND(N$12=$J15+2,N$12<=$J15+$L15),$I15/$L15,
IF(AND(N$12=$J15+3,N$12<=$J15+$L15),$I15/$L15,
IF(AND(N$12=$J15+4,N$12<=$J15+$L15),$I15/$L15,
IF(AND(N$12=$J15+5,N$12<=$J15+$L15),$I15/$L15,
0
)))))))

The new formula works but only for 3 years

N12 is the current year, custom format yyyy
J15 is the years of purchase, custom format yyyy
K15 is the month of purchase, custom format mm
I15 is the value of the purchase, Currency, 0 decimal places
L15 is the number of years to depreciate over, Number, 0 decimal
places, numeric value between 1 and 6 years

=
IF(ISERROR(
IF(YEAR(N$12)=YEAR($J15),(1-(MONTH($K15)-1)/12)*$I15/$L15,
IF(YEAR(N$12)=YEAR($J15)+$L15,($I15/$L15)*(MONTH($K15)-1)/12,
IF(AND(YEAR(N$12)=YEAR($J15)+1,YEAR(N$12)<=YEAR($J15)+$L15),$I15/$L15,
IF(AND(YEAR(N$12)=YEAR($J15)+2,YEAR(N$12)<=YEAR($J15)+$L15),$I15/$L15,
IF(AND(YEAR(N$12)=YEAR($J15)+3,YEAR(N$12)<=YEAR($J15)+$L15),$I15/$L15,
0
))))),
0,(
IF(YEAR(N$12)=YEAR($J15),(1-(MONTH($K15)-1)/12)*$I15/$L15,
IF(YEAR(N$12)=YEAR($J15)+$L15,($I15/$L15)*(MONTH($K15)-1)/12,
IF(AND(YEAR(N$12)=YEAR($J15)+1,YEAR(N$12)<=YEAR($J15)+$L15),$I15/$L15,
IF(AND(YEAR(N$12)=YEAR($J15)+2,YEAR(N$12)<=YEAR($J15)+$L15),$I15/$L15,
IF(AND(YEAR(N$12)=YEAR($J15)+3,YEAR(N$12)<=YEAR($J15)+$L15),$I15/$L15,
0
))))))

Thanks in advance for any help provided
Regards
Fred Newton

 

[Bernard Liengme]

With all functions, nesting is permitted to seven (7) levels

best wishes

 

[Guest]

Is there any way to "work around " the nesting limit?

 

[Frank Kabel]

Hi
why are you using different IF statements for the years as you always
return the value
$I15/$L15
for the condition
AND(YEAR(N$12)=YEAR($J15)+x,YEAR(N$12)<=$J15+$L15)

Why not simplify that? Maybe with
AND(YEAR(N$12)>=YEAR($J15)+1,YEAR(N$12)<=YEAR($J15)+5,YEAR(N$12)<=$J15+
$L15)

 

[Fred]

Hi Frank,

That's what I needed, a fresh pair of eyes to look at it and state the
obvious. Many thanks, changed the formulae and all works a treat, even
the If(ISError goes in just fine now.

Thanks again
Regards
Fred Newton

 

[Frank Kabel]

Hi Fred
thanks for your feedback