Make any formula immune to circular references
Syntax: =\\(range) => range
=LAMBDA(range,
LET(
range_rows, ROW(range),
range_columns, COLUMN(range),
minrow, MIN(range_rows),
maxrow, MAX(range_rows),
mincolumn, MIN(range_columns),
maxcolumn, MAX(range_columns),
r, ROW(), // cell containing the formula
c, COLUMN(), // cell containing the formula
IF(AND(r>=minrow,r<=maxrow,c>=mincolumn,c<=maxcolumn,SHEET(range)=SHEET()), // check if cell containing the formula is even in range
LET(
rowcount, maxrow-minrow+1,
columncount, maxcolumn-mincolumn+1,
sourcecol, CHOOSECOLS(range,c-mincolumn+1),
middle,
IF(rowcount=1,
"", // there is only one row
SWITCH(r,
minrow, // cell containing the formula is in the first row
VSTACK(
"",
TAKE(sourcecol, r-maxrow)
),
maxrow, // cell containing the formula is in the last row
VSTACK(
TAKE(sourcecol, r-minrow),
""
),
VSTACK( // else -> cell containing the formula is a middle row
TAKE(sourcecol, r-minrow),
"",
TAKE(sourcecol, r-maxrow)
)
)
),
IF(columncount=1,
middle, // there is only one column
SWITCH(c,
mincolumn, HSTACK(middle,TAKE(range, rowcount, c-maxcolumn)), // column with the cell containing the formula is first
maxcolumn, HSTACK(TAKE(range, rowcount, c-mincolumn),middle), // column with the cell containing the formula is last
HSTACK(TAKE(range, rowcount, c-mincolumn),middle,TAKE(range, rowcount, c-maxcolumn)) // column with the cell containing the formula is in the middle
))
),
range // return unmodified range if cell containing the formula is not in it.
)
)
)
Documentation
Takes a range and removes from it the reference to the cell in which the function itself resides, returning the rest of the range.
Commentary
Technique used in this function is enabled by the fact that the TAKE() function doesn’t trigger a circular reference warning if the cells you are returning don’t include the cell that contains the formula, even though the range as a whole does. This is true for functions such as DROP, CHOOSECOLS, CHOOSEROWS and INDEX as well.
An attentive reader might be wondering therefore, why I didn’t just iterate over the source range with MAKEARRAY and fill all the values with INDEX, except where AND(x=ROW(),y=COLUMN()).
Well, I did that too. Much less interesting and 3-5 times slower, depending on the size of the source range.
=LAMBDA(range,
LET(
r, ROW()-MIN(ROW(range))+1,
c, COLUMN()-MIN(COLUMN(range))+1,
MAKEARRAY(
ROWS(range),
COLUMNS(range),
LAMBDA(x,y,IF(AND(x=r,y=c),"",INDEX(range,x,y)))
)
)
)