Cumulative sum in dynamic arrays

[zn24]

Hi, I'm trying to do a cummulative sum within a dynamic array by each row. What I want to achieve is highlighted in blue with normal formulas. I would love your help with how I can achieve same with dynamic arrays. Thank you.

Cusum with DA.xlsx
	E	F	G	H	I	J	K	L	M	N	O
5
6
7
8
9				Dynamic array
10				0.5	0.7	0.3	0.9	0.3
11				0.3	0.7	0.5	1.0	0.9
12				0.9	0.6	0.3	0.5	0.6
13				0.9	0.2	1.0	0.8	0.4
14				0.4	0.5	0.4	0.6	0.2
15
16
17				what I'm trying to achieve with ddynamic array
18				0.5	1.2	1.5	2.4	2.7
19				0.8	2.2	3.0	4.9	6.1
20				1.7	3.6	4.8	7.1	8.9
21				2.5	4.6	6.8	10.0	12.2
22				2.9	5.5	8.1	11.9	14.3
23
24
25
26
27
28
29
Sheet1

Cell Formulas
Range	Formula
H10:L14	H10	=RANDARRAY(5,5)
H18:L22	H18	=SUM($H$10:H10)
Dynamic array formulas.

 

[shaowu459]

Theres definitely an error there. I just recalculated manually and this should be correct i.e the yellow highlighted cells

Sample.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
47					2022	2023	2024	2025	2026	2027
48
49		8.00%
50												MANUAL CALCULATION TO FIND ANS HIGHLIGHTED IN YELLOW
51						654.4	722.05	785.00	858.62	919.27		125.00	120.00	110.40	101.57	93.44
52						52.35	48.16	53.91	58.49	64.01		5.00	9.60	8.83	8.13	7.48
53						602.0	673.9	731.1	800.1	855.3
54						125.0	125.00	150.00	150.00	150.00			125.00	120.00	110.40	101.57
55						727.0	798.88	881.08	950.14	1,005.26			5.00	9.60	8.83	8.13
56
57	2023	125.00				5.00	8.89	8.23	7.62	7.06				150.00	144.00	132.48
58	2024	125.00				0.00	5.00	8.23	7.62	7.06				6.00	11.52	10.60
59	2025	150.00				0.00	0.00	6.00	9.62	9.06
60	2026	150.00				0.00	0.00	0.00	6.00	9.06					150.00	144.00
61	2027	150.00				0.00	0.00	0.00	0.00	6.00					6.00	11.52
62						5.00	13.89	22.46	30.86	38.23
63					654.40	722.0	785.00	858.62	919.27	967.04						150.00
64																6.00
65						THIS IS WHAT I'M TRYING TO ACHIEVE
66						5.00	9.60	8.83	8.13	7.48		5.00	9.60	8.83	8.13	7.48
67						0.00	5.00	9.60	8.83	8.13		0.00	5.00	9.60	8.83	8.13
68						0.00	0.00	6.00	11.52	10.60		0.00	0.00	6.00	11.52	10.60
69						0.00	0.00	0.00	6.00	11.52		0.00	0.00	0.00	6.00	11.52
70						0.00	0.00	0.00	0.00	6.00		0.00	0.00	0.00	0.00	6.00
71
Schedules

