I have 4 columns labelled 1st Year, Frequency, Spread and cost.
then I have columns spanning upto 30 they represent years but can be labelled as 1 to 30. My challenge is that I want a formula/function/macro that depending on what I type in the first 4 columns it will populate the remaining 30 columns with either cost or 0.
example: if I had £9,000 at a frequency of 10, starting on the 5th year with a spread of 3.
The cost represents the cost of each frequent. so in the example I would get £3,000 on each of the 5th,6th and 7th years then another £3,000 on the 15th,16th,17th years and finally another £3,000 on the 25th, 26th and 27th years.
what I want to be able to do is have one function/formula that I can paste across all of the year cells. I currently have a formula that can copy the cost at different frequencies but I cant figure the spread out.
=IF($K:$K>0,IF(OR($8:$8=$J:$J,MOD($8:$8-$J:$J,$K:$K)=0),$L:$L,0),0)
where K is frequency
8:8 is year
J is cost
what is the best way to approach this?
Thank you in advance I hope this has made sense!!
Jenny
Bookmarks