How to workaround circular ref in array formula?

[rybnz]

Trying to make an excel table auto calculates when new row as been added

Column A,
1
5
-7
6

Column B would be the sum of the value in Column A of the current row + the value of column B from the  previous row
A, B
1, 1
5, 6
-7, -1
6, 5

In a traditional way, I would just do =A2+B1, then copy the formula to the entire column, however, this could be 10000+ rows, and I'm trying to utilise array formula so I won't need to manually copy and paste the formula when new rows being added.

I've tried to put this in Column B but it wouldn't work, my thoughts it's to do with circular data.
{A$2:A$20000 + B$1:B$19999}

OR

{A$2:A$20000 + offset(A$2:A$20000, -1, 0)}

Can anyone give some advice please? :folded_hands:

[#2]

@rybnz 

The SCAN function was added to Excel to handle accumulation (This function cannot be placed in a table).

arr being a dynamic range

=SCAN(0,arr,LAMBDA(a,v,a+v))

[#3]

@rybnz  a couple options:

Matrix option:

=LET(n, COUNT(A:A), 
  list,A1:INDEX(A:A,n),
  triangle,--(SEQUENCE(n)>=SEQUENCE(1,n))
  MMULTI(triangle,list))

line 1 will count how many items.  you many need to adjust if you have headers and such

line 2 will dynamically create your list of items, again adjust for headers and such

line 3 creates a diagonal matrix of 1s in lower left and 0s in upper right

line 4 does matrix multiplication to do the sum

another option is using LAMBDA helper functions

=LET(n, COUNT(A:A),
  list,A1:INDEX(A:A,n),
  SCAN(0,list,LAMBDA(q,p, q+p)))

again lines 1 & 2 dynamically find your list

line 3 uses SCAN to pass the values of the "list" to the LAMBDA 1 at a time and then LAMBDA returns an array based on the prior accumulator value ("q" in this case) added to the value being passed ("p")

[#4]

@rybnz 

Main question are there blank cells within the range in column A or not.

[#5]

@Sergei Baklan 

No blank cells, all data are filled from top to bottom in column A

[#6]

@rybnz when any of above works