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)

--
Regards
Frank Kabel
Frankfurt, Germany

"Fred" schrieb im Newsbeitrag
oups.com...
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($J 15)+$L15),$I15/$L15,

IF(AND(YEAR(N$12)=YEAR($J15)+2,YEAR(N$12)<=YEAR($J 15)+$L15),$I15/$L15,

IF(AND(YEAR(N$12)=YEAR($J15)+3,YEAR(N$12)<=YEAR($J 15)+$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($J 15)+$L15),$I15/$L15,

IF(AND(YEAR(N$12)=YEAR($J15)+2,YEAR(N$12)<=YEAR($J 15)+$L15),$I15/$L15,

IF(AND(YEAR(N$12)=YEAR($J15)+3,YEAR(N$12)<=YEAR($J 15)+$L15),$I15/$L15,
0
))))))

Thanks in advance for any help provided
Regards
Fred Newton