Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Fred
 
Posts: n/a
Default Nested IF limit or Open parentheses limit

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

  #2   Report Post  
Bernard Liengme
 
Posts: n/a
Default

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

best wishes

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


"Fred" wrote in message
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



  #3   Report Post  
newbie
 
Posts: n/a
Default

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

"Bernard Liengme" wrote:

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

best wishes

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


"Fred" wrote in message
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




  #4   Report Post  
Frank Kabel
 
Posts: n/a
Default

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


  #5   Report Post  
Fred
 
Posts: n/a
Default


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



  #6   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi Fred
thanks for your feedback :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Fred wrote:
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cannot open a file that Excel says is open Plodhia Excel Discussion (Misc queries) 2 December 7th 04 02:43 AM


All times are GMT +1. The time now is 09:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"