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

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 ...

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 ...

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

"Bernard Liengme" wrote:

"Bernard Liengme" wrote:

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,

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,

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

Hi Fred

thanks for your feedback :-)

--

Regards

Frank Kabel

Frankfurt, Germany

thanks for your feedback :-)

--

Regards

Frank Kabel

Frankfurt, Germany

1. limit of nested parentheses in visual.net

2. Exceeding the limit for Nested IFs

5. Editing and Moviing from 6 If's to 7 Ifs (Nested)

6. nested ifs and end ifs and elses

8. excel SUM function - can it be limited to whats in parentheses?

9. Limit of CDFS.IFS filesystem-size?

10. IF function - limit to the number of IFs?

11. Limit to IFs ???

13. What is quicker? Nested or non nested ifs

14. New IFS interface (jfs.ifs, udf.ifs for example)

15. Changed mailbox limit but user still keeping default Exchange limits

6 post • Page:**1** of **1**