XTYPE

XTYPE(reference,[analysis_type])
reference
Required. Specifies the cell/range or array to be analyzed
analysis_type
Optional. Specifies the type of analysis to be performed and takes one of five arguments: omitted or 0, basic analysis; 1, advanced analysis; 2, equivalent to TYPE(); 3, equivalent to TYPE() with XTYPE()-style output; 4, equivalent to CELL("type",)
XTYPE outputs the precise data type of a cell/range or the elements of an array beyond the resolution of Excel's TYPE() or CELL("type",)
R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
XTYPE outputs the precise data type of a cell/range or the elements of an array beyond the resolution of Excel's TYPE() or CELL("type",)

XTYPE was designed to provide detailed and high-resolution information about the data type that a cell or cells of a range/array contain. Thus, it is spill-compatible as well. It has two parameters: reference (required) and analysis_type (optional, which specifies the mode/type of data analysis). XTYPE operates in the following modes:

A) analysis_type omitted or 1: In this "basic" mode, it outputs one of the following five types of data for the selected reference:
dt:NUMBER
dt:TEXT
dt:BLANK
dt:LOGICAL
dt:ERROR

B) analysis_type 1: In this advanced mode, it provides more detailed information on the BLANK, LOGICAL, and ERROR types and outputs one of the following data types for the selected reference:
dt:NUMBER
dt:TEXT
dt:BLANK_REAL or dt:BLANK_FORMULA
dt:LOGICAL_TRUE or dt:LOGICAL_FALSE
dt:ERROR_EXTERNAL! or dt:ERROR_NULL! or dt:ERROR_DIV/0! or dt:ERROR_VALUE! or dt:ERROR_REF! or dt:ERROR_NAME? or dt:ERROR_NUM! or dt:ERROR_N/A or dt:ERROR_GETTING_DATA or "dt:ERROR_SPILL! or dt:ERROR_CONNECT! or dt:ERROR_BLOCKED! or dt:ERROR_UNKNOWN! or dt:ERROR_FIELD! or dt:ERROR_CALC!

C) analysis_type 2: it acts exactly as the Excel's TYPE() function

D) analysis_type 3: it acts as the Excel's TYPE() function, but outputs the data type in the XTYPE style. (This demonstrates the utility of XTYPE over TYPE, as TYPE outputs "1 or number" for real blanks and "2 or text" for formula blanks; see attached XL2BB.)

E) analysis_type 4: it acts exactly as the Excel's CELL("type",) function with the added bonus that it is spill-compatible, (while CELL("type",) on its own is not spill-compatible).

Excel Formula: 
=LAMBDA(reference,[analysis_type],
   LET(at,analysis_type,
      IF(ISERROR(MATCH(at,{0,1,2,3,4},0)),
         UNIQUE(NA()),
         LET(ref,reference,
            IF(OR(at="",at=0),
               IF(ISERROR(ref),"dt:ERROR",IF(ref="","dt:BLANK",IF(ISNUMBER(ref),"dt:NUMBER",IF(ISTEXT(ref),"dt:TEXT",IF(OR(TEXT(IFERROR(ref,""),10)="TRUE",TEXT(IFERROR(ref,""),10)="FALSE"),"dt:LOGICAL","dt:OTHER"))))),
               IF(at=1,
                  IF(ISERROR(ref),SWITCH(ERROR.TYPE(ref),0,"dt:ERROR_EXTERNAL!",1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_DATA",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!"),IF(ref="",IF(ISBLANK(ref),"dt:BLANK_REAL","dt:BLANK_Formula"),IF(ISNUMBER(ref),"dt:NUMBER",IF(ISTEXT(ref),"dt:TEXT",LET(x,TEXT(IFERROR(ref,""),10)="TRUE",y,TEXT(IFERROR(ref,""),10)="FALSE",IF(OR(x,y),IF(x,"dt:LOGICAL_TRUE",IF(y,"dt:LOGICAL_FALSE")),"dt:OTHER")))))),
                  LET(res,MAP(ref,LAMBDA(mref,IF(OR(at=2,at=3),TYPE(mref),CELL("type",mref)))),SWITCH(at,2,res,3,SWITCH(res,1,"dt:NUMBER",2,"dt:TEXT",4,"dt:LOGICAL",16,"dt:ERROR"),4,res))
               )
            )
         )
      )
   )
)

