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.

 

[zn24]

I don't think you've explained what you're looking for well. Can you explain in words what you're trying to do?

Yes so I'm trying to build a depreciation waterfall using the reducing balance method so I need to sum the waterfall by row only. So this formula works well "=MAP(H10#,LAMBDA(x,SUM(H10:x)))" but I need "H10" to move down to H11, then H12 and so forth instead of staying constant

 

[Dave Patton]

Please show an example of your requirement complete with expected results.

With legacy functions, an example (guess) follows:

Scan.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		125.00	8%			5.00	9.60	8.83	8.13	7.48	6.88	6.33	5.82
3		10,000.00	30%			1,500.00	2,550.00	1,785.00	1,249.50	874.65	612.26	428.58	300.00
4		20,000.00	20%			2,000.00	3,600.00	2,880.00	2,304.00	1,843.20	1,474.56	1,179.65	943.72
5		Total				3,505.00	6,159.60	4,673.83	3,561.63	2,725.33	2,093.70	1,614.56	1,249.54
1d

Cell Formulas
Range	Formula
G2:M4	G2	=ROUND(($B2-SUM($F2:F2))*$C2,2)
F2:F4	F2	=B2*C2/2
F5:M5	F5	=BYCOL(F2:M4,SUM)
Dynamic array formulas.

 

[shaowu459]

okay so this formula "=MAP(H10#,LAMBDA(x,SUM(H10:x)))" works well but I need "H10" within the sum to move down to H11, then H12 and so forth instead of staying constant, is that possible?

I'm not sure what you need. can you post expected results?

 

[Cubist]

I'm not sure what you need. can you post expected results?

The expected results are shown in #9 but with an array instead of a drag formula.

 

[zn24]

I'm not sure what you need. can you post expected results?

Hi sure so here what I'm trying to achieve highlighted in yellow using dynamic arrays

Gators.xlsx
	K	L	M	N	O
55
56	5.00	8.89	8.23	7.62	7.06
57	0.00	5.00	8.23	7.62	7.06
58	0.00	0.00	6.00	9.62	9.06
59	0.00	0.00	0.00	6.00	9.06
60	0.00	0.00	0.00	0.00	6.00
61	5.00	13.89	22.46	30.86	38.23
62	774.4	885.51	1,013.05	1,132.19	1,243.96
63
64	THIS IS WHAT I'M TRYING TO ACHIEVE
65	5.00	8.89	8.23	7.62	7.06
66	0.00	5.00	8.89	8.23	7.62
67	0.00	0.00	6.00	10.67	9.88
68	0.00	0.00	0.00	6.00	10.67
69	0.00	0.00	0.00	0.00	6.00
70
71
Schedules

