Forum Discussion
When #VALUE! isn’t an error: The hidden reference behavior of Excel’s dynamic arrays
1. Mechanism Overview Functions such as TAKE, DROP, OFFSET, and INDIRECT normally produce a range when supplied with scalar parameters. When instead a 1-D or 2-D numeric array is passed to one of th...
That makes my routine use of thunks look laboured!
= LET(
nestedRangesϑ, MAP({1;2;3;4}, LAMBDA(k, THUNK(TAKE(range, k)))),
MAP(nestedRangesϑ, SUMϑ)
)
where
SUMϑ = LAMBDA(ϑ, SUM(ϑ( )));I seem to remember from the past OFFSET creating usable entities that would be errors on the grid. One trick I have used in the past is range intersection which might be used to pick a specific field from a record.
Combining the best of both worlds, we could simplify the beginning to:
nestedRangesϑ, MAP(TAKE(range, {1; 2; 3; 4}), THUNK)