Date. perform function every 12 months

[qinyu]

Hi,

i have a row of dates starting with 3/1/2000 (increasing every month) and ends at 3/1/2005.

i need to increase the price of A every 2 yrs by 5%, and the price of B every 3 yrs by 8%. between the above dates.

how can i create a function to do that. and to change the price increase from 2 yrs to 4 yrs by changing one cell (the no. of years)

thanks

 

[Andrew Poulsom]

Welcome to the Board.

Here is an example:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Product	Years	Increase	3/1/2000	12/1/2000	3/1/2001	12/1/2001	3/1/2002	12/1/2002	3/1/2003	12/1/2003	3/1/2004	12/1/2004	3/1/2005	12/1/2005
2	A	2	5%	1,000	1,000	1,000	1,000	1,050	1,050	1,050	1,050	1,103	1,103	1,103	1,103
3	B	3	8%	2,000	2,000	2,000	2,000	2,000	2,000	2,160	2,160	2,160	2,160	2,160	2,160
Sheet1

The formula in E2 is:

=IF((YEAR(E$1)-YEAR($D$1))>=$B2,$D2*(1+$C2)^INT((YEAR(E$1)-YEAR($D$1))/$B2),$D2)

copied down and across.