Yearly Plan
 

I have a replacement reserve schedule that has the following information in
Columns €“ Current cost to replace, last year replaced, life of item (years),
Due for replacement, Year until next replaced, Future replacement cost and
each future year (now for the next 35 years). I have created a formula (IF /
THEN) that places the future replacement cost into to correct year. This
only works for the first year, but not any additional years, i.e. if the item
needs to be replaced every 4 years. Any thoughts on a formula I could use?

Yearly Plan
 

Hi Tony,
could you please give an example it will help to clarify, (Future year, are
they columns by year, month ??)

"Tony" wrote:

I have a replacement reserve schedule that has the following information in
Columns €“ Current cost to replace, last year replaced, life of item (years),
Due for replacement, Year until next replaced, Future replacement cost and
each future year (now for the next 35 years). I have created a formula (IF /
THEN) that places the future replacement cost into to correct year. This
only works for the first year, but not any additional years, i.e. if the item
needs to be replaced every 4 years. Any thoughts on a formula I could use?

Yearly Plan
 

Columns by year - 2009-2010-2011, etc...

"Eduardo" wrote:

Hi Tony,
could you please give an example it will help to clarify, (Future year, are
they columns by year, month ??)

"Tony" wrote:

I have a replacement reserve schedule that has the following information in
Columns €“ Current cost to replace, last year replaced, life of item (years),
Due for replacement, Year until next replaced, Future replacement cost and
each future year (now for the next 35 years). I have created a formula (IF /
THEN) that places the future replacement cost into to correct year. This
only works for the first year, but not any additional years, i.e. if the item
needs to be replaced every 4 years. Any thoughts on a formula I could use?

Yearly Plan
 

Ok
First name the column that have the year until next replaced and another one
for the future replacement cost then

lets say that you have the information in the columns as follow starting in
row 2 with the information
Current Cost to replace Column A
last year replaced Column B
Life expected column c
Year until next replaced column D
Future replacement cost column E
2009 here enter the formula as follow
=SUMPRODUCT(--(Year_until_next_replaced=F1),Future_replacement_c ost)
2010
2011
2012
Copy that formula all over the years where you need it, and it will
summarize the totals per year from column F up to ......

"Tony" wrote:

I have a replacement reserve schedule that has the following information in
Columns €“ Current cost to replace, last year replaced, life of item (years),
Due for replacement, Year until next replaced, Future replacement cost and
each future year (now for the next 35 years). I have created a formula (IF /
THEN) that places the future replacement cost into to correct year. This
only works for the first year, but not any additional years, i.e. if the item
needs to be replaced every 4 years. Any thoughts on a formula I could use?

Yearly Plan
 

=SUMPRODUCT(--(Year_until_next_replaced=F1),Future_replacement_c ost)

I am a little lost, what are the "--" in the begining of the formula?
Sorry, I have nort worked with this formula before.

"Eduardo" wrote:

Ok
First name the column that have the year until next replaced and another one
for the future replacement cost then

lets say that you have the information in the columns as follow starting in
row 2 with the information
Current Cost to replace Column A
last year replaced Column B
Life expected column c
Year until next replaced column D
Future replacement cost column E
2009 here enter the formula as follow
=SUMPRODUCT(--(Year_until_next_replaced=F1),Future_replacement_c ost)
2010
2011
2012
Copy that formula all over the years where you need it, and it will
summarize the totals per year from column F up to ......

"Tony" wrote:

I have a replacement reserve schedule that has the following information in
Columns €“ Current cost to replace, last year replaced, life of item (years),
Due for replacement, Year until next replaced, Future replacement cost and
each future year (now for the next 35 years). I have created a formula (IF /
THEN) that places the future replacement cost into to correct year. This
only works for the first year, but not any additional years, i.e. if the item
needs to be replaced every 4 years. Any thoughts on a formula I could use?

Yearly Plan
 

Also, your column D, is that to be Year or Year's until next replacement?

"Eduardo" wrote:

Ok
First name the column that have the year until next replaced and another one
for the future replacement cost then

lets say that you have the information in the columns as follow starting in
row 2 with the information
Current Cost to replace Column A
last year replaced Column B
Life expected column c
Year until next replaced column D
Future replacement cost column E
2009 here enter the formula as follow
=SUMPRODUCT(--(Year_until_next_replaced=F1),Future_replacement_c ost)
2010
2011
2012
Copy that formula all over the years where you need it, and it will
summarize the totals per year from column F up to ......

