"Extract formulas from the grid into a function" doesn't work

[se16n]

Book1.xlsx Hello,

Please see an attached Excel file with cases demonstration.

Could you please explain why this 2 errors are appearing? I don't get it how can I convert this type of cases into a lambda.

The reference to D21 in cell F21 cannot be imported into a function, because it is used by the "#" operator which requires a reference to a grid position, so these cells cannot be moved off the grid into a function

The reference to J7:J16 in cell K7 cannot be imported into a function, because it is used by the SUMIF function which requires a reference to a grid position, so these cells cannot be moved off the grid into a function

[jack-williams]

Thanks for the feedback. These are caused by some limitations which our initial version does not handle:

• The grid -> function feature does not support dynamic arrays currently, which stems from how we do the conversion. This is not a fundamental limitation, but a gap in capability.
• The SUMIF limitation is caused by the fact that SUMIF must take a range, but when we roll this up into one formula it will be a LET parameter instead. We could convert the SUMIF to an equivalent formula that uses SUM/FILTER, but we do not have that in this version.

I cannot make any guarantees about fixing this, but I'll keep this open to gather any more feedback and track it.

[se16n]

Thank you for your prompt response Jack.

The grid -> function feature does not support dynamic arrays currently, which stems from how we do the conversion. This is not a fundamental limitation, but a gap in capability.

Great news!

Just wanted to add a more context why is this even important to me (and I hope other people can find it useful as well). At current Excel state its impossible to debug LAMBDA/LET using F9 which is very restricting and inconvenient. What I do instead is I create an intermediate column for each LET binding and then use AFE to create a LAMBDA out of these steps, it is kind of Elastic Sheet-Defined Functions described by the Calc Intelligence back in 2018. As you can imagine inability to transforming dynamic formula grid references into LET bindings is at least limiting.

By the way, an opposite operation: unwrapping LAMBDA/LET to grid would be helpful, because it is not uncommon to see LAMBDAs/LETs with Nth of lines with nested Excel functions within LET steps, again F9-based debugger is simply not working.

The SUMIF limitation is caused by the fact that SUMIF must take a range, but when we roll this up into one formula it will be a LET parameter instead. We could convert the SUMIF to an equivalent formula that uses SUM/FILTER, but we do not have that in this version.

If this issue is only related to the functions expecting a range instead of array then I think it is just fine current behavior as is because this can be easily workarounded. Maybe to change the error message text though, so it is clear that certain functions expect range.

Thank you.