xtype.xlsx
ABCDEFGHIJKL
1
2#CALC!
3type:numbertexterror(real) blanklogical (false)(formula) blanklogical (true)texterror
4cell:2b#N/AFALSE TRUE*#DIV/0!
5parameters
6individual cells
7nonedt:NUMBERdt:TEXTdt:ERRORdt:BLANKdt:LOGICALdt:BLANKdt:LOGICALdt:TEXTdt:ERROR
80dt:NUMBERdt:TEXTdt:ERRORdt:BLANKdt:LOGICALdt:BLANKdt:LOGICALdt:TEXTdt:ERROR
91dt:NUMBERdt:TEXTdt:ERROR_N/Adt:BLANK_REALdt:LOGICAL_FALSEdt:BLANK_Formuladt:LOGICAL_TRUEdt:TEXTdt:ERROR_DIV/0!
10212161424216
113dt:NUMBERdt:TEXTdt:ERRORdt:NUMBERdt:LOGICALdt:TEXTdt:LOGICALdt:TEXTdt:ERROR
124vlvbvlvlv
13spills
14nonedt:NUMBERdt:TEXTdt:ERRORdt:BLANKdt:LOGICALdt:BLANKdt:LOGICALdt:TEXTdt:ERROR
150dt:NUMBERdt:TEXTdt:ERRORdt:BLANKdt:LOGICALdt:BLANKdt:LOGICALdt:TEXTdt:ERROR
161dt:NUMBERdt:TEXTdt:ERROR_N/Adt:BLANK_REALdt:LOGICAL_FALSEdt:BLANK_Formuladt:LOGICAL_TRUEdt:TEXTdt:ERROR_DIV/0!
17212161424216
183dt:NUMBERdt:TEXTdt:ERRORdt:NUMBERdt:LOGICALdt:TEXTdt:LOGICALdt:TEXTdt:ERROR
194vlvbvlvlv
20no cells for input
21nonedt:BLANK
220dt:BLANK
231dt:BLANK_REAL
242#VALUE!
253#VALUE!
264#VALUE!
27no parameters#VALUE!
28wrong parameter#N/A
29
Sheet3
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[analysis_type],LET(at,analysis_type,IF(ISERROR(MATCH(at,{0,1,2,3,4},0)),UNIQUE(NA()),LET(ref,reference,IF(OR(at="",at=0),IF(ISERROR(ref),"dt:ERROR",IF(ref="","dt:BLANK",IF(ISNUMBER(ref),"dt:NUMBER",IF(ISTEXT(ref),"dt:TEXT",IF(OR(TEXT(IFERROR(ref,""),10)="TRUE",TEXT(IFERROR(ref,""),10)="FALSE"),"dt:LOGICAL","dt:OTHER"))))),IF(at=1,IF(ISERROR(ref),SWITCH(ERROR.TYPE(ref),0,"dt:ERROR_EXTERNAL!",1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_DATA",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!"),IF(ref="",IF(ISBLANK(ref),"dt:BLANK_REAL","dt:BLANK_Formula"),IF(ISNUMBER(ref),"dt:NUMBER",IF(ISTEXT(ref),"dt:TEXT",LET(x,TEXT(IFERROR(ref,""),10)="TRUE",y,TEXT(IFERROR(ref,""),10)="FALSE",IF(OR(x,y),IF(x,"dt:LOGICAL_TRUE",IF(y,"dt:LOGICAL_FALSE")),"dt:OTHER")))))),LET(res,MAP(ref,LAMBDA(mref,IF(OR(at=2,at=3),TYPE(mref),CELL("type",mref)))),SWITCH(at,2,res,3,SWITCH(res,1,"dt:NUMBER",2,"dt:TEXT",4,"dt:LOGICAL",16,"dt:ERROR"),4,res))))))))
E4E4=LOOKUP(C2,D2:E2)
H4H4=IF(P4>2,1,"")
K4K4=1/0
C7:K7C7=XTYPE(C4)
C8:K8C8=XTYPE(C4,0)
C9:K9C9=XTYPE(C4,1)
C10:K10C10=XTYPE(C4,2)
C11:K11C11=XTYPE(C4,3)
C12:K12C12=XTYPE(C4,4)
C14:K14C14=XTYPE(C4:K4)
C15:K15C15=XTYPE(C4:K4,0)
C16:K16C16=XTYPE(C4:K4,1)
C17:K17C17=XTYPE(C4:K4,2)
C18:K18C18=XTYPE(C4:K4,3)
C19:K19C19=XTYPE(C4:K4,4)
C21C21=XTYPE(,)
C22C22=XTYPE(,0)
C23C23=XTYPE(,1)
C24C24=XTYPE(,2)
C25C25=XTYPE(,3)
C26C26=XTYPE(,4)
C27C27=XTYPE()
C28C28=XTYPE(C4:K4,5)
Dynamic array formulas.


Please feel free to share any thoughts or suggestions.
 
Upvote 0
Version 2

Here is the updated/optimized code that adds two additional arguments for "analysis_type":

analysis_type: Optional. Specifies the type of analysis to be performed and takes one of seven arguments:
0 or omitted, basic analysis with output in the XTYPE style: dt:NUMBER, dt:TEXT, dt:BLANK, dt:LOGICAL, or dt:ERROR
1, advanced analysis (gives additional detail about the exact type of blank, logical, or error types)
2, same as basic analysis, but with numeric output: 1 for number, 2 for text, 3 for blanks, 4 for logical, 16 for error
3, same as advanced analysis, but with numeric output: 31 for real blank, 32 for formula blank, 41 for TRUE, 42 for FALSE, 160 for #EXTERNAL!, 161 for #NULL!, 162 for #DIV/0!, 163 for #VALUE!, 164 for #REF!, 165 for #NAME?, 166 for #NUM!, 167 for #N/A, 168 for #GETTING_DATA, 169 for #SPILL!,1610 for #CONNECT!, 1611 for #BLOCKED!, 1612 for #UNKNOWN!, 1613 for #FIELD!, 1614 for #CALC!
4, equivalent to TYPE();
5, equivalent to TYPE() with XTYPE()-style output;
6, equivalent to CELL("type",), but enhanced with spill compatibility

Excel Formula: 
=LAMBDA(reference,[analysis_type],
   LET(
      at,analysis_type,
         IF(ISERROR(MATCH(at,{0,1,2,3,4,5,6},0)),
         UNIQUE(NA()),
            LET(
               ref,reference,
                  IF(OR(IO(analysis_type),at=0),
                     IF(ISERROR(ref),"dt:ERROR",IF(ref="","dt:BLANK",IF(ISNUMBER(ref),"dt:NUMBER",IF(ISTEXT(ref),"dt:TEXT",IF(OR(TEXT(IFERROR(ref,""),10)="TRUE",TEXT(IFERROR(ref,""),10)="FALSE"),"dt:LOGICAL","dt:OTHER"))))),
                     IF(OR(at=1,at=2,at=3),
                        SWITCH(at,
                           1,IF(ISERROR(ref),SWITCH(ERROR.TYPE(ref),0,"dt:ERROR_EXTERNAL!",1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_DATA",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!"),IF(ref="",IF(ISBLANK(ref),"dt:BLANK_REAL","dt:BLANK_FORMULA"),IF(ISNUMBER(ref),"dt:NUMBER",IF(ISTEXT(ref),"dt:TEXT",LET(x,TEXT(IFERROR(ref,""),10)="TRUE",y,TEXT(IFERROR(ref,""),10)="FALSE",IF(OR(x,y),IF(x,"dt:LOGICAL_TRUE",IF(y,"dt:LOGICAL_FALSE")),"dt:OTHER")))))),
                           2,IF(ISERROR(ref),16,IF(ref="",3,IF(ISNUMBER(ref),1,IF(ISTEXT(ref),2,IF(OR(TEXT(IFERROR(ref,""),10)="TRUE",TEXT(IFERROR(ref,""),10)="FALSE"),4,5))))),
                           3,IF(ISERROR(ref),SWITCH(ERROR.TYPE(ref),0,160,1,161,2,162,3,163,4,164,5,165,6,166,7,167,8,168,9,169,10,1610,11,1611,12,1612,13,1613,14,1614),IF(ref="",IF(ISBLANK(ref),31,32),IF(ISNUMBER(ref),1,IF(ISTEXT(ref),2,LET(x,TEXT(IFERROR(ref,""),10)="TRUE",y,TEXT(IFERROR(ref,""),10)="FALSE",IF(OR(x,y),IF(x,41,IF(y,42)),"dt:OTHER"))))))),
                        LET(
                           res,MAP(ref,LAMBDA(mref,IF(OR(at=4,at=5),TYPE(mref),CELL("type",mref)))),
                           SWITCH(at,
                              4,res,
                              5,SWITCH(res,1,"dt:NUMBER",2,"dt:TEXT",4,"dt:LOGICAL",16,"dt:ERROR"),
                              6,res)
                        )
                     )
                  )
            )
      )
   )
)