"Tony" wrote:

I have a replacement reserve schedule that has the following information in
Columns €“ Current cost to replace, last year replaced, life of item (years),
Due for replacement, Year until next replaced, Future replacement cost and
each future year (now for the next 35 years). I have created a formula (IF /
THEN) that places the future replacement cost into to correct year. This
only works for the first year, but not any additional years, i.e. if the item
needs to be replaced every 4 years. Any thoughts on a formula I could use?

Yearly Plan
 

http://www.mcgimpsey.com/excel/formulae/doubleneg.html
http://xldynamic.com/source/xld.SUMPRODUCT.html
--
David Biddulph

"Tony" wrote in message
...
=SUMPRODUCT(--(Year_until_next_replaced=F1),Future_replacement_c ost)

I am a little lost, what are the "--" in the begining of the formula?
Sorry, I have nort worked with this formula before.

"Eduardo" wrote:

Ok
First name the column that have the year until next replaced and another
one
for the future replacement cost then

lets say that you have the information in the columns as follow starting
in
row 2 with the information
Current Cost to replace Column A
last year replaced Column B
Life expected column c
Year until next replaced column D
Future replacement cost column E
2009 here enter the formula as follow
=SUMPRODUCT(--(Year_until_next_replaced=F1),Future_replacement_c ost)
2010
2011
2012
Copy that formula all over the years where you need it, and it will
summarize the totals per year from column F up to ......

"Tony" wrote:

I have a replacement reserve schedule that has the following
information in
Columns - Current cost to replace, last year replaced, life of item
(years),
Due for replacement, Year until next replaced, Future replacement cost
and
each future year (now for the next 35 years). I have created a formula
(IF /
THEN) that places the future replacement cost into to correct year.
This
only works for the first year, but not any additional years, i.e. if
the item
needs to be replaced every 4 years. Any thoughts on a formula I could
use?

Yearly Plan
 

Hi Tony,
the --, is in the case there is an error in the formula like empty cell,
with -- you don't get the N/A
In column D I entered the formula B1+C1, last year replaced + life expectancy

I didn't have much information so I assume that you wanted a total
summarized by year for all the replacements, if you want a line by line
amount under each year you can use this formula

=+IF($D2=F$1,$E2,"")
That will enter the amount line by line in each year


"Tony" wrote:

Also, your column D, is that to be Year or Year's until next replacement?

"Eduardo" wrote:

Ok
First name the column that have the year until next replaced and another one
for the future replacement cost then

lets say that you have the information in the columns as follow starting in
row 2 with the information
Current Cost to replace Column A
last year replaced Column B
Life expected column c
Year until next replaced column D
Future replacement cost column E
2009 here enter the formula as follow
=SUMPRODUCT(--(Year_until_next_replaced=F1),Future_replacement_c ost)
2010
2011
2012
Copy that formula all over the years where you need it, and it will
summarize the totals per year from column F up to ......

"Tony" wrote:

I have a replacement reserve schedule that has the following information in
Columns €“ Current cost to replace, last year replaced, life of item (years),
Due for replacement, Year until next replaced, Future replacement cost and
each future year (now for the next 35 years). I have created a formula (IF /
THEN) that places the future replacement cost into to correct year. This
only works for the first year, but not any additional years, i.e. if the item
needs to be replaced every 4 years. Any thoughts on a formula I could use?

Yearly Plan
 

the major fault with this formula is it is only calculating the FV once and
not comp. each year.

"Eduardo" wrote:

Hi Tony,
the --, is in the case there is an error in the formula like empty cell,
with -- you don't get the N/A
In column D I entered the formula B1+C1, last year replaced + life expectancy

I didn't have much information so I assume that you wanted a total
summarized by year for all the replacements, if you want a line by line
amount under each year you can use this formula

=+IF($D2=F$1,$E2,"")
That will enter the amount line by line in each year


"Tony" wrote:

Also, your column D, is that to be Year or Year's until next replacement?

"Eduardo" wrote:

Ok
First name the column that have the year until next replaced and another one
for the future replacement cost then

