activity frequency - formula

[eddster]

Hi All,

I wondered if anyone can help me with a simple formula to automatically input activity cost data on the basis of frequency intervals.

Below is a simple example the cells in green are input by the user, the yellow ones I would like to be calculated by a formula.

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Activity	1st year	frequency (years)	cost	2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029
2	paint wall	2018	1	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000
3	replace flooring	2019	10	10000		10000										10000
4	replace boiler	2018	10	5000	5000										5000

<tbody>
</tbody>

 

[baitmaster]

In F2: =(MOD(F$1-$C2,$D2)=0)*$E2

 

[eddster]

In F2: =(MOD(F$1-$C2,$D2)=0)*$E2

In F2: =(MOD(F$1-$C2,$D2)=0)*$E2

Hi Baitmaster thank you for your prompt response and assistance

I have run the formula which has partly worked for my needs but does not fully allow for the scenario in that I wish the frequency to apply only from the 1st activity date i.e. looking forward

in the formula given it is including costs for activity before the 1st year occurrence

example below - items incorrect are in RED

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	q
1	activity	1st YEAR	frequency (years)	COST	2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029
2	PAINT1	2018	1	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000
3	PAINT2	2019	2	500	0	500	0	500	0	500	0	500	0	500	0	500
4	PAINT3	2018	3	750	750	0	0	750	0	0	750	0	0	750	0	0
5	PAINT4	2019	1	200	200	200	200	200	200	200	200	200	200	200	200	200
6	PAINT5	2019	5	100	0	100	0	0	0	0	100	0	0	0	0	100
7	PAINT6	2020	1	50	50	50	50	50	50	50	50	50	50	50	50	50
8	PAINT7	2020	2	40	40	0	40	0	40	0	40	0	40	0	40	0
9	PAINT8	2020	3	20	0	0	20	0	0	20	0	0	20	0	0	20
10	PAINT9	2021	3	10	10	0	0	10	0	0	10	0	0	10	0	0
11	PAINT10	2021	1	5	5	5	5	5	5	5	5	5	5	5	5	5

<tbody>
</tbody>

 

[eddster]

Hi Baitmaster

Sorry brain was being non functional yesterday

solved with =IF($C3>F$2,0,((MOD(F$2-$C3,$D3)=0)*$E3))

Regards

Edd