The following helper LAMBDA is used in this code:

IO
Excel Formula: 
=LAMBDA(parameter,ISOMITTED(parameter))

xtype.xlsx
ABCDEFGHIJKLMNO
1
2#CALC!basic types
3type:numbertexterror(real) blanklogical (false)(formula) blanklogical (true)texterror1dt:NUMBER
4cell:2b#N/AFALSE TRUE*#DIV/0!2dt:TEXT
5parameters3dt:BLANK
6individual cells4dt:LOGICAL
7nonedt:NUMBERdt:TEXTdt:ERRORdt:BLANKdt:LOGICALdt:BLANKdt:LOGICALdt:TEXTdt:ERROR16dt:ERROR
80dt:NUMBERdt:TEXTdt:ERRORdt:BLANKdt:LOGICALdt:BLANKdt:LOGICALdt:TEXTdt:ERROR
91dt:NUMBERdt:TEXTdt:ERROR_N/Adt:BLANK_REALdt:LOGICAL_FALSEdt:BLANK_FORMULAdt:LOGICAL_TRUEdt:TEXTdt:ERROR_DIV/0!advanced types
102121634342161dt:NUMBER
113121673142324121622dt:TEXT
1241216142421631dt:BLANK_REAL
135dt:NUMBERdt:TEXTdt:ERRORdt:NUMBERdt:LOGICALdt:TEXTdt:LOGICALdt:TEXTdt:ERROR32dt:BLANK_FORMULA
146vlvbvlvlv41dt:LOGICAL_TRUE
15spills42dt:LOGICAL_FALSE
16nonedt:NUMBERdt:TEXTdt:ERRORdt:BLANKdt:LOGICALdt:BLANKdt:LOGICALdt:TEXTdt:ERROR160dt:ERROR_EXTERNAL!
170dt:NUMBERdt:TEXTdt:ERRORdt:BLANKdt:LOGICALdt:BLANKdt:LOGICALdt:TEXTdt:ERROR161dt:ERROR_NULL!
181dt:NUMBERdt:TEXTdt:ERROR_N/Adt:BLANK_REALdt:LOGICAL_FALSEdt:BLANK_FORMULAdt:LOGICAL_TRUEdt:TEXTdt:ERROR_DIV/0!162dt:ERROR_DIV/0!
19212163434216163dt:ERROR_VALUE!
20312167314232412162164dt:ERROR_REF!
21412161424216165dt:ERROR_NAME?
225dt:NUMBERdt:TEXTdt:ERRORdt:NUMBERdt:LOGICALdt:TEXTdt:LOGICALdt:TEXTdt:ERROR166dt:ERROR_NUM!
236vlvbvlvlv167dt:ERROR_N/A!
24no cells for input168dt:ERROR_GETTING_DATA!
25nonedt:BLANK169dt:ERROR_SPILL!
260dt:BLANK1610dt:ERROR_CONNECT!
271dt:BLANK_REAL1611dt:ERROR_BLOCKED!
28231612dt:ERROR_UNKNOWN!
293311613dt:ERROR_FIELD!
304#VALUE!1614dt:ERROR_CALC!
315#VALUE!
326#VALUE!
33no parameters#VALUE!
34
Sheet5
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[analysis_type],LET(at,analysis_type,IF(ISERROR(MATCH(at,{0,1,2,3,4,5,6},0)),UNIQUE(NA()),LET(ref,reference,IF(OR(IO(analysis_type),at=0),IF(ISERROR(ref),"dt:ERROR",IF(ref="","dt:BLANK",IF(ISNUMBER(ref),"dt:NUMBER",IF(ISTEXT(ref),"dt:TEXT",IF(OR(TEXT(IFERROR(ref,""),10)="TRUE",TEXT(IFERROR(ref,""),10)="FALSE"),"dt:LOGICAL","dt:OTHER"))))),IF(OR(at=1,at=2,at=3),SWITCH(at,1,IF(ISERROR(ref),SWITCH(ERROR.TYPE(ref),0,"dt:ERROR_EXTERNAL!",1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_DATA",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!"),IF(ref="",IF(ISBLANK(ref),"dt:BLANK_REAL","dt:BLANK_FORMULA"),IF(ISNUMBER(ref),"dt:NUMBER",IF(ISTEXT(ref),"dt:TEXT",LET(x,TEXT(IFERROR(ref,""),10)="TRUE",y,TEXT(IFERROR(ref,""),10)="FALSE",IF(OR(x,y),IF(x,"dt:LOGICAL_TRUE",IF(y,"dt:LOGICAL_FALSE")),"dt:OTHER")))))),2,IF(ISERROR(ref),16,IF(ref="",3,IF(ISNUMBER(ref),1,IF(ISTEXT(ref),2,IF(OR(TEXT(IFERROR(ref,""),10)="TRUE",TEXT(IFERROR(ref,""),10)="FALSE"),4,5))))),3,IF(ISERROR(ref),SWITCH(ERROR.TYPE(ref),0,160,1,161,2,162,3,163,4,164,5,165,6,166,7,167,8,168,9,169,10,1610,11,1611,12,1612,13,1613,14,1614),IF(ref="",IF(ISBLANK(ref),31,32),IF(ISNUMBER(ref),1,IF(ISTEXT(ref),2,LET(x,TEXT(IFERROR(ref,""),10)="TRUE",y,TEXT(IFERROR(ref,""),10)="FALSE",IF(OR(x,y),IF(x,41,IF(y,42)),"dt:OTHER"))))))),LET(res,MAP(ref,LAMBDA(mref,IF(OR(at=4,at=5),TYPE(mref),CELL("type",mref)))),SWITCH(at,4,res,5,SWITCH(res,1,"dt:NUMBER",2,"dt:TEXT",4,"dt:LOGICAL",16,"dt:ERROR"),6,res))))))))
E4E4=LOOKUP(C2,D2:E2)
H4H4=IF(P4>2,1,"")
K4K4=1/0
C7:K7C7=XTYPE(C$4)
C8:K8C8=XTYPE(C$4,0)
C9:K9C9=XTYPE(C$4,1)
C10:K10C10=XTYPE(C$4,2)
C11:K11C11=XTYPE(C$4,3)
C12:K12C12=XTYPE(C$4,4)
C13:K13C13=XTYPE(C$4,5)
C14:K14C14=XTYPE(C$4,6)
C16:K16C16=XTYPE(C4:K4)
C17:K17C17=XTYPE(C4:K4,0)
C18:K18C18=XTYPE(C4:K4,1)
C19:K19C19=XTYPE(C4:K4,2)
C20:K20C20=XTYPE(C4:K4,3)
C21:K21C21=XTYPE(C4:K4,4)
C22:K22C22=XTYPE(C4:K4,5)
C23:K23C23=XTYPE(C4:K4,6)
C25C25=XTYPE(,)
C26C26=XTYPE(,0)
C27C27=XTYPE(,1)
C28C28=XTYPE(,2)
C29C29=XTYPE(,3)
C30C30=XTYPE(,4)
C31C31=XTYPE(,5)
C32C32=XTYPE(,6)
C33C33=XTYPE()
Dynamic array formulas.
 
