Forum Discussion
Weird Results from Array Manipulation - Any ideas on why?
Thank you both for replying. I think djclementes did a better job of mind reading what I wanted in response to a vague question. I might be overreading the response, but it almost sounds like choose is a sort of syntactic sugar around "if". If true, this may be the end of my efforts around what I have been doing involving trying to freely pass arrays from a choose "encapsulation" (multi-dimensional array as one possible choice in a choose lambda) and a "stuffed" array (multi-dimensional array as only possible choice in a choose lambda) in a single dimensional array (like a column) that is then "stuffed" into a list.
Basically anything of the form lambda(a1, ...,a10, lambda(choice, choose(choice, a1, ... , a10)) lets you pass around any desired arrays without "flattening" them. (What I am calling encapsulation.) The current explanation about passing an array to the "choice" variable in a choose function means that even if they are not flattened when stored, there are still ways to inadvertently get excel to flatten them. I'm not quite sure how passing a sequence as the choice variable could result in a jagged array, but I am sensing that an array function is done more "at the same time" than I was imagining.
I'm still not sure why things would be evaluated (and compared) prior to the full return of the function, i.e.
The first array value of 1 (in the bycolumn or sequence(2) reference) should create this formula:
lambda(choice, choose(choice, many(1)))
Shouldn't this mean that content of the sequence/bycol returns is an array of dimensionless functions? e.g. {lambda(x, choose(x, "b")); lambda(x, choose(x, sequence(4,1,5))}
And shouldn't picking out one such function, e.g. index(row_of_functions, 1,1) just return a dimensionless function?
Why would excel know/care about what the content of a non-evaluated function is when constructing the row of functions?
Consider, for instance, this formula that does not conflate the array creation with evaluation of the embedded function:
=LET(
array,IF({TRUE,FALSE}, "a", LAMBDA(x, SEQUENCE(x))),
evaluate_embedded_array_formula, INDEX(array, 1, 2)(3),
return_singleton, INDEX(array, 1,1),
HSTACK(return_singleton, evaluate_embedded_array_formula))