How to assign multiple discontinuous cells to a LET parameter?

[Rnkhch]

Hello,

I know I can assign a single cell or a continuous range of cells to a LET parameter as below:

=LET(x,A1,operation...)
=LET(x,A1:M1,operation...)

But how would I assign multiple discontinuous cells to the x parameter? I'm hoping I can do something like this:

=LET(x,{A1;B6;D23;G12},operation...)

Thanks for any input!

Edit: I meant to say LET parameter in the title, but there is no way of editing it now. If possible, it would be great if one of the forum administrators could fix this; thank you

 

[jasonb75]

I just tried something quickly not expecting it to work, but it looks like it does.

Note that this is for numeric data in the 'value' cells. For text you would need to change the MATCH part to MATCH(REPT("z",255),x)
If you have mixed data (some cells numeric, others text) then it will be a little more complicated but still possible.
These methods will return errors in the array cells as long as there is a non error cell later in the array (errors after the last non error cell will not be picked up). I don't think that there will be a way to get around that.

=LET(x,CHOOSE(SEQUENCE(5),A1,B1,C1,D1),a,MATCH(1E+100,x),INDEX(x,SEQUENCE(a)))

 

[Rnkhch]

Very interesting! Thank you!

I just wrapped your function in a LAMBDA like this (for now I put 8 parameters only, but will increase in the finalized version hopefully ):

MC=LAMBDA(cell001,[cell002],[cell003],[cell004],[cell005],[cell006],[cell007],[cell008],LET(x,CHOOSE(SEQUENCE(8),cell001,cell002,cell003,cell004,cell005,cell006,cell007,cell008),a,MATCH(1E+100,x),INDEX(x,SEQUENCE(a))))

And it seems to be to working so well:

- It reproduces numbers, texts, and errors.
- It doesn't reproduce blanks; it substitutes blanks with zeros.
- There are trailing zeros for all non-specified (non-selected) parameters

Now, I'm trying to see if I can make it faithfully reproduce blanks and also if I can get rid of the trailing zeros . Curious what you think

 

[jasonb75]

- It doesn't reproduce blanks; it substitutes blanks with zeros.

Are all of the cells going to be numeric / errors in the final product?

What you are seeing is a byproduct of the way that excel handles empty cells. An empty cell is blank for text purposes and has a value of zero for numeric purposes.
Functions that will work with both numbers and text will default to numeric value.

It should be fixable (much easier if you were only working with text entries) but we need to be sure to use the correct approach based on the data that you will be working with, otherwise the fix for this will likely break something else.

I'm not sure that I'm following what you're seeing with the trailing zeros, perhaps that is a problem specific to LAMBDA (not something I've looked at much so far).
When you say 'Non-selected' parameters do you mean left empty in the LAMBA function, or am I barking up the wrong tree here?

I'm going offline in about 10 minutes or so (nearly 1AM here) but if you can give me as much info as possible to work with then I'll play around with some ideas in the morning ans see what I can come up with.

 

[Rnkhch]

So, I'm including an example XL2BB which demonstrates what is happening:

I have one number, two texts, two (real) errors, one real blank, and one formula blank in the range of B2:B8. I used the MC LAMBDA function to select the same seven cells discontinuously, and the spill is in eight cells C2:C9.

You can see that the numbers, texts, errors, and formula blanks are reproduced successfully. The only data type that is not reproduced successfully is the real blanks (C4). And cell C9 represents the trailing zero I was talking about which comes from the unused LAMBDA parameter (cell008).

In response to your questions:

Are all of the cells going to be numeric / errors in the final product?

Ideally the formula should handle any data type and it's almost there (with the exception of real blanks). So the real sheet is gonna have a mix of all these data types

perhaps that is a problem specific to LAMBDA

Yes, the trailing zeros only appear when using LAMBDA

When you say 'Non-selected' parameters do you mean left empty in the LAMBA function

Yes, which in this example would be cell008

I'll play around with some ideas in the morning

Thank you. Please take your time, and I look forward to continuing this exciting discussion

MC.xlsx
	A	B	C	D	E	F	G	H
1
2		42.986	42.986
3		a	a
4			0
5		#N/A	#N/A
6
7		#DIV/0!	#DIV/0!
8		Hi!	Hi!
9			0
10
11
12
Sheet2

Cell Formulas
Range	Formula
C2:C9	C2	=MC(B2,B3,B4,B5,B6,B7,B8)
B5	B5	=LOOKUP(F2,F3:F4)
B6	B6	=IF(ISNUMBER(A1),1,"")
B7	B7	=2.784/0
Dynamic array formulas.

 

[jasonb75]

The only data type that is not reproduced successfully is the real blanks (C4).

The 'real blanks' as you refer to them are often problematic in formulas and this appears to be no different. When you return a blank with a formula, that cell contains a null string (a true blank in text form). When the cell is empty (the real blank) effectively has 3 different values at the same time, (note that this may not be accurate in terms of technical fact but hopefully will be helpful in terms of understanding what is happening).
To simple formulas that count cells with content (e.g. COUNT and COUNTA) the cell is empty, there is nothing to count. For any functions that perform numeric calculations, or those that can be used with numbers or text the empty cell has a value of 0. For functions that can only be used with text it is the same as a formula blank.