Optimized code:

XTYPE
Excel Formula: 
=LAMBDA(reference,[analysis_type],
   LET(
      at,IF(IO(analysis_type),0,analysis_type),
         IF(NOT(OR(at={0,1,2,3,4,5,6})),
            UNIQUE(NA()),
            LET(ref,reference,
               IF(OR(at={0,1,2,3}),
                  SWITCH(at,
                     0,IF(ISERROR(ref),"dt:ERROR",IF(ref="","dt:BLANK",IF(ISNUMBER(ref),"dt:NUMBER",IF(ISTEXT(ref),"dt:TEXT",IF(OR(TEXT(IFERROR(ref,""),10)="TRUE",TEXT(IFERROR(ref,""),10)="FALSE"),"dt:LOGICAL","dt:OTHER"))))),
                     1,IF(ISERROR(ref),SWITCH(ERROR.TYPE(ref),0,"dt:ERROR_EXTERNAL!",1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_DATA",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!"),IF(ref="",IF(ISBLANK(ref),"dt:BLANK_REAL","dt:BLANK_FORMULA"),IF(ISNUMBER(ref),"dt:NUMBER",IF(ISTEXT(ref),"dt:TEXT",LET(x,TEXT(IFERROR(ref,""),10)="TRUE",y,TEXT(IFERROR(ref,""),10)="FALSE",IF(OR(x,y),IF(x,"dt:LOGICAL_TRUE",IF(y,"dt:LOGICAL_FALSE")),"dt:OTHER")))))),
                     2,IF(ISERROR(ref),16,IF(ref="",3,IF(ISNUMBER(ref),1,IF(ISTEXT(ref),2,IF(OR(TEXT(IFERROR(ref,""),10)="TRUE",TEXT(IFERROR(ref,""),10)="FALSE"),4,5))))),
                     3,IF(ISERROR(ref),SWITCH(ERROR.TYPE(ref),0,160,1,161,2,162,3,163,4,164,5,165,6,166,7,167,8,168,9,169,10,1610,11,1611,12,1612,13,1613,14,1614),IF(ref="",IF(ISBLANK(ref),31,32),IF(ISNUMBER(ref),1,IF(ISTEXT(ref),2,LET(x,TEXT(IFERROR(ref,""),10)="TRUE",y,TEXT(IFERROR(ref,""),10)="FALSE",IF(OR(x,y),IF(x,41,IF(y,42)),"dt:OTHER"))))))),
                  LET(
                     res,MAP(ref,LAMBDA(mref,IF(OR(at={4,5}),TYPE(mref),CELL("type",mref)))),
                        SWITCH(at,
                           4,res,
                           5,SWITCH(res,1,"dt:NUMBER",2,"dt:TEXT",4,"dt:LOGICAL",16,"dt:ERROR"),
                           6,res)
                  )
               )
            )
         )
   )
)

The following helper LAMBDA is used in this code:

IO
Excel Formula: 
=LAMBDA(parameter,ISOMITTED(parameter))

