MC

[Rnkhch]

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 or {} results in syntax error. MC allows for selecting multiple discontinuous cells without verbose coding. Thus the code remains concise and almost identical to the functions intended syntax/parametric structure. The result of the MC function is a spill which is identical in size to the number of supplied cells. MC faithfully reproduces all types of data including, numbers, texts, errors, formula blanks, and real blanks. I have set the max number of supplied cells to 20, but this can be easily increased to the desired number.

For instance, supplying five discontinuous cells to the ISNUMBER() function can easily be done as ISNUMBER(MC(A1,C8,G12,H18,K4))

=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
    )
)

In this code, I have used two simple additional LAMBDAs to keep the syntax of MP more concise and clean:

IO=LAMBDA(cell,ISOMITTED(cell))

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

Book1
	A	B	C	D	E	F	G	H
1		Original cells	MC spill	ISNUMBER with MC
2		42.986	42.986	TRUE
3		a	a	FALSE
4				FALSE
5		#N/A	#N/A	FALSE
6				FALSE
7		#DIV/0!	#DIV/0!	FALSE
8		Hi!	Hi!	FALSE
9
10
11
12
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=MC(B2,B3,B4,B5,B6,B7,B8)
D2:D8	D2	=ISNUMBER(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.

Please feel free to share any thoughts or suggestions.

This work is primarily done by Jason (jasonb75), and the discussion can be found at the following thread:

How to assign multiple discontinuous cells to a LET parameter?

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...

www.mrexcel.com