Is it possible to create an Excel array formula where each element n is the sum of the first n elements of another array?

user_6664142 0
Viewed 149

Assuming there is an array in cells A1# (for instance a sequence or an array of random numbers), I would like to obtain an array in cells B1# where each row (n) of the array is the sum from 1 to n of the rows of the first array. Is it possible to do so with a formula?

excel-formula excel arrays
4 Answers

In B1 enter:

=SUBTOTAL(9,OFFSET(A1#,0,0,SEQUENCE(COUNT(A1#))))

A simple example. If A1 contained the dynamic:

=SEQUENCE(10,,1,1)

enter image description here

user_24746560

If volatility matters and you have Office 365, you can also do:

= MMULT(--( SEQUENCE( 1, ROWS( A1:A10 ) ) <= SEQUENCE( ROWS( A1:A10 ) ) ), A1:A10 )

enter image description here

This will compute faster.

user_46171210

One more version:

=SUMIF(OFFSET(A1,,,SEQUENCE(COUNT(A1#))),"<>")

enter image description here

user_48515900

If you are in the beta channel you can use the new SCAN function.

Cell A1

=SEQUENCE(10,,1,1)

Cell B1

=SCAN(0,A1#,LAMBDA(a,b,a+b))
vzczc_2240
Related