How to make the formula in column D E F be more dynamic when the number in column C changed into different row?

[suloveh68]

See attached picture for the detail:

Question: The output for column D, E, F should be using the number in column C * % starting from 100% all the way down to year of 21; however, the installed number in column C can happened in year 5,6, or any year, how to make the calculation in column D, E,F be more dynamic? For example, if there is installed amount 55 happened in year 5, how to write a formula in column D to make the number be calculated automatically in year 5

 

[StephenCrump]

Welcome to the Forum!

How about:

	A	B	C	D	E	F	G
1	Year	%		Installed	Calculation 1	Calculation 2	Calculation 3
2	1	1.00
3	2	0.98
4	3	0.96		55	55.00
5	4	0.94			53.90
6	5	0.92			52.80
7	6	0.90			51.70
8	7	0.88			50.60
9	8	0.86		58	49.50	58.00
10	9	0.84			48.40	56.84
11	10	0.82			47.30	55.68
12	11	0.80			46.20	54.52
13	12	0.78			45.10	53.36
14	13	0.76		59	44.00	52.20	59.00
15	14	0.74			42.90	51.04	57.82
16	15	0.72			41.80	49.88	56.64
17	16	0.70			40.70	48.72	55.46
18	17	0.68			39.60	47.56	54.28
19	18	0.66			38.50	46.40	53.10
20	19	0.64			37.40	45.24	51.92
21	20	0.62			36.30	44.08	50.74
22	21	0.60			35.20	42.92	49.56
23					34.10	41.76	48.38
24					33.00	40.60	47.20
25						39.44	46.02
26						38.28	44.84
27						37.12	43.66
28						35.96	42.48
29						34.80	41.30
30							40.12
31							38.94
32							37.76
33							36.58
34							35.40
35
Sheet1

Cell Formulas
Range	Formula
A2:A22	A2	=SEQUENCE(21)
E1:G1	E1	="Calculation "&SEQUENCE(,COUNT(D2:D22))
E2:G34	E2	=LET(d,D2:D22,pc,B2:B22,f,IF(d<>"",1+ROW(d)-MIN(ROW(d))),N,TRANSPOSE(FILTER(f,f)),s,SEQUENCE(ROWS(pc)+MAX(N)-1),IFERROR(INDEX(d,N)*IF(s>=N,INDEX(pc,s-N+1),""),""))
Dynamic array formulas.

 

[suloveh68]

Welcome to the Forum!

How about:

	A	B	C	D	E	F	G
1	Year	%		Installed	Calculation 1	Calculation 2	Calculation 3
2	1	1.00
3	2	0.98
4	3	0.96		55	55.00
5	4	0.94			53.90
6	5	0.92			52.80
7	6	0.90			51.70
8	7	0.88			50.60
9	8	0.86		58	49.50	58.00
10	9	0.84			48.40	56.84
11	10	0.82			47.30	55.68
12	11	0.80			46.20	54.52
13	12	0.78			45.10	53.36
14	13	0.76		59	44.00	52.20	59.00
15	14	0.74			42.90	51.04	57.82
16	15	0.72			41.80	49.88	56.64
17	16	0.70			40.70	48.72	55.46
18	17	0.68			39.60	47.56	54.28
19	18	0.66			38.50	46.40	53.10
20	19	0.64			37.40	45.24	51.92
21	20	0.62			36.30	44.08	50.74
22	21	0.60			35.20	42.92	49.56
23					34.10	41.76	48.38
24					33.00	40.60	47.20
25						39.44	46.02
26						38.28	44.84
27						37.12	43.66
28						35.96	42.48
29						34.80	41.30
30							40.12
31							38.94
32							37.76
33							36.58
34							35.40
35
Sheet1

Cell Formulas
Range	Formula
A2:A22	A2	=SEQUENCE(21)
E1:G1	E1	="Calculation "&SEQUENCE(,COUNT(D2:D22))
E2:G34	E2	=LET(d,D2:D22,pc,B2:B22,f,IF(d<>"",1+ROW(d)-MIN(ROW(d))),N,TRANSPOSE(FILTER(f,f)),s,SEQUENCE(ROWS(pc)+MAX(N)-1),IFERROR(INDEX(d,N)*IF(s>=N,INDEX(pc,s-N+1),""),""))
Dynamic array formulas.

wow! That's so genius! Thank you so much!