# How to workaround circular ref in array formula?

Copper Contributor

# How to workaround circular ref in array formula?

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

5 Replies

# Re: How to workaround circular ref in array formula?

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

# Re: How to workaround circular ref in array formula?

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

# Re: How to workaround circular ref in array formula?

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

# Re: How to workaround circular ref in array formula?

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

# Re: How to workaround circular ref in array formula?

@rybnz when any of above works