xtype.xlsx
ABCDEFGHIJKLMNO
1
2#CALC!basic types
3type:numbertexterror(real) blanklogical (false)(formula) blanklogical (true)texterror1dt:NUMBER
4cell:2b#N/AFALSE TRUE*#DIV/0!2dt:TEXT
5parameters3dt:BLANK
6individual cells4dt:LOGICAL
7nonedt:NUMBERdt:TEXTdt:ERRORdt:BLANKdt:LOGICALdt:BLANKdt:LOGICALdt:TEXTdt:ERROR16dt:ERROR
80dt:NUMBERdt:TEXTdt:ERRORdt:BLANKdt:LOGICALdt:BLANKdt:LOGICALdt:TEXTdt:ERROR
91dt:NUMBERdt:TEXTdt:ERROR_N/Adt:BLANK_REALdt:LOGICAL_FALSEdt:BLANK_FORMULAdt:LOGICAL_TRUEdt:TEXTdt:ERROR_DIV/0!advanced types
102121634342161dt:NUMBER
113121673142324121622dt:TEXT
1241216142421631dt:BLANK_REAL
135dt:NUMBERdt:TEXTdt:ERRORdt:NUMBERdt:LOGICALdt:TEXTdt:LOGICALdt:TEXTdt:ERROR32dt:BLANK_FORMULA
146vlvbvlvlv41dt:LOGICAL_TRUE
15spills42dt:LOGICAL_FALSE
16nonedt:NUMBERdt:TEXTdt:ERRORdt:BLANKdt:LOGICALdt:BLANKdt:LOGICALdt:TEXTdt:ERROR160dt:ERROR_EXTERNAL!
170dt:NUMBERdt:TEXTdt:ERRORdt:BLANKdt:LOGICALdt:BLANKdt:LOGICALdt:TEXTdt:ERROR161dt:ERROR_NULL!
181dt:NUMBERdt:TEXTdt:ERROR_N/Adt:BLANK_REALdt:LOGICAL_FALSEdt:BLANK_FORMULAdt:LOGICAL_TRUEdt:TEXTdt:ERROR_DIV/0!162dt:ERROR_DIV/0!
19212163434216163dt:ERROR_VALUE!
20312167314232412162164dt:ERROR_REF!
21412161424216165dt:ERROR_NAME?
225dt:NUMBERdt:TEXTdt:ERRORdt:NUMBERdt:LOGICALdt:TEXTdt:LOGICALdt:TEXTdt:ERROR166dt:ERROR_NUM!
236vlvbvlvlv167dt:ERROR_N/A!
24no cells for input168dt:ERROR_GETTING_DATA!
25nonedt:BLANK169dt:ERROR_SPILL!
260dt:BLANK1610dt:ERROR_CONNECT!
271dt:BLANK_REAL1611dt:ERROR_BLOCKED!
28231612dt:ERROR_UNKNOWN!
293311613dt:ERROR_FIELD!
304#VALUE!1614dt:ERROR_CALC!
315#VALUE!
326#VALUE!
33wrong parameter#N/A
34no parameters#VALUE!
35
posting
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[analysis_type],LET(at,IF(IO(analysis_type),0,analysis_type),IF(NOT(OR(at={0,1,2,3,4,5,6})),UNIQUE(NA()),LET(ref,reference,IF(OR(at={0,1,2,3}),SWITCH(at,0,IF(ISERROR(ref),"dt:ERROR",IF(ref="","dt:BLANK",IF(ISNUMBER(ref),"dt:NUMBER",IF(ISTEXT(ref),"dt:TEXT",IF(OR(TEXT(IFERROR(ref,""),10)="TRUE",TEXT(IFERROR(ref,""),10)="FALSE"),"dt:LOGICAL","dt:OTHER"))))),1,IF(ISERROR(ref),SWITCH(ERROR.TYPE(ref),0,"dt:ERROR_EXTERNAL!",1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_DATA",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!"),IF(ref="",IF(ISBLANK(ref),"dt:BLANK_REAL","dt:BLANK_FORMULA"),IF(ISNUMBER(ref),"dt:NUMBER",IF(ISTEXT(ref),"dt:TEXT",LET(x,TEXT(IFERROR(ref,""),10)="TRUE",y,TEXT(IFERROR(ref,""),10)="FALSE",IF(OR(x,y),IF(x,"dt:LOGICAL_TRUE",IF(y,"dt:LOGICAL_FALSE")),"dt:OTHER")))))),2,IF(ISERROR(ref),16,IF(ref="",3,IF(ISNUMBER(ref),1,IF(ISTEXT(ref),2,IF(OR(TEXT(IFERROR(ref,""),10)="TRUE",TEXT(IFERROR(ref,""),10)="FALSE"),4,5))))),3,IF(ISERROR(ref),SWITCH(ERROR.TYPE(ref),0,160,1,161,2,162,3,163,4,164,5,165,6,166,7,167,8,168,9,169,10,1610,11,1611,12,1612,13,1613,14,1614),IF(ref="",IF(ISBLANK(ref),31,32),IF(ISNUMBER(ref),1,IF(ISTEXT(ref),2,LET(x,TEXT(IFERROR(ref,""),10)="TRUE",y,TEXT(IFERROR(ref,""),10)="FALSE",IF(OR(x,y),IF(x,41,IF(y,42)),"dt:OTHER"))))))),LET(res,MAP(ref,LAMBDA(mref,IF(OR(at={4,5}),TYPE(mref),CELL("type",mref)))),SWITCH(at,4,res,5,SWITCH(res,1,"dt:NUMBER",2,"dt:TEXT",4,"dt:LOGICAL",16,"dt:ERROR"),6,res)))))))
E4E4=LOOKUP(C2,D2:E2)
H4H4=IF(P4>2,1,"")
K4K4=1/0
C7:K7C7=XTYPE(C$4)
C8:K8C8=XTYPE(C$4,0)
C9:K9C9=XTYPE(C$4,1)
C10:K10C10=XTYPE(C$4,2)
C11:K11C11=XTYPE(C$4,3)
C12:K12C12=XTYPE(C$4,4)
C13:K13C13=XTYPE(C$4,5)
C14:K14C14=XTYPE(C$4,6)
C16:K16C16=XTYPE(C4:K4)
C17:K17C17=XTYPE(C4:K4,0)
C18:K18C18=XTYPE(C4:K4,1)
C19:K19C19=XTYPE(C4:K4,2)
C20:K20C20=XTYPE(C4:K4,3)
C21:K21C21=XTYPE(C4:K4,4)
C22:K22C22=XTYPE(C4:K4,5)
C23:K23C23=XTYPE(C4:K4,6)
C25C25=XTYPE(,)
C26C26=XTYPE(,0)
C27C27=XTYPE(,1)
C28C28=XTYPE(,2)
C29C29=XTYPE(,3)
C30C30=XTYPE(,4)
C31C31=XTYPE(,5)
C32C32=XTYPE(,6)
C33C33=XTYPE(C4:K4,8)
C34C34=XTYPE()
Dynamic array formulas.
 
