Yearly Plan

[Tony]

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?

 

[Eduardo]

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

 

[Tony]

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

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

 

[Eduardo]

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_cost)
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]

=SUMPRODUCT(--(Year_until_next_replaced=F1),Future_replacement_cost)

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

 

[Tony]

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

 

[Eduardo]

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]

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

 

[Eduardo]

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

 

[Eduardo]

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