Forum Discussion
Is there a way to work around Circular reference in Dynamic Array?
Hi! I am trying to convert a table into a dynamic array,
but the rows in the table is referring to other column's previous row value, so when I try to convert it into a dynamic array,
it'll become a circular reference.
Is there a way to work around it?
I attached the example excel file to this post for your reference.
This is just a simplified example of the actual table I am trying to convert.
I tried the "Iterative Calculation" option , the calculation will be correct.
But the problem is, the original excel table in my case is very complicated, so even if I set the "Max calculate times" to 10,000, the calculation can not be finished, leaving at least half of the values wrong.
My alternative approach is to solve this half dynamically. But I consider it ugly...
Leaving only col1 dynamic, and other columns will have to fill down the formula to "row 1048576", which is not pretty.
(col1 is actually referring to other places in the real case)
Is there a way to work around this and make the table fully dynamic?
Thank you!
Best Regards,
Alan
One way to achieve this is to utilize a CHOOSE based lambda function to store and recall multiple items at each iteration of SCAN, then MAP the results to return each individual item. For example:
=LET( val, LAMBDA([a],[b], LAMBDA(x, CHOOSE(x, a, b))), num, SEQUENCE(, 2), arr, SCAN(val(), Table1[Col1], LAMBDA(acc,one, LAMBDA(two, val(two, IF(two < 0, 10, 5)))(IF(one < 0, one + acc(2), 0)))), MAP(IFNA(arr, num), IFNA(num, arr), LAMBDA(f,x, f(x))) )Replace Table1[Col1] with the reference to your dynamic column 1 as needed.
4 Replies
One way to achieve this is to utilize a CHOOSE based lambda function to store and recall multiple items at each iteration of SCAN, then MAP the results to return each individual item. For example:
=LET( val, LAMBDA([a],[b], LAMBDA(x, CHOOSE(x, a, b))), num, SEQUENCE(, 2), arr, SCAN(val(), Table1[Col1], LAMBDA(acc,one, LAMBDA(two, val(two, IF(two < 0, 10, 5)))(IF(one < 0, one + acc(2), 0)))), MAP(IFNA(arr, num), IFNA(num, arr), LAMBDA(f,x, f(x))) )Replace Table1[Col1] with the reference to your dynamic column 1 as needed.
OMG! This is mind blowing! Never thought that Choose can be used like this! EPIC!!
Thank you very much!You're welcome.
Alternatively, for this specific scenario, since the flow of the 2 dynamically calculated columns is row-major order (left-to-right, top-to-bottom), the following would also work:
=SCAN(0,EXPAND(Table1[Col1],,2),LAMBDA(a,v,IF(ISNA(v),IF(a<0,10,5),IF(v<0,v+a,0))))EXPAND is used to add a second column to Table1[Col1], which is filled with #N/A errors because the optional [pad_with] argument is not specified. This is then used to determine which formula to evaluate by testing if the current value ISNA.
Cheers!