lets say that you have the information in the columns as follow starting in
row 2 with the information
Current Cost to replace Column A
last year replaced Column B
Life expected column c
Year until next replaced column D
Future replacement cost column E
2009 here enter the formula as follow
=SUMPRODUCT(--(Year_until_next_replaced=F1),Future_replacement_c ost)
2010
2011
2012
Copy that formula all over the years where you need it, and it will
summarize the totals per year from column F up to ......

"Tony" wrote:

I have a replacement reserve schedule that has the following information in
Columns €“ Current cost to replace, last year replaced, life of item (years),
Due for replacement, Year until next replaced, Future replacement cost and
each future year (now for the next 35 years). I have created a formula (IF /
THEN) that places the future replacement cost into to correct year. This
only works for the first year, but not any additional years, i.e. if the item
needs to be replaced every 4 years. Any thoughts on a formula I could use?

Yearly Plan
 

Hi Tony,
Could you give me an example of all the calculations you want to achieve,
thank you


"Tony" wrote:

the major fault with this formula is it is only calculating the FV once and
not comp. each year.

"Eduardo" wrote:

Hi Tony,
the --, is in the case there is an error in the formula like empty cell,
with -- you don't get the N/A
In column D I entered the formula B1+C1, last year replaced + life expectancy

I didn't have much information so I assume that you wanted a total
summarized by year for all the replacements, if you want a line by line
amount under each year you can use this formula

=+IF($D2=F$1,$E2,"")
That will enter the amount line by line in each year


"Tony" wrote:

Also, your column D, is that to be Year or Year's until next replacement?

"Eduardo" wrote:

Ok
First name the column that have the year until next replaced and another one
for the future replacement cost then

lets say that you have the information in the columns as follow starting in
row 2 with the information
Current Cost to replace Column A
last year replaced Column B
Life expected column c
Year until next replaced column D
Future replacement cost column E
2009 here enter the formula as follow
=SUMPRODUCT(--(Year_until_next_replaced=F1),Future_replacement_c ost)
2010
2011
2012
Copy that formula all over the years where you need it, and it will
summarize the totals per year from column F up to ......

"Tony" wrote:

I have a replacement reserve schedule that has the following information in
Columns €“ Current cost to replace, last year replaced, life of item (years),
Due for replacement, Year until next replaced, Future replacement cost and
each future year (now for the next 35 years). I have created a formula (IF /
THEN) that places the future replacement cost into to correct year. This
only works for the first year, but not any additional years, i.e. if the item
needs to be replaced every 4 years. Any thoughts on a formula I could use?

Yearly Plan
 

Tony,
I am leaving now but prepare an example and send to me and I will come back
to you tomorrow morning

"Tony" wrote:

the major fault with this formula is it is only calculating the FV once and
not comp. each year.

"Eduardo" wrote:

Hi Tony,
the --, is in the case there is an error in the formula like empty cell,
with -- you don't get the N/A
In column D I entered the formula B1+C1, last year replaced + life expectancy

I didn't have much information so I assume that you wanted a total
summarized by year for all the replacements, if you want a line by line
amount under each year you can use this formula

=+IF($D2=F$1,$E2,"")
That will enter the amount line by line in each year


"Tony" wrote:

Also, your column D, is that to be Year or Year's until next replacement?

"Eduardo" wrote:

Ok
First name the column that have the year until next replaced and another one
for the future replacement cost then

lets say that you have the information in the columns as follow starting in
row 2 with the information
Current Cost to replace Column A
last year replaced Column B
Life expected column c
Year until next replaced column D
Future replacement cost column E
2009 here enter the formula as follow
=SUMPRODUCT(--(Year_until_next_replaced=F1),Future_replacement_c ost)
2010
2011
2012
Copy that formula all over the years where you need it, and it will
summarize the totals per year from column F up to ......

"Tony" wrote:

I have a replacement reserve schedule that has the following information in
Columns €“ Current cost to replace, last year replaced, life of item (years),
Due for replacement, Year until next replaced, Future replacement cost and
each future year (now for the next 35 years). I have created a formula (IF /
THEN) that places the future replacement cost into to correct year. This
only works for the first year, but not any additional years, i.e. if the item
needs to be replaced every 4 years. Any thoughts on a formula I could use?