Updated code: addresses the issue where the function spill is affected by incorrect analysis_type parameter, such as arrays (eg. {1,2}) instead of single numbers 0-6, errors, etc. (I created the PLS function to address this issue, as shown below, but if there is an easier way to handle this issue, please let me know.)

XTYPE
VBA Code: 
=LAMBDA(reference,[analysis_type],
   IF(
      IO(reference),
      NA(),
      LET(
         r,reference,
         a,PLS(PO0(analysis_type)),
         IF(
            OR(a={1,3}),
            LET(
               x,TEXT(IFERROR(r,""),10)="TRUE",
               y,TEXT(IFERROR(r,""),10)="FALSE",
               SWITCH(a,
                  1,IF(ISERROR(r),XERROR.TYPE(r,1),IF(r="",IF(ISBLANK(r),"dt:BLANK_REAL","dt:BLANK_FORMULA"),IF(ISNUMBER(r),"dt:NUMBER",IF(ISTEXT(r),"dt:TEXT",IF(OR(x,y),IF(x,"dt:LOGICAL_TRUE",IF(y,"dt:LOGICAL_FALSE")),"dt:OTHER"))))),
                  3,IF(ISERROR(r),XERROR.TYPE(r),IF(r="",IF(ISBLANK(r),31,32),IF(ISNUMBER(r),1,IF(ISTEXT(r),2,IF(OR(x,y),IF(x,41,IF(y,42)),"dt:OTHER"))))))
            ),
            IF(
               OR(a={0,2}),
               SWITCH(a,
                  0,IF(ISERROR(r),"dt:ERROR",IF(r="","dt:BLANK",IF(ISNUMBER(r),"dt:NUMBER",IF(ISTEXT(r),"dt:TEXT",IF(OR(TEXT(IFERROR(r,""),10)="TRUE",TEXT(IFERROR(r,""),10)="FALSE"),"dt:LOGICAL","dt:OTHER"))))),
                  2,IF(ISERROR(r),16,IF(r="",3,IF(ISNUMBER(r),1,IF(ISTEXT(r),2,IF(OR(TEXT(IFERROR(r,""),10)="TRUE",TEXT(IFERROR(r,""),10)="FALSE"),4,5)))))),
                     LET(
                        t,MAP(r,LAMBDA(u,IF(OR(a={4,5}),TYPE(u),CELL("type",u)))),
                        SWITCH(a,
                           4,t,
                           5,SWITCH(t,1,"dt:NUMBER",2,"dt:TEXT",4,"dt:LOGICAL",16,"dt:ERROR"),
                           6,t)
                     )
            )
         )
      )
   )
)

The following helper functions are also used in the code. All are included in my "Blank power workbook" template, which you can download through the link in my signature below:

IO (shortened form of ISOMITTED)
VBA Code: 
=LAMBDA(parameter,ISOMITTED(parameter))

PLS (stands for "parameter limit, single")
(Returns the parameter if it is a single entry [not blank or error] and #N/A otherwise)
(Causes the function to crash to a single #N/A if incorrect parameters are entered [either the wrong type or more than a single entry in array brackets "{}"])
VBA Code: 
=LAMBDA(parameter,INDEX(IF(OR(JAN(parameter),JAO(parameter),JAP(parameter)),NA(),parameter),1))

PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
VBA Code: 
=LAMBDA([parameter],IF(IO(parameter),0,parameter))

JAN (Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise [simplified form of ISARRAY])
VBA Code: 
=LAMBDA([reference],LET(r,IFERROR(IF(reference="","",reference),""),IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE))))

JAO (Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])
VBA Code: 
=LAMBDA(reference,IF(IO(reference),FALSE,IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE)))

JAP (Returns TRUE if the reference contains at least one blank and FALSE otherwise [simplified form of ISBLANKS])
VBA Code: 
=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))
 
Updated code:
1) Adds two new types of blanks, i.e. 33 and 34 (33 is for one or more space characters, and 34 is for when a blank-generating formula is pasted as value) (refer to the attached XL2BB for the comparison of all four types of blanks) (I added the 33 space blank as several of my students and colleagues complained that when they used type 2 for text in some of my other functions, the results included cells containing space characters as well, so I thought to separate space blanks from text)
2) Addresses the recent changes in the error types and their associated numbers
3) Increases the speed (I believe it is now about 10 times faster based on my tests with very large chunks of cells)

This provides the highest resolution of data types so far. Enjoy using, and as always, the easiest way to get this and my other functions is to download my xltx template from the link in my signature.

