Forum Discussion

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 03, 2023

VijayVardhan 

THUNKS

1]  To an extent the answer is 'because that is the way it was implemented'.  In normal use, INDEX is applied to a range and the result is also a range object, be it a row, column or cell.  Once the function is applied to an array, the rules change.  To return a multi-element array, the indices need to be explicit, for example

= INDEX(array, {3;2;1}, 5)

Now Lambda functions are first class objects that may be included as parameters or returned as the result of a formula, the rules seem to have changed again and both row and column parameters are required even if one has to be 1.

 

2] Moving to the next issue,

= INDEX(sumsArray, 4, 1)

is valid, but it returns a Lambda function (specifically a thunk) and not an array of values.  Lambda functions are only evaluated when the necessary parameters are provided.  In the case of the Thunk, no parameters are required but the evaluation is then triggered by the null parameter string:

= INDEX(sumsArray, 4, 1)()

You could even split the calculation in to two parts

= LET(
    arrϑ, INDEX(sumsArray, 4, 1),
    arrϑ()
  )

if you so chose.

 

The main limitation that is being addressed by the use of thunks is that (as currently implemented) Lambda helper functions are not capable of returning arrays of arrays without contorted workarounds.  Given that I would define the modern spreadsheet as an environment for manipulating multi-dimensional arrays as 2D arrays of arrays, this is an appalling limitation.  Most of the formulas I write require the array of arrays, quite often at multiple points within a given formula.

 

Note: Traditional spreadsheets approach the problem differently by working from the cell upwards.  Arrays are displayed by writing 10s of 1000s of individual scalar formulas that reproduce the appearance of the result array without any explicit recognition given to the structure of the data being manipulated.

 

  • VijayVardhan's avatar
    VijayVardhan
    Copper Contributor
    Dec 04, 2023

    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.

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Dec 04, 2023

      VijayVardhan

      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!

      • VijayVardhan's avatar
        VijayVardhan
        Copper Contributor
        Dec 10, 2023

        @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?

Resources