For the empty cell to be treated specifically as a blank you would need to check it before anything is done with it and convert it to a formula blank it it is empty, IF(ISBLANK(cell),"",cell)
Naturally, this may not always be the best option and with what you are doing it needs to be done with each individual cell before using CHOOSE to make an array from the individual cells.
With a single continuous range it could be done as an array. IF(ISBLANK(cell1:cell2),"",cell1:cell2), in either case this needs to be the first step in the formula, any calculations done before this test would mean that the cells have the numeric value of zero and are no longer blank.

C9 represents the trailing zero I was talking about which comes from the unused LAMBDA parameter (cell008)

It appears that the unused optional parameters in LAMBDA behave the same way as empty cells in that they default to 0 if there is no specific instruction in the formula to say different. As with the blanks, there is a way to correct this by testing to see if the argument is used at an early stage in the function. Unfortunately it needs to be done on each individual argument, there is no way of putting this in to an array to test them all together. ISOMITTED(cell) returns TRUE for the unused parameters.

Putting it all together for a LAMBDA function, it comes out as shown in the mini sheet below. Note that the SEQUENCE part starts with the maximum number of arguments in the LAMBDA function, then subtracts the unused ones from it based on the results of ISOMITTED(cell).

Note that the results of the formula are not in the same order as the original cells, I deliberately changed the order in which the cells were entered into the function call in order to be sure that it still worked correctly when the cells were not in a continuous range.

Hopefully I haven't missed anything.

lambda.xlsx
	A	B	C
1			Formula2
2		42.986	a
3		a
4			42.986
5		#REF!	Hi!
6			#REF!
7		#DIV/0!
8		Hi!	#DIV/0!
Sheet5

Cell Formulas
Range	Formula
C2:C8	C2	=LAMBDA(cell001,[cell002],[cell003],[cell004],[cell005],[cell006],[cell007],[cell008],LET(s,SEQUENCE(8-ISOMITTED(cell002)-ISOMITTED(cell003)-ISOMITTED(cell004)-ISOMITTED(cell005)-ISOMITTED(cell006)-ISOMITTED(cell007)-ISOMITTED(cell008)),x,CHOOSE(s,IF(ISBLANK(cell001),"",cell001),IF(ISBLANK(cell002),"",cell002),IF(ISBLANK(cell003),"",cell003),IF(ISBLANK(cell004),"",cell004),IF(ISBLANK(cell005),"",cell005),IF(ISBLANK(cell006),"",cell006),IF(ISBLANK(cell007),"",cell007),IF(ISBLANK(cell008),"",cell008)),x))(B3,B6,B2,B8,B5,B4,B7)
B5	B5	=LOOKUP(#REF!,#REF!)
B6	B6	=IF(ISNUMBER(A1),1,"")
B7	B7	=2.784/0
Dynamic array formulas.

 

[Rnkhch]

Thanks for all the work and explanation! I tested the updated LAMBDA in various conditions, and it is working well, so I changed the solution to your post #15

Now we have a generic way of conveniently selecting multiple discontinuous cells in other formulas.

A particular example that initially dragged me into this idea was IF(ISNUMBER), which works with one cell, and I had to write AND-based lengthy statements for more cells such as IF(AND(ISNUMBER(),ISNUMBER(),etc.),,). Now I can simply write: IF(ISNUMBER(MC(cell1,cell2,etc.)),,)


I updated the LAMBDA by increasing the number of parameters to 20 and by condensing it through two additional LAMBDAs to shorten the ISOMITTED() and IF(ISBLANK()) statements as follows:

IO=LAMBDA(cell,ISOMITTED(cell))
IB=LAMBDA(cell,IF(ISBLANK(cell),"",cell))

MC=LAMBDA(cell001,[cell002],[cell003],[cell004],[cell005],[cell006],[cell007],[cell008],[cell009],[cell010],[cell011],[cell012],[cell013],[cell014],[cell015],[cell016],[cell017],[cell018],[cell019],[cell020],LET(s,SEQUENCE(20-IO(cell002)-IO(cell003)-IO(cell004)-IO(cell005)-IO(cell006)-IO(cell007)-IO(cell008)-IO(cell009)-IO(cell010)-IO(cell011)-IO(cell012)-IO(cell013)-IO(cell014)-IO(cell015)-IO(cell016)-IO(cell017)-IO(cell018)-IO(cell019)-IO(cell020)),x,CHOOSE(s,IB(cell001),IB(cell002),IB(cell003),IB(cell004),IB(cell005),IB(cell006),IB(cell007),IB(cell008),IB(cell009),IB(cell010),IB(cell011),IB(cell012),IB(cell013),IB(cell014),IB(cell015),IB(cell016),IB(cell017),IB(cell018),IB(cell019),IB(cell020)),x))

I thought to post this in the LAMBDA section of the forum so other people can easily find and use it too. But since its primarily your work, I didn't want to take credit for it. So perhaps you can post it there if you get a chance? Thanks

 

[jasonb75]

I thought to post this in the LAMBDA section of the forum so other people can easily find and use it too.

Please feel free to post it there as you wish. The base of the idea was yours and you will be able to explain the purpose better than I could.

 

[Rnkhch]

I posted the LAMBDA, which can be found here :

MC

MC allows for conveniently selecting multiple discontinuous cells as a single parameter within the syntax of formulas MC stands for "multiple cells". Many functions allow for selecting one cell or a continuous range of cells, and attempting to select multiple discontinuous cells through commas...

www.mrexcel.com