Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
yearly calendar | Excel Discussion (Misc queries) | |||
Material Requirements Plan (MRP) and Capacity Requirements Plan (C | Excel Worksheet Functions | |||
Yearly total average? | Excel Discussion (Misc queries) | |||
Update yearly records | Excel Discussion (Misc queries) | |||
yearly invoice | Excel Worksheet Functions |