XTYPE
Excel Formula: 
=LAMBDA(reference,[analysis_type],
   IF(
      IO(reference),
      NA(),
      LET(
         r,reference,
         a,PLS(PO0(analysis_type)),
         IF(
            OR(a={0,1,2,3}),
            LET(
               z,MAP(r,
                  LAMBDA(b,
                     IF(
                        ISERROR(b),
                        XERROR.TYPE(b),
                        IF(
                           b="",
                           IF(ISBLANK(b),31,IFERROR(IF(ISFORMULA(b),32,34),32)),
                           IF(ISNUMBER(b),1,IF(ISTEXT(b),IF(LEN(TRIM(b)),2,33),IF(b,41,42)))
                        )
                     )
                  )),
               SWITCH(a,
                  0,SWITCH(z,1,"dt:NUMBER",2,"dt:TEXT",31,"dt:BLANK",32,"dt:BLANK",33,"dt:BLANK",34,"dt:BLANK",41,"dt:LOGICAL",42,"dt:LOGICAL","dt:ERROR"),
                  1,SWITCH(z,1,"dt:NUMBER",2,"dt:TEXT",31,"dt:BLANK_REAL",32,"dt:BLANK_FORMULA",33,"dt:BLANK_SPACE",34,"dt:BLANK_PASTED",41,"dt:LOGICAL_TRUE",42,"dt:LOGICAL_FALSE",160,"dt:ERROR_EXTERNAL!",161,"dt:ERROR_NULL!",162,"dt:ERROR_DIV/0!",163,"dt:ERROR_VALUE!",164,"dt:ERROR_REF!",165,"dt:ERROR_NAME?",166,"dt:ERROR_NUM!",167,"dt:ERROR_N/A",168,"dt:ERROR_GETTING_Data",169,"dt:ERROR_SPILL!",1610,"dt:ERROR_CONNECT!",1611,"dt:ERROR_BLOCKED!",1612,"dt:ERROR_UNKNOWN!",1613,"dt:ERROR_FIELD!",1614,"dt:ERROR_CALC!"),
                  2,SWITCH(z,1,1,2,2,31,3,32,3,33,3,34,3,41,4,42,4,16),3,z)),
            LET(
               t,MAP(r,LAMBDA(u,IF(OR(a={4,5}),TYPE(u),CELL("type",u)))),
               SWITCH(a,
                  4,t,
                  5,SWITCH(t,1,"dt:NUMBER",2,"dt:TEXT",4,"dt:LOGICAL",16,"dt:ERROR"),
                  6,t)
            )
         )
      )
   )
)

The code for the following small helper functions is not changed, but I'm listing them for easier transfer if needed. The main purpose of most of these helper functions, as some people were wondering, is to ensure that only entering correct parameters in the main function, in this case XTYPE, leads to the intended output. If the wrong parameter(s) are entered (i.e. either the wrong type and/or the wrong number of parameters), the main function crashes with an error, typically #N/A. Thus, accidental/unintended erroneous outputs will be avoided this way.

IO (shortened form of ISOMITTED)
Excel Formula: 
=LAMBDA(parameter,ISOMITTED(parameter))

PLS (stands for "parameter limit, single")
(Returns the parameter if it is a single entry [not blank or error] and #N/A otherwise)
(Causes the function to crash to a single #N/A if incorrect parameters are entered [either the wrong type or more than a single entry in array brackets "{}"])
Excel Formula: 
=LAMBDA(parameter,
   INDEX(
      IF(
         OR(JAN(parameter),JAO(parameter),JAP(parameter)),
         NA(),
         parameter),
      1
   )
)

PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
Excel Formula: 
=LAMBDA([parameter],
   IF(
      IO(parameter),
      0,
      parameter
   )
)

JAN (Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise)
Excel Formula: 
=LAMBDA([reference],
   LET(
      r,IFERROR(IF(reference="","",reference),""),
      IF(
         IO(reference),
         FALSE,
         IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE)
      )
   )
)

JAO (Returns TRUE if the reference contains at least one error and FALSE otherwise)
Excel Formula: 
=LAMBDA(reference,
   IF(
      IO(reference),
      FALSE,
      IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE)
   )
)

JAP (Returns TRUE if the reference contains at least one blank and FALSE otherwise)
Excel Formula: 
=LAMBDA(reference,
   IF(
      IO(reference),
      NA(),
      IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)
   )
)



