Is it possible to make TYPE and CELL functions to work on a selected range?

[Rnkhch]

Hello,

Just wondering if it is possible to make the TYPE and CELL functions work on a selected range, such that the output would be a spill corresponding to each of the cells (and not the entire selection) within the selected range. Here is an example of what I mean exactly:

Blank power workbook1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1										44750	144750	44750
2		a	n	t	5	3	g	6	TRUE	#DIV/0!		t	?
3		7.9	#N/A	#SPILL!	FALSE	h		89	book	8-Jul	8	i	TRUE
4
5	individual cells dragged:	2	2	2	1	1	2	1	4	16	1	2	2
6		1	16	16	4	2	2	1	2	1	1	2	4
7
8	selected range:	64
9
10
11	individual cells dragged:	l	l	l	v	v	l	v	v	v	b	l	l
12		v	v	v	v	l	l	v	l	v	v	l	v
13
14	selected range:	l
15
16
Sheet1

Cell Formulas
Range	Formula
K1	K1	=TEXT(J3,10)
J1	J1	=VALUE(J3)
J2	J2	=1/0
C3	C3	=NA()
D3	D3	={1,2,3}
G3	G3	=IF(C1>2,1,"")
B5:M6	B5	=TYPE(B2)
B8	B8	=TYPE(B2:M3)
B11:M12	B11	=CELL("type",B2)
B14	B14	=CELL("type",B2:M3)

Here, B8 returns the type of the entire selection (which is of course its expected job), but would it be possible to make the formula in B8 to spill the same results as in B5:M6? And similarly, would it be possible to make the formula in B14 to spill the same results as in B11:M12?

Thanks for any input

 

[smozgur]

Not directly as far as I know that TYPE and CELL functions cannot spill.

However, perhaps using the MAKEARRAY function as shown below (being not sure about the performance):

Blank power workbook1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1										44750	144750	44750
2		a	n	t	5	3	g	6	TRUE	#DIV/0!		t	?
3		7.9	#N/A	#SPILL!	FALSE	h		89	book	44750	8	i	TRUE
4
5	individual cells dragged:	2	2	2	1	1	2	1	4	16	1	2	2
6		1	16	16	4	2	2	1	2	1	1	2	4
7
8	selected range:	2	2	2	1	1	2	1	4	16	1	2	2
9		1	16	16	4	2	2	1	2	1	1	2	4
10
11	individual cells dragged:	l	l	l	v	v	l	v	v	v	b	l	l
12		v	v	v	v	l	l	v	l	v	v	l	v
13
14	selected range:	l	l	l	v	v	l	v	v	v	b	l	l
15		v	v	v	v	l	l	v	l	v	v	l	v
Sheet1

Cell Formulas
Range	Formula
K1	K1	=TEXT(J3,10)
J1	J1	=VALUE(J3)
J2	J2	=1/0
C3	C3	=NA()
D3	D3	={1,2,3}
G3	G3	=IF(C1>2,1,"")
B5:M6	B5	=TYPE(B2)
B8:M9	B8	=MAKEARRAY(ROWS(B2:M3),COLUMNS(B2:M3),LAMBDA(r,c,TYPE(INDEX(B2:M3,r,c))))
B11:M12	B11	=CELL("type",B2)
B14:M15	B14	=MAKEARRAY(ROWS(B2:M3),COLUMNS(B2:M3),LAMBDA(r,c,CELL("type", INDEX(B2:M3,r,c))))
Dynamic array formulas.

 

[Rnkhch]

Thank you so much! This is exactly what I was looking for and thinking about.

(Before your response, I was thinking more about how to generate a "sequence of functions" similar to how SEQUENCE makes a sequence of numbers, and your solution is a perfect demonstration of that.)

 

[smozgur]

Thank you so much! This is exactly what I was looking for and thinking about.

You're welcome. I am glad to hear it helps.

Thanks for the feedback.

 

[Rnkhch]

One question: when I use your formula in LET, like this:

=LET(range,B2:M3,MAKEARRAY(ROWS(range),COLUMNS(range),LAMBDA(r,c,CELL("type", INDEX(range,r,c)))))

then I get a spill of errors.

How can I correct this scenario? Thanks much!

Also wondering why the same spill of errors doesn't happen with TYPE, when using LET? (It only happens with CELL)

 

[smozgur]

You are right.

Honestly, I will likely continue thinking about this tomorrow with fresh mind but I really don't have any logical explanation for this. It should be something "special" about the CELL function.

 

[Rnkhch]

Sure, thanks much. Hope you can figure out a solution for the error spill.

 

[Rnkhch]

I have been looking a lot into this, and so far my understanding is that the reason for the error for CELL in my post #5 is that the LET that I defined is outside of the MAKEARRAY's LAMBDA, so this internal LAMBDA does not recognize the external LET's parameters and definitions. I tested this by putting a LET inside the LAMBDA and it worked fine:

