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 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? 🙏

excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Mar 31, 2023

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

     

     

  • 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