Cell Formulas
Range	Formula
K47:O47	K47	=SEQUENCE(,years,start)
G49	G49	='Assumptions '!G39
Q51	Q51	=K54
R51:U51,U60,T57:U57,S54:U54	R51	=Q51-Q52
Q52,U64,T61,S58,R55	Q52	=$G$49*Q51/2
R52:U52,U61,T58:U58,S55:U55	R52	=$G$49*R51
R54	R54	=K54
K51:O51	K51	=IF(K8#=start,J63,SUMIFS(K63#,K8#,K8#-1))
K52:O52	K52	=IF(K8#=start,G49*J63,SUMIFS(K53#,K8#,K8#-1)*G49)
K53:O53	K53	=K51#-K52#
K54:O54	K54	='Assumptions '!K35:O35
K55:O55	K55	=K53#+K54#
F57:F61	F57	=TRANSPOSE(K47#)
G57:G61	G57	=TRANSPOSE(K54#)
K57:O61	K57	=IFS(K47#<F57#,0,K47#=F57#,K54#*$G$49/2,K47#>F57#,(G57#-MAP(K57:O57,LAMBDA(x,SUM(K57:x))))*$G$49)
S57	S57	=G59
T60	T60	=G60
K62:O62	K62	=BYCOL(K57#,LAMBDA(a,SUM(a)))
J63	J63	=Model!I96
K63:O63	K63	=K55#-K62#
U63	U63	=G61
K66:O66	K66	=Q52:U52
K67:O67	K67	=Q55:U55
K68:O68	K68	=Q58:U58
K69:O69	K69	=Q61:U61
K70:O70	K70	=Q64:U64
Q66:U66	Q66	=Q52:U52
Q67:U67	Q67	=Q55:U55
Q68:U68	Q68	=Q58:U58
Q69:U69	Q69	=Q61:U61
Q70:U70	Q70	=Q64:U64
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
start	='Assumptions '!$H$8	K51:K52, K47
years	='Assumptions '!$H$9	K47

工作簿1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1					2022	2023	2024	2025	2026	2027
2
3		8%
4
5						654.40	722.05	785.00	858.62	919.27
6						52.35	48.16	53.91	58.49	64.01
7						602.00	673.90	731.11	800.10	855.30
8						125.00	125.00	150.00	150.00	150.00
9						727.00	798.88	881.08	950.14	1,005.26
10
11	2023	125				5.00	8.89	8.23	7.62	7.06
12	2024	125				-	5.00	8.23	7,362.00	7.06
13	2025	150				-	-	6.00	9.62	9.06
14	2026	150				-	-	-	6.00	9.06
15	2027	150				-	-	-	-	6.00
16						5.00	13.89	30.86	30.86	38.23
17					654.40	722.00	785.00	919.27	919.27	967.04
18
19
20						5.00	9.60	8.83	8.13	7.48
21						-	5.00	9.60	8.83	8.13
22						-	-	6.00	11.52	10.60
23						-	-	-	6.00	11.52
24						-	-	-	-	6.00
25
Sheet3

Cell Formulas
Range	Formula
F20:J24	F20	=DROP(REDUCE(B11:B15*0,F1:J1,LAMBDA(x,y,HSTACK(x,IFS(y<A11:A15,,y=A11:A15,B11:B15*B3/2,1,(B11:B15-BYROW(x,LAMBDA(z,SUM(z))))*B3)))),,1)
Dynamic array formulas.

 

[shaowu459]

if you registered beta channel, you can use eta-reduced lambda in the formula.

工作簿1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1					2022	2023	2024	2025	2026	2027
2
3		8%
4
5						654.40	722.05	785.00	858.62	919.27
6						52.35	48.16	53.91	58.49	64.01
7						602.00	673.90	731.11	800.10	855.30
8						125.00	125.00	150.00	150.00	150.00
9						727.00	798.88	881.08	950.14	1,005.26
10
11	2023	125				5.00	8.89	8.23	7.62	7.06
12	2024	125				-	5.00	8.23	7,362.00	7.06
13	2025	150				-	-	6.00	9.62	9.06
14	2026	150				-	-	-	6.00	9.06
15	2027	150				-	-	-	-	6.00
16						5.00	13.89	30.86	30.86	38.23
17					654.40	722.00	785.00	919.27	919.27	967.04
18
19
20						5.00	9.60	8.83	8.13	7.48
21						-	5.00	9.60	8.83	8.13
22						-	-	6.00	11.52	10.60
23						-	-	-	6.00	11.52
24						-	-	-	-	6.00
25
Sheet3

Cell Formulas
Range	Formula
F20:J24	F20	=DROP(REDUCE(B11:B15*0,F1:J1,LAMBDA(x,y,HSTACK(x,IFS(y<A11:A15,,y=A11:A15,B11:B15*B3/2,1,(B11:B15-BYROW(x,SUM))*B3)))),,1)
Dynamic array formulas.

 

[zn24]

eta-reduced lambda

OMG thank you so much. I'm not registered beta, never heard of it i'll get on that as well. Thank you soo very much.