=MAKEARRAY(ROWS(B2:M3),COLUMNS(B2:M3),LAMBDA(r,c,LET(range,B2:M3,CELL("type", INDEX(range,r,c)))))

which is of course not helpful, but shows what is happening.

And the reason TYPE worked with the outside LET is that TYPE works with ranges anyways, so it never even saw the variable from the outside LET even though that variable was defined.

So now the big question is how to supply a variable from the outside LET of my post 5 into the internal LAMBDA of MAKEARRAY because my goal is to put the MAKEARRAY inside a LET which will itself be inside the main LAMBDA that I'm coding for.

 

[Fluff]

The Makearray function can see the variables in the original Let function, the problem is more likely to be that the Range once passed to Makearray becomes an array.

 

[Rnkhch]

I see, thank you.

I attempted to "reconstruct" the "range" through INDIRECT as follows, based on one of the solutions from my other thread:

Is it possible to generate a string from the syntax of a parameter?

Hello, Would it be possible to output a string corresponding to the syntax of a parameter? Here is an example: =LET(x,A5:H5,operation...) Would it be possible to have a function in place of "operation" that would get the string output of the second parameter's syntax as "A5:H5" (without the...

www.mrexcel.com

=LET(range,B2:M3,MAKEARRAY(ROWS(range),COLUMNS(range),LAMBDA(r,c,CELL("type", INDEX(INDIRECT(TAKE(ADDRESS(ROW(range),COLUMN(range),4),1,1)&":"&TAKE(ADDRESS(ROW(range),COLUMN(range),4),-1,-1)),r,c)))))

I assumed that an INDIRECT-generated range should still be a range rather than an array, though not sure if this a correct assumption?

Unfortunately, the Evaluate Formula window turns blank exactly at the time that it tries to evaluate the INDIRECT portion so I cannot see what happens at that stage.

Even with INDIRECT, I'm still getting errors, though now different: the first error is #VALUE followed by #REF errors Any idea why? (I did it in two ways with external and internal ranges relative to the LAMBDA)

Blank power workbook1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		a	n	t	5	3	g	6	TRUE	#DIV/0!		t	?
3		7.9	#N/A	#SPILL!	FALSE	h		89	book	44750	8	i	TRUE
4
5	individual cells dragged:	2	2	2	1	1	2	1	4	16	1	2	2
6		1	16	16	4	2	2	1	2	1	1	2	4
7
8	selected range:	2	2	2	1	1	2	1	4	16	1	2	2
9		1	16	16	4	2	2	1	2	1	1	2	4
10
11	individual cells dragged:	l	l	l	v	v	l	v	v	v	b	l	l
12		v	v	v	v	l	l	v	l	v	v	l	v
13
14	selected range:	l	l	l	v	v	l	v	v	v	b	l	l
15		v	v	v	v	l	l	v	l	v	v	l	v
16
17	B14 formula with LET:	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#DIV/0!	#VALUE!	#VALUE!	#VALUE!
18		#VALUE!	#N/A	#SPILL!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
19
20
21	INDIRECT with external range:	#VALUE!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
22		#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
23
24
25	INDIRECT with internal range:	#VALUE!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
26		#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
27
Sheet1

Cell Formulas
Range	Formula
J2	J2	=1/0
C3	C3	=NA()
D3	D3	={1,2,3}
G3	G3	=IF(C1>2,1,"")
B5:M6	B5	=TYPE(B2)
B8:M9	B8	=MAKEARRAY(ROWS(B2:M3),COLUMNS(B2:M3),LAMBDA(r,c,TYPE(INDEX(B2:M3,r,c))))
B11:M12	B11	=CELL("type",B2)
B14:M15	B14	=MAKEARRAY(ROWS(B2:M3),COLUMNS(B2:M3),LAMBDA(r,c,CELL("type", INDEX(B2:M3,r,c))))
B17:M18	B17	=LET(range,B2:M3,MAKEARRAY(ROWS(range),COLUMNS(range),LAMBDA(r,c,CELL("type", INDEX(range,r,c)))))
B21:M22	B21	=LET(range,B2:M3,MAKEARRAY(ROWS(range),COLUMNS(range),LAMBDA(r,c,CELL("type", INDEX(INDIRECT(TAKE(ADDRESS(ROW(range),COLUMN(range),4),1,1)&":"&TAKE(ADDRESS(ROW(range),COLUMN(range),4),-1,-1)),r,c)))))
B25:M26	B25	=LET(range,B2:M3,MAKEARRAY(ROWS(range),COLUMNS(range),LAMBDA(r,c,LET(irange,B2:M3,x,INDIRECT(TAKE(ADDRESS(ROW(irange),COLUMN(irange),4),1,1)&":"&TAKE(ADDRESS(ROW(range),COLUMN(irange),4),-1,-1)),CELL("type", INDEX(x,r,c))))))
Dynamic array formulas.