Spreading a given cost of X$'s over Z years Starting at year Y

[STCrosstown]

So I am trying to create a formula to equally spread a given cost X starting at year Y and spread over Z amount of years. Ex

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cost
X[/TD]
[TD]Start Year
Y[/TD]
[TD]Spread over Z years[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]2020[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help is much appreciated

 

[jtakw]

Hi,

Change the 0 (zero) to "" if you want Blank rather than 0 to show:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Cost X	Start Year Y	Spread over Z years	2019	2020	2021	2022	2023	2024	2025
2	1000	2020	5	0	200	200	200	200	200	0
Sheet509

Cell Formulas
Range	Formula
D2	=IF(OR(D1<$B2,D1>=$B2+$C2),0,$A2/$C2)

Formula copied across.

 

[STCrosstown]

Thanks,

What if I want the cost to occur once ever 5 years?

 

[jtakw]

Then use D3 formula copied across:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Cost X	Start Year Y	Spread over Z years	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030	2031	2032	2033	2034	2035	2036	2037	2038	2039	2040
2	1000	2020	5	0	200	200	200	200	200	0
3				0	200	0	0	0	0	200	0	0	0	0	200	0	0	0	0	200	0	0	0	0	200
Sheet509

Cell Formulas
Range	Formula
D2	=IF(OR(D1<$B2,D1>=$B2+$C2),0,$A2/$C2)
D3	=IF(OR(D1<$B2,D1<>MROUND(D1,$C2),D1>=$B2+$C2^2),0,$A2/$C2)

 

[jtakw]

Ignore my D3 formula above, use this one:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Cost X	Start Year Y	Spread over Z years	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030	2031	2032	2033	2034	2035	2036	2037	2038	2039	2040
2	1000	2020	5	0	200	200	200	200	200	0
3				0	200	0	0	0	0	200	0	0	0	0	200	0	0	0	0	200	0	0	0	0	200
Sheet509

Cell Formulas
Range	Formula
D2	=IF(OR(D1<$B2,D1>=$B2+$C2),0,$A2/$C2)
D3	=IF(OR(D1<$B2,MOD(D1-$B2,$C2)<>0,D1>=$B2+$C2^2),0,$A2/$C2)