Forum Discussion

rybnz's avatar
rybnz
Copper Contributor
Mar 31, 2023

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 ...
excel
Formulas and Functions
mtarler's avatar
mtarler
Silver Contributor
Mar 31, 2023

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

Resources