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
528
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)))
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

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