XTYPE2.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1
2#CALC!31323334basic types
3The four types of blanks:  1dt:NUMBER
4LEN00602dt:TEXT
5ISBLANK10003dt:BLANK
6IS ""11014dt:LOGICAL
7ISFORMULA010016dt:ERROR
8ISTEXT0111
9LEN(TRIM)0000advanced types
101dt:NUMBER
11individual cell output (analysis_type)spill output (analysis_type)2dt:TEXT
12inputomitted0123456omitted012345631dt:BLANK_REAL
1382dt:NUMBERdt:NUMBERdt:NUMBER111dt:NUMBERvdt:NUMBERdt:NUMBERdt:NUMBER111dt:NUMBERv32dt:BLANK_FORMULA
14bookdt:TEXTdt:TEXTdt:TEXT222dt:TEXTldt:TEXTdt:TEXTdt:TEXT222dt:TEXTl33dt:BLANK_SPACE
15dt:BLANKdt:BLANKdt:BLANK_REAL3311dt:NUMBERbdt:BLANKdt:BLANKdt:BLANK_REAL3311dt:NUMBERb34dt:BLANK_PASTED
16 dt:BLANKdt:BLANKdt:BLANK_FORMULA3322dt:TEXTldt:BLANKdt:BLANKdt:BLANK_FORMULA3322dt:TEXTl41dt:LOGICAL_TRUE
17 dt:BLANKdt:BLANKdt:BLANK_SPACE3332dt:TEXTldt:BLANKdt:BLANKdt:BLANK_SPACE3332dt:TEXTl42dt:LOGICAL_FALSE
18dt:BLANKdt:BLANKdt:BLANK_PASTED3342dt:TEXTldt:BLANKdt:BLANKdt:BLANK_PASTED3342dt:TEXTl161dt:ERROR_NULL!
19TRUEdt:LOGICALdt:LOGICALdt:LOGICAL_TRUE4414dt:LOGICALvdt:LOGICALdt:LOGICALdt:LOGICAL_TRUE4414dt:LOGICALv162dt:ERROR_DIV/0!
20FALSEdt:LOGICALdt:LOGICALdt:LOGICAL_FALSE4424dt:LOGICALvdt:LOGICALdt:LOGICALdt:LOGICAL_FALSE4424dt:LOGICALv163dt:ERROR_VALUE!
21#NULL!dt:ERRORdt:ERRORdt:ERROR_NULL!1616116dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_NULL!1616116dt:ERRORv164dt:ERROR_REF!
22#DIV/0!dt:ERRORdt:ERRORdt:ERROR_DIV/0!1616216dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_DIV/0!1616216dt:ERRORv165dt:ERROR_NAME?
23#VALUE!dt:ERRORdt:ERRORdt:ERROR_VALUE!1616316dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_VALUE!1616316dt:ERRORv166dt:ERROR_NUM!
24#REF!dt:ERRORdt:ERRORdt:ERROR_REF!1616416dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_REF!1616416dt:ERRORv167dt:ERROR_N/A
25#NAME?dt:ERRORdt:ERRORdt:ERROR_NAME?1616516dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_NAME?1616516dt:ERRORv168dt:ERROR_GETTING_DATA
26#NUM!dt:ERRORdt:ERRORdt:ERROR_NUM!1616616dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_NUM!1616616dt:ERRORv169dt:ERROR_SPILL!
27#N/Adt:ERRORdt:ERRORdt:ERROR_N/A1616716dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_N/A1616716dt:ERRORv1610dt:ERROR_CONNECT!
28#GETTING_DATAdt:ERRORdt:ERRORdt:ERROR_GETTING_Data1616816dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_GETTING_Data1616816dt:ERRORv1611dt:ERROR_BLOCKED!
29#SPILL!dt:ERRORdt:ERRORdt:ERROR_SPILL!1616916dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_SPILL!1616916dt:ERRORv1612dt:ERROR_UNKNOWN!
30#CONNECT!dt:ERRORdt:ERRORdt:ERROR_CONNECT!16161016dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_CONNECT!16161016dt:ERRORv1613dt:ERROR_FIELD!
31#BLOCKED!dt:ERRORdt:ERRORdt:ERROR_BLOCKED!16161116dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_BLOCKED!16161116dt:ERRORv1614dt:ERROR_CALC!
32#UNKNOWN!dt:ERRORdt:ERRORdt:ERROR_UNKNOWN!16161216dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_UNKNOWN!16161216dt:ERRORv1619dt:ERROR_PYTHON!
33#FIELD!dt:ERRORdt:ERRORdt:ERROR_FIELD!16161316dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_FIELD!16161316dt:ERRORv
34#CALC!dt:ERRORdt:ERRORdt:ERROR_CALC!16161416dt:ERRORvdt:ERRORdt:ERRORdt:ERROR_CALC!16161416dt:ERRORv
35
36no parameteroutput
37#VALUE!
38 
XTYPE
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[analysis_type],IF(IO(reference),NA(),LET(r,reference,a,PLS(PO0(analysis_type)),IF(OR(a={0,1,2,3}),LET(z,MAP(r,LAMBDA(b,IF(ISERROR(b),XERROR.TYPE(b),IF(b="",IF(ISBLANK(b),31,IFERROR(IF(ISFORMULA(b),32,34),32)),IF(ISNUMBER(b),1,IF(ISTEXT(b),IF(LEN(TRIM(b)),2,33),IF(b,41,42))))))),SWITCH(a,0,SWITCH(z,1,"dt:NUMBER",2,"dt:TEXT",31,"dt:BLANK",32,"dt:BLANK",33,"dt:BLANK",34,"dt:BLANK",41,"dt:LOGICAL",42,"dt:LOGICAL","dt:ERROR"),1,SWITCH(z,1,"dt:NUMBER",2,"dt:TEXT",31,"dt:BLANK_REAL",32,"dt:BLANK_FORMULA",33,"dt:BLANK_SPACE",34,"dt:BLANK_PASTED",41,"dt:LOGICAL_TRUE",42,"dt:LOGICAL_FALSE",160,"dt:ERROR_EXTERNAL!",161,"dt:ERROR_NULL!",162,"dt:ERROR_DIV/0!",163,"dt:ERROR_VALUE!",164,"dt:ERROR_REF!",165,"dt:ERROR_NAME?",166,"dt:ERROR_NUM!",167,"dt:ERROR_N/A",168,"dt:ERROR_GETTING_Data",169,"dt:ERROR_SPILL!",1610,"dt:ERROR_CONNECT!",1611,"dt:ERROR_BLOCKED!",1612,"dt:ERROR_UNKNOWN!",1613,"dt:ERROR_FIELD!",1614,"dt:ERROR_CALC!"),2,SWITCH(z,1,1,2,2,31,3,32,3,33,3,34,3,41,4,42,4,16),3,z)),LET(t,MAP(r,LAMBDA(u,IF(OR(a={4,5}),TYPE(u),CELL("type",u)))),SWITCH(a,4,t,5,SWITCH(t,1,"dt:NUMBER",2,"dt:TEXT",4,"dt:LOGICAL",16,"dt:ERROR"),6,t))))))
H3,B38,B16H3=IF(1,"")
G4:J4G4=LEN(G3)
G5:J5G5=IF(ISBLANK(G3),1,0)
G6:J6G6=IF(G3="",1,0)
G7:J7G7=IF(ISFORMULA(G3),1,0)
G8:J8G8=IF(ISTEXT(G3),1,0)
G9:J9G9=LEN(TRIM(G3))
C13:C34C13=XTYPE(B13)
D13:D34D13=XTYPE(B13,0)
E13:E34E13=XTYPE(B13,1)
F13:F34F13=XTYPE(B13,2)
G13:G34G13=XTYPE(B13,3)
H13:H34H13=XTYPE(B13,4)
I13:I34I13=XTYPE(B13,5)
J13:J34J13=XTYPE(B13,6)
K13:K34K13=XTYPE(B13:B34)
L13:L34L13=XTYPE(B13:B34,0)
M13:M34M13=XTYPE(B13:B34,1)
N13:N34N13=XTYPE(B13:B34,2)
O13:O34O13=XTYPE(B13:B34,3)
P13:P34P13=XTYPE(B13:B34,4)
Q13:Q34Q13=XTYPE(B13:B34,5)
R13:R34R13=XTYPE(B13:B34,6)
C37C37=XTYPE()
Dynamic array formulas.
 
Last edited:
You must log in or register to reply here.

Forum statistics

Threads
1,223,880
Messages
6,175,157
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top