Cumulative sum in dynamic arrays

Z

zn24

New Member
Joined
Apr 30, 2024
Messages
12
Office Version
  1. 365
Platform
  1. MacOS
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
EFGHIJKLMNO
5
6
7
8
9Dynamic array
100.50.70.30.90.3
110.30.70.51.00.9
120.90.60.30.50.6
130.90.21.00.80.4
140.40.50.40.60.2
15
16
17what I'm trying to achieve with ddynamic array
180.51.21.52.42.7
190.82.23.04.96.1
201.73.64.87.18.9
212.54.66.810.012.2
222.95.58.111.914.3
23
24
25
26
27
28
29
Sheet1
Cell Formulas
RangeFormula
H10:L14H10=RANDARRAY(5,5)
H18:L22H18=SUM($H$10:H10)
Dynamic array formulas.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Try: =LET(d,H10#,MAP(d,LAMBDA(a,SUM(d*(ROW(d)<=ROW(a))*(COLUMN(d)<=COLUMN(a))))))
 
Upvote 0
Scan.xlsm
HIJKL
9Dynamic array
100.50.70.30.90.3
11
12
13
14
15
16
17what I'm trying to achieve with dynamic array
180.51.21.52.42.7
19
1b
Cell Formulas
RangeFormula
H18:L18H18=SCAN(0,H10:L10,SUM)
Dynamic array formulas.
 
Upvote 0
Scan.xlsm
ABCDEF
1Dynamic array
20.50.70.30.90.3
30.80.80.80.80.8
4
5
60.51.21.52.42.7
71.32.83.95.66.7
8
1b
Cell Formulas
RangeFormula
B6:F7B6=MAP(B2:F3,LAMBDA(x,SUM(B2:x)))
Dynamic array formulas.
 
Upvote 0
Another option with out a spill range.
Excel Formula: 
=MAKEARRAY(5,5,LAMBDA(r,c, SUM(TAKE(RANDARRAY(5,5),r,c))))
 
Upvote 0
Hi thank you all so much for your responses. Okay so this works well per row, but how do I make it per the entire array because its to be part of a larger formula with dynamic array dependent cells vertically and horizontally. I triedd to work back the calculation manually to see what the first row in row 56 should show which is whats highlighted blue. Thank you so much for your help.

Cusum DA.xlsx
FGHIJKLMNOP
48202220232024202520262027
49
508.00%
51
52654.4774.40885.511,013.051,132.19
53125.0125.00150.00150.00150.00
54779.4899.401,035.511,163.051,282.19
55
562023125.005.008.898.237.627.06
572024125.000.005.008.237.627.06
582025150.000.000.006.009.629.06
592026150.000.000.000.006.009.06
602027150.000.000.000.000.006.00
615.0013.8922.4630.8638.23
62654.40774.4885.511,013.051,132.191,243.96
63
64
65120.00110.40101.5793.4485.97
66FIRST ROW SHOULD SHOW THIS5.009.608.838.137.48
67
Schedules
Cell Formulas
RangeFormula
K48:O48K48=SEQUENCE(,years,start)
G50G50='Assumptions '!G39
K52:O52K52=IF(K9#=start,J62,SUMIFS(K62#,K9#,K9#-1))
K53:O53K53='Assumptions '!K35:O35
K54:O54K54=K52#+K53#
F56:F60F56=TRANSPOSE(K48#)
G56:G60G56=TRANSPOSE(K53#)
K56:O60K56=IF(K48#<F56#,0,IF(K48#=F56#,G56#*G50/2,(G56#-MAP(K56:O56,LAMBDA(x,SUM(K56:x))))*G50))
K61:O61K61=BYCOL(K56#,LAMBDA(a,SUM(a)))
J62J62=Model!I96
K62:O62K62=K54#-K61#
K65K65=G56-K56
L65:O65L65=K65-L66
K66K66=$G$50*G56/2
L66:O66L66=$G$50*K65
Dynamic array formulas.
Named Ranges
NameRefers ToCells
start='Assumptions '!$H$8K48, K52
years='Assumptions '!$H$9K48
 
Upvote 0
I don't think you've explained what you're looking for well. Can you explain in words what you're trying to do?
 
Upvote 0
VBA Code: 
=MAP(H10#,LAMBDA(x,SUM(H10:x)))




工作簿1
DEFGHIJKLMNOPQ
5
6
7
8
9Dynamic array
100.7446326180.888620.9378090.2092170.580488
110.5910820720.0741010.6812470.7361530.676297
120.2286211240.0467970.7116270.6420010.965817
130.4881859890.5851540.4841410.7535860.089419
140.5438482970.0721380.5014780.6916810.047764
15
16
17what I'm trying to achieve with ddynamic array
180.7446326181.63325212.57106162.78027843.3607667
191.3357146892.29843523.91749194.86286226.1196475
201.5643358142.57385294.90453636.49190738.7145099
212.0525218023.64719266.46201738.802974411.114996
222.5963700994.26317917.579481910.6121212.971905
23
240.7446326181.6332522.5710622.7802783.360767TRUETRUETRUETRUETRUE
251.3357146892.2984353.9174924.8628626.119648TRUETRUETRUETRUETRUE
261.5643358142.5738534.9045366.4919078.71451TRUETRUETRUETRUETRUE
272.0525218023.6471936.4620178.80297411.115TRUETRUETRUETRUETRUE
282.5963700994.2631797.57948210.6121212.97191TRUETRUETRUETRUETRUE
29
30
31
Sheet1
Cell Formulas
RangeFormula
H10:L14H10=RANDARRAY(5,5)
H18:L22H18=SUM($H$10:H10)
H24:L28H24=MAP(H10#,LAMBDA(x,SUM(H10:x)))
M24:Q28M24=H24#=H18:L22
Dynamic array formulas.
 
Upvote 0
I believe this is the recursion you want. I'm not sure if there's a way to do it with an array.
Book1
ABCDE
18%
2
3
4
5
6
7
820231255.00<- Initial value
99.60
108.83
118.13
127.48
136.88
Sheet7
Cell Formulas
RangeFormula
D8D8= $B$8 * $B$1 / 2
D9:D13D9= ($B$8 - SUM($D$8:D8)) * $B$1
 
Upvote 0
shaowu459 said:
VBA Code: 
=MAP(H10#,LAMBDA(x,SUM(H10:x)))




工作簿1
DEFGHIJKLMNOPQ
5
6
7
8
9Dynamic array
100.7446326180.888620.9378090.2092170.580488
110.5910820720.0741010.6812470.7361530.676297
120.2286211240.0467970.7116270.6420010.965817
130.4881859890.5851540.4841410.7535860.089419
140.5438482970.0721380.5014780.6916810.047764
15
16
17what I'm trying to achieve with ddynamic array
180.7446326181.63325212.57106162.78027843.3607667
191.3357146892.29843523.91749194.86286226.1196475
201.5643358142.57385294.90453636.49190738.7145099
212.0525218023.64719266.46201738.802974411.114996
222.5963700994.26317917.579481910.6121212.971905
23
240.7446326181.6332522.5710622.7802783.360767TRUETRUETRUETRUETRUE
251.3357146892.2984353.9174924.8628626.119648TRUETRUETRUETRUETRUE
261.5643358142.5738534.9045366.4919078.71451TRUETRUETRUETRUETRUE
272.0525218023.6471936.4620178.80297411.115TRUETRUETRUETRUETRUE
282.5963700994.2631797.57948210.6121212.97191TRUETRUETRUETRUETRUE
29
30
31
Sheet1
Cell Formulas
RangeFormula
H10:L14H10=RANDARRAY(5,5)
H18:L22H18=SUM($H$10:H10)
H24:L28H24=MAP(H10#,LAMBDA(x,SUM(H10:x)))
M24:Q28M24=H24#=H18:L22
Dynamic array formulas.
Click to expand...


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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Find (ctrl F) not working on dynamic array output
Replies
3
Views
41
MARK858
MARK858
T
  • Solved
Spilled array formula
Replies
3
Views
124
tana
T
H
  • Question Question
Rolling 12 Months Array Sum
Replies
3
Views
499
Fluff
Fluff
S
  • Question Question
Sum Subtotals Based on numbered subsections
2
Replies
11
Views
593
sameri
S
Z
  • Solved
Using MIN function in dynamic arrays
Replies
6
Views
997
zn24
Z

Forum statistics

Threads
1,223,883
Messages
6,175,168
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top