Cell Formulas
Range	Formula
K56:O60	K56	=IFS(K9#<B56#,0,K9#=B56#,K53#*$G$50/2,K9#>B56#,(G56#-MAP(K56:O56,LAMBDA(x,SUM(K56:x))))*$G$50)
K61:O61	K61	=BYCOL(K56#,LAMBDA(a,SUM(a)))
K62:O62	K62	=K54#-K61#
K65:O69	K65	=IF(K$9<$B56,0,IF(K$9=$B56,$G56*$G$50/2,($G56-SUM($K65:K65))*$G$50))
Dynamic array formulas.

 

[shaowu459]

The expected results are shown in #9 but with an array instead of a drag formula.

the results in #9 also could be done with a single REDUCE function.

工作簿1
	A	B	C	D	E	F	G	H	I
1		0.08
2
3
4
5
6
7
8	2023	125		5.00	<-Initialvalue		5.00	TRUE
9				9.60			9.60	TRUE
10				8.83			8.83	TRUE
11				8.13			8.13	TRUE
12				7.48			7.48	TRUE
13				6.88			6.88	TRUE
14
Sheet2

Cell Formulas
Range	Formula
G8:G13	G8	=REDUCE(D8,ROW(1:5),LAMBDA(x,y,VSTACK(x,(B8-SUM(x))*B1)))
D8	D8	=$B$8*$B$1/2
D9:D13	D9	=($B$8-SUM($D$8:D8))*$B$1
H8:H13	H8	=G8=D8
Dynamic array formulas.

 

[shaowu459]

Hi sure so here what I'm trying to achieve highlighted in yellow using dynamic arrays

Gators.xlsx
	K	L	M	N	O
55
56	5.00	8.89	8.23	7.62	7.06
57	0.00	5.00	8.23	7.62	7.06
58	0.00	0.00	6.00	9.62	9.06
59	0.00	0.00	0.00	6.00	9.06
60	0.00	0.00	0.00	0.00	6.00
61	5.00	13.89	22.46	30.86	38.23
62	774.4	885.51	1,013.05	1,132.19	1,243.96
63
64	THIS IS WHAT I'M TRYING TO ACHIEVE
65	5.00	8.89	8.23	7.62	7.06
66	0.00	5.00	8.89	8.23	7.62
67	0.00	0.00	6.00	10.67	9.88
68	0.00	0.00	0.00	6.00	10.67
69	0.00	0.00	0.00	0.00	6.00
70
71
Schedules

Cell Formulas
Range	Formula
K56:O60	K56	=IFS(K9#<B56#,0,K9#=B56#,K53#*$G$50/2,K9#>B56#,(G56#-MAP(K56:O56,LAMBDA(x,SUM(K56:x))))*$G$50)
K61:O61	K61	=BYCOL(K56#,LAMBDA(a,SUM(a)))
K62:O62	K62	=K54#-K61#
K65:O69	K65	=IF(K$9<$B56,0,IF(K$9=$B56,$G56*$G$50/2,($G56-SUM($K65:K65))*$G$50))
Dynamic array formulas.

you just copied part of the worksheet, i can't copy them.

 

[zn24]

Oh my mistake.

Sample.xlsx
	F	G	H	I	J	K	L	M	N	O
47					2022	2023	2024	2025	2026	2027
48
49		8.00%
50
51						654.4	722.05	785.00	858.62	919.27
52						52.35	48.16	53.91	58.49	64.01
53						602.0	673.9	731.1	800.1	855.3
54						125.0	125.00	150.00	150.00	150.00
55						727.0	798.88	881.08	950.14	1,005.26
56
57	2023	125.00				5.00	8.89	8.23	7.62	7.06
58	2024	125.00				0.00	5.00	8.23	7.62	7.06
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
61	2027	150.00				0.00	0.00	0.00	0.00	6.00
62						5.00	13.89	22.46	30.86	38.23
63					654.40	722.0	785.00	858.62	919.27	967.04
64
65						THIS IS WHAT I'M TRYING TO ACHIEVE
66						5.00	8.89	8.23	7.62	7.06
67						0.00	5.00	8.89	8.23	7.62
68						0.00	0.00	6.00	10.67	9.88
69						0.00	0.00	0.00	6.00	10.67
70						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
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)
K62:O62	K62	=BYCOL(K57#,LAMBDA(a,SUM(a)))
J63	J63	=Model!I96
K63:O63	K63	=K55#-K62#
K66:O70	K66	=IF(K$47<$F57,0,IF(K$47=$F57,$G57*$G$49/2,($G57-SUM($K66:K66))*$G$49))
Dynamic array formulas.

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

 

[shaowu459]

Oh my mistake.

Sample.xlsx
	F	G	H	I	J	K	L	M	N	O
47					2022	2023	2024	2025	2026	2027
48
49		8.00%
50
51						654.4	722.05	785.00	858.62	919.27
52						52.35	48.16	53.91	58.49	64.01
53						602.0	673.9	731.1	800.1	855.3
54						125.0	125.00	150.00	150.00	150.00
55						727.0	798.88	881.08	950.14	1,005.26
56
57	2023	125.00				5.00	8.89	8.23	7.62	7.06
58	2024	125.00				0.00	5.00	8.23	7.62	7.06
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
61	2027	150.00				0.00	0.00	0.00	0.00	6.00
62						5.00	13.89	22.46	30.86	38.23
63					654.40	722.0	785.00	858.62	919.27	967.04
64
65						THIS IS WHAT I'M TRYING TO ACHIEVE
66						5.00	8.89	8.23	7.62	7.06
67						0.00	5.00	8.89	8.23	7.62
68						0.00	0.00	6.00	10.67	9.88
69						0.00	0.00	0.00	6.00	10.67
70						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
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)
K62:O62	K62	=BYCOL(K57#,LAMBDA(a,SUM(a)))
J63	J63	=Model!I96
K63:O63	K63	=K55#-K62#
K66:O70	K66	=IF(K$47<$F57,0,IF(K$47=$F57,$G57*$G$49/2,($G57-SUM($K66:K66))*$G$49))
Dynamic array formulas.

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

How to calculate 8.89 in L66? there could be a circular reference error.

 

[zn24]

How to calculate 8.89 in L66? there could be a circular reference error.

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