Forum Discussion
THUNKS
Do you mean that if Lambda function is included in the array then one of the parameter that is either the row or the column argument of the INDEX function must be 1 to produce the output without throwing any error?
Is this the double Lambda setup is called as thunk or is it the double Lambda's wrapped inside BYROW function is called as thunk? Can you explain more about this thunk concept as without understanding of which it's difficult to understand the answers of the questions and to get the hang of the formula.
How is that the formula INDEX(sumsArray, 4, 1) returns a Lambda function and not an array of values consequently requiring a null parameter string? I see that sumsArray = Double Lambda's wrapped inside BYROW function and BYROW function fulfills the parameter passing requirement of Lambda function by feeding 'data' as parameter to the double Lambda's.
I regard a Thunk as a special case of a named Lambda function that requires no parameters but returns a predefined array when called with an empty parameter string. A single Thunk can be created by including the array as the final (and only) parameter to LAMBDA. It is also possible to pass the array as a parameter by using a further LAMBDA.
Thunkλ
= LAMBDA(x, LAMBDA(x))
This may not look useful but it does allow and array of Thunks to be built using a Lambda helper function such as BYROW.
Why might one use an array of Thunks? An example might be to create an array of range references, something that Excel will not allow. Instead, one may use an array of Thunks, anyone of which may be expanded to return a range reference. For example
tablesϑ
= VSTACK(
Thunkλ(Table1),
Thunkλ(Table2),
Thunkλ(Table3)
)
represents an array of 3 elements each of which is a thunk. Where things get interesting, is that the array can be used as an argument in a normal Excel function such as XLOOKUP. Thus
= LET(
selectedTblϑ, XLOOKUP(selectYr, years, tablesϑ),
selectedTblϑ()
)
The first line of code returns a single thunk and the second expands it to return a range reference to the data within a table which may be on any sheet of the workbook.
Alternatively
= REDUCE(headers, tablesϑ,
LAMBDA(acc,tblϑ, VSTACK(acc, tblϑ()))
)
will stack the contents of all three tables vertically as a single dynamic array.
I don't know whether this helps, but it may give a flavour of what is possible!
@PeterBartholomew
PeterBartholomew1
Peter, you said when we change from range reference to an array then argument specified must be either 1 or 0 and empty argument is invalid or unacceptable however I see this is not the case as I have given an array to INDEX function in the formula below and it returns desired outcome even if I keep column argument empty.
INDEX (({1; 2; 3; 4}*4/2)+2, 3) this returns 8 or
INDEX ((B1:B4*4/2)+2, 3) returns 8.
This is contrary to what you have said, will you please explain.
Next you said if the array contains the LAMBDA then empty or even 0 argument is not acceptable. It has to be 1 representing the row or column of the array. Which kind of LAMBDA array are you talking about? Is it the array returned by the LAMBDA function or the array of the LAMBDA’s that is array of the thunks for which INDEX argument must be 1 and cannot be empty or 0?Apologies. You appear to be correct. I had memory of arrays not returning the same result as ranges when called with a parameter omitted but that appears not to be the case. In the formula
= INDEX({10,20,30;40,50,60;70,80,90}, {1;2;3}, {1,3})omitting the second parameter no longer returns whole columns, but that is true of a range reference too. The point with the Thunks was simply as you had noted
= LET( Arrayϑ, BYROW(array, LAMBDA(x, LAMBDA(x))), Elementϑ, INDEX(Arrayϑ, 2), Elementϑ() )fails with a #REF! error whereas
= LET( Arrayϑ, BYROW(array, LAMBDA(x, LAMBDA(x))), Elementϑ, INDEX(Arrayϑ, 2, 1), Elementϑ() )works. What is annoying is that
= LET( Arrayϑ, BYROW(array, LAMBDA(x, LAMBDA(x))), MAP(Arrayϑ, LAMBDA(Elementϑ, Elementϑ())) )should just return the original array but instead it errors, complaining that nested arrays (the intended result) are not allowed.
PeterBartholomew1
Let’s assume, No of rows = m and no of columns = n, m > 1 and n > 1.
For either single column range or array input, giving column no argument of INDEX function as 1 or 0 or omitted is acceptable. For 2D range of m X n, omitting the column no argument results into #REF! error whereas for 2D array of m X n, it returns either entire row or first element of the row depending upon whether or not you are enclosing the row no argument inside curly braces. Logic behind not throwing #REF! error for 2D array input with column no argument omitted is unknown or the Microsoft is only privy to. For a single column of Thunks as an argument for array parameter of INDEX function, column no argument cannot be 0 or omitted and must be 1 to select a single Thunk from the array of Thunks. They could have programmed to allow 0 or omitted but they didn’t.
----------------------------------------------------------------------------------------------
I see Thunk as facility for packing and storing the array of raw or processed data.
I don’t understand why even single Thunk packed by BYROW function is required to be selected by INDEX function before it is unpacked or released. This sounds very illogical and incomprehensible. For example,
LET (THUNK, BYROW ({1, 2, 3}, LAMBDA (x, LAMBDA (x))), THUNK()).
This fails to release the THUNK and spill the array on the sheet. For this we need to select the Thunk with INDEX function first before we unpack and spill it on the sheet as is in the below code.
LET (THUNK, BYROW ({1, 2, 3}, LAMBDA (x, LAMBDA (x))), INDEX (THUNK,1, 1)()).
----------------------------------------------------------------------------------------------
MAP function can return only one element (single cell value) for each element passed to it. Hence it fails to return n elements of 1 X n array (Packed inside one Thunk by BYROW) for each Thunk passed by it.
LET (THUNK, {1, 2, 3}, BYROW (array, LAMBDA (x, LAMBDA (x))),
MAP (THUNK, LAMBDA (Elementθ, Elementθ()))) fails to return or unpack and spill the 3 elements of 1 X 3 array packed in the single Thunk by BYROW.
LET (THUNK, {1, 2, 3; 4, 5, 6}, BYROW (array, LAMBDA (x, LAMBDA (x))),
MAP (THUNK, LAMBDA (Elementθ, Elementθ()))) fails to return 2 X 3 array from two Thunks each containing 1 X 3 array.
We must make sure that we pass only single cell value to MAP function for each element passed by MAP to LAMBDA in order to get all the elements of m X n array packed inside m no of Thunks by BYROW function. Each Thunk containing 1 X n array. Following formula takes care of this:
LET (THUNK, {1, 2, 3; 4, 5, 6}, BYROW (array, LAMBDA (x, LAMBDA (x))),
MAP (THUNK, LAMBDA (Elementθ, INDEX (Elementθ(), 1, COLUMN(A1)))))
You need to drag this formula around till you get all the elements of m X n array.
----------------------------------------------------------------------------------------------
Same is the case with MAKEARRAY. It can only return a single cell value for each (row, column) argument that it passes to LAMBDA. It cannot return n elements of 1 X n array (packed by BYROW into a single Thunk) for one (row, column) argument that it passes to LAMBDA hence following formula fails to spill second Thunk from a 2 X 1 array of Thunks (THUNK) containing two Thunks in a single column.
LET (THUNK, {1, 2, 3; 4, 5, 6}, BYROW (array, LAMBDA (x, LAMBDA (x))),
MAKEARRAY (2, 1, LAMBDA (m, n, INDEX (THUNK, m, n)()))).
We need to make sure that we return a single cell value to MAKEARRAY for each (row, column) argument that it passes to LAMBDA so that it will be able to return or spill that single value on sheet. This is taken care of by following formula:
LET (THUNK, {1, 2, 3; 4, 5, 6}, BYROW (array, LAMBDA (x, LAMBDA (x))),
MAKEARRAY (2, 3, LAMBDA (m, n, INDEX (INDEX (THUNK, m, 1)(), 1, n)))).
----------------------------------------------------------------------------------------------
Obviously as a renowned formula guru of this forum you must be cognizant about all this. I just wanted to post my comprehensions. Thanx very much for enlightening me about the concept of Thunk.