Nested IF limit or Open parentheses limit

Nested IF limit or Open parentheses limit

Post by Fred » Fri, 10 Dec 2004 23:33:03


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
 
 
 

Nested IF limit or Open parentheses limit

Post by Bernard Li » Fri, 10 Dec 2004 23:56:11

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

best wishes

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Fred" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...



 
 
 

Nested IF limit or Open parentheses limit

Post by bmV3Yml » Sat, 11 Dec 2004 02:47:02

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

"Bernard Liengme" wrote:

 
 
 

Nested IF limit or Open parentheses limit

Post by Frank Kabe » Sat, 11 Dec 2004 02:53:59

i
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" < XXXX@XXXXX.COM > schrieb im Newsbeitrag
news: XXXX@XXXXX.COM ...
was
ran
changes
there
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,
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,

 
 
 

Nested IF limit or Open parentheses limit

Post by Fred » Sat, 25 Dec 2004 00:24:41


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
 
 
 

Nested IF limit or Open parentheses limit

Post by Frank Kabe » Sat, 25 Dec 2004 00:34:34

Hi Fred
thanks for your feedback :-)

--
Regards
Frank Kabel
Frankfurt, Germany