Dynamic Let Function

Question

If you have a long let function with lots of variables, it is annoying to audit. It is also a shame that all the other info in the let function is "trapped" without manually going in to change the item ment to be returned.

Here is a fun way to solve both problems but it has one thing missing that perhaps someone from the community will be able to figure out.

If you create an item_array and an item output, you can use a relative reference to a cell to ouput any item in the let function. This lets you copy and past with reference to the changing variable to get the changing output.

My question is how can I make the `item_output` simply replicate the `item_array" without having to retype each item for the choice function. I tried textsplit to generate the second argument of CHOOSE but CHOOSE treats the whole array as an item and the output is the variable name rather than the data. Seems like INDEX or CHOOSE or CHOOSECOLS or something should work but I had no luck making them work. Perhaps someone will have an idea?

data_junky · Answer

Many thanks Peter."The question then is, would the CHOOSE statement remain once the formula is debugged or should it be removed."&nbsp; You could remove the CHOOSE statement but there is no need especially if the goal isn't simply to debug. You may want to pull out different bits of information from this let function based on changing parameters in your spreadsheet.Your thunk idea is very intriguing.&nbsp;&nbsp;&nbsp;

m_tarler · Answer

I think what you want is to have the "index_num" to be an array like: XMATCH(item_array,item_array)

but the problem is that it is a column of values and the 'item_array' is a column so it will only show the one value.

In the attached I created a version with XMATCH(TOROW(item_array),item_array), and that does produce the array output but because the other values are single values it repeats them for the 'array' so the grid is 'ugly'.

Since you are manually adding the list of variables why not just spit them out using something like:
IFERROR(VSTACK({"a","b","c","d","e","f","item","item_array","index_num"},
HSTACK(a,b,c,d,e,f,item,item_array,index_num)),""))

But then maybe I don't understand your goal.

Dynamic_Let.xlsx13 KB

peterbartholomew1 · Answer

I don't think there is any elegant way of collecting all the LET variables together so that any one may be selected using a formula. CHOOSE is a good option but relies upon the developer inserting a list of parameters that correspond to the variables used within the LET function. The question then is, would the CHOOSE statement remain once the formula is debugged or should it be removed?

Another approach that I have started playing with is to build a data dictionary, comprising pairs of values, the first being a variable name (text) and the second its value, as a thunk (a function that is evaluated by providing it with an empty parameter string).

Working with Arrays of Ranges | Microsoft Community Hub

My intention was to allow a LAMBDA function to return two or more arrays that can be referenced as required for further calculation or presentation. The type of situation I had in mind was a function that returns an array of 'opening cash' amounts and the 'opening revolver debt'.

The relevance to this discussion is that it would be possible to load all the variables used within a LET function to such a data dictionary (possible, but not necessarily pretty) and, from there, examined by name or by index.

selahmartinez · Answer

Thanks for the link.

peterbartholomew1 · Answer

Following my previous reply, I have now put a demonstrator together to illustrate the idea of using a (home spun) data dictionary to monitor the functioning of a LET/LAMBDA formula/function. It is still very much 'work in progress' though.

Forum Discussion

Dynamic Let Function

7 Replies

Resources