How to workaround circular ref in array formula?

Copper Contributor

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

Column A,

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}


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

Can anyone give some advice please? :folded_hands:

5 Replies


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


arr being a dynamic range





@rybnz  a couple options:

Matrix option:

=LET(n, COUNT(A:A), 

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

  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")


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

@Sergei Baklan 

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

@rybnz when any of above works