ISNUMBERS checks if all elements of the reference are numbers and provides a single logical output, with options for checking if the reference is a single number or equal to another set of numbers and extending equality with elements of other data types
The output of ISNUMBERS is always a single logical (i.e. TRUE or FALSE) (except when the reference is omitted in which case it has the option of displaying an output of any size if included in the "if_omitted" parameter). In its simplest form, ISNUMBERS can be used to check if all elements of the reference are numbers or not, but its utility can be significantly extended to other scenarios by using the various parameters of the function alone or in some combinations. Thus, it can be used in at least eight (and possibly even more) ways:
1) checking, in a straight-forward way, if all elements in the reference are numbers without writing extra code for the native ISNUMBER function which spills a set of logicals
2) checking if the numbers in the reference are equal to a second set of numbers by including numbers in the "match_set" parameter and keeping the "extend_match" parameter omitted or setting it to 0
3) checking if the numbers and any other non-numerical elements in the reference are equal to the match set by including non-numerical values in the "match_set" parameter and setting the "extend_match" parameter to 1
4) checking if all elements of the reference are numbers or are equal to any numbers or non-numerical values specified in the match set by including non-numerical values in the "match_set" parameter and setting the "extend_match" parameter to 2
5) checking if the reference contains value(s) that are logically evaluable by including "TRUE" and "FALSE" in the context of #4 (as logically evaluable elements are either numbers or TRUE/FALSE)
6) using the #4 scenario to check if references that don't contain any numbers (i.e. contain texts, errors, blanks, or logicals) have elements that appear in the match set
7) checking if the reference contains only a single value by setting the "single" parameter to true (this can be used in all of the above scenarios)
8) specifying the desired function output, in any of the above scenarios, if the reference is omitted (the default output in this case would be FALSE) by including the desired output in the "if_omitted" parameter
ISNUMBERS takes five parameters, one required and four optional, as follows:
A) The first parameter, required, specifies the function input and can be any cell/range/array
Note that the reference can contain any type of data, i.e. numbers, texts, blanks, logicals, and errors (for a full list of all data types, refer to the latest update of XTYPE)
B) The second parameter, optional, specifies the desired output if the reference is omitted
Note that any value(s) of any data type(s) can be included in this parameter
Note that if more than one entry need to be entered, they should be included in array brackets "{}" per native syntax
Note that the only scenario where the function output can be larger than a single element is when the reference is omitted, thereby allowing the "if_omitted" content to be shown as the result
C) The third parameter, optional, specifies whether the function checks if the reference contains only a single number (or other data type by using the subsequent parameters) and takes one of two arguments:
0 or omitted or FALSE, for disabling the parameter
1 or TRUE or any number other than 0, for enabling the parameter
Note that besides being omitted, only one real number or TRUE/FALSE can be entered in this parameter; entering more than one number or logical will cause the function to crash to a #VALUE! error
Note that if anything other than the specified allowed arguments is entered, the function will return a #VALUE! error
D) The fourth parameter, optional, specifies a second set of elements for equality check with the reference elements
Note that if more than one value need to be entered, they must be included in array brackets "{}" per native syntax
Note that any value(s) of any data type(s) can be included in this parameter
E) The fifth parameter, optional, specifies how the function uses the "match set" provided in the "match_set" parameter (see section D above) and takes one of three arguments:
0 or omitted, for checking if all the reference elements are numbers that are equal to the numbers in the match set
1, for checking if all the numbers and any non-numerical elements in the reference are equal to the match set
2, for checking if all the reference elements are numbers or match the elements in the match set
Note that for arguments 0 and 1, there must be numbers in the reference; otherwise, the function will return FALSE; for argument 2, however, the reference may contain only non-numerical values as well as a mix of numbers and non-numerical values
Note that only one argument can be entered in this parameter; entering more than one number via array brackets "{}" would lead to a #VALUE! error
Note that besides being omitted, only the specified three arguments can be used in this array; entering anything else will lead to a #VALUE! error
(You can download my template from the link in my signature below, which makes it easier to obtain/test the functions compared to manually pasting from the text/XL2BB)
ISNUMBERS
IO (shortened form of ISOMITTED)
The output of ISNUMBERS is always a single logical (i.e. TRUE or FALSE) (except when the reference is omitted in which case it has the option of displaying an output of any size if included in the "if_omitted" parameter). In its simplest form, ISNUMBERS can be used to check if all elements of the reference are numbers or not, but its utility can be significantly extended to other scenarios by using the various parameters of the function alone or in some combinations. Thus, it can be used in at least eight (and possibly even more) ways:
1) checking, in a straight-forward way, if all elements in the reference are numbers without writing extra code for the native ISNUMBER function which spills a set of logicals
2) checking if the numbers in the reference are equal to a second set of numbers by including numbers in the "match_set" parameter and keeping the "extend_match" parameter omitted or setting it to 0
3) checking if the numbers and any other non-numerical elements in the reference are equal to the match set by including non-numerical values in the "match_set" parameter and setting the "extend_match" parameter to 1
4) checking if all elements of the reference are numbers or are equal to any numbers or non-numerical values specified in the match set by including non-numerical values in the "match_set" parameter and setting the "extend_match" parameter to 2
5) checking if the reference contains value(s) that are logically evaluable by including "TRUE" and "FALSE" in the context of #4 (as logically evaluable elements are either numbers or TRUE/FALSE)
6) using the #4 scenario to check if references that don't contain any numbers (i.e. contain texts, errors, blanks, or logicals) have elements that appear in the match set
7) checking if the reference contains only a single value by setting the "single" parameter to true (this can be used in all of the above scenarios)
8) specifying the desired function output, in any of the above scenarios, if the reference is omitted (the default output in this case would be FALSE) by including the desired output in the "if_omitted" parameter
ISNUMBERS takes five parameters, one required and four optional, as follows:
A) The first parameter, required, specifies the function input and can be any cell/range/array
Note that the reference can contain any type of data, i.e. numbers, texts, blanks, logicals, and errors (for a full list of all data types, refer to the latest update of XTYPE)
B) The second parameter, optional, specifies the desired output if the reference is omitted
Note that any value(s) of any data type(s) can be included in this parameter
Note that if more than one entry need to be entered, they should be included in array brackets "{}" per native syntax
Note that the only scenario where the function output can be larger than a single element is when the reference is omitted, thereby allowing the "if_omitted" content to be shown as the result
C) The third parameter, optional, specifies whether the function checks if the reference contains only a single number (or other data type by using the subsequent parameters) and takes one of two arguments:
0 or omitted or FALSE, for disabling the parameter
1 or TRUE or any number other than 0, for enabling the parameter
Note that besides being omitted, only one real number or TRUE/FALSE can be entered in this parameter; entering more than one number or logical will cause the function to crash to a #VALUE! error
Note that if anything other than the specified allowed arguments is entered, the function will return a #VALUE! error
D) The fourth parameter, optional, specifies a second set of elements for equality check with the reference elements
Note that if more than one value need to be entered, they must be included in array brackets "{}" per native syntax
Note that any value(s) of any data type(s) can be included in this parameter
E) The fifth parameter, optional, specifies how the function uses the "match set" provided in the "match_set" parameter (see section D above) and takes one of three arguments:
0 or omitted, for checking if all the reference elements are numbers that are equal to the numbers in the match set
1, for checking if all the numbers and any non-numerical elements in the reference are equal to the match set
2, for checking if all the reference elements are numbers or match the elements in the match set
Note that for arguments 0 and 1, there must be numbers in the reference; otherwise, the function will return FALSE; for argument 2, however, the reference may contain only non-numerical values as well as a mix of numbers and non-numerical values
Note that only one argument can be entered in this parameter; entering more than one number via array brackets "{}" would lead to a #VALUE! error
Note that besides being omitted, only the specified three arguments can be used in this array; entering anything else will lead to a #VALUE! error
(You can download my template from the link in my signature below, which makes it easier to obtain/test the functions compared to manually pasting from the text/XL2BB)
ISNUMBERS
Excel Formula:
=LAMBDA(reference,[if_omitted],[single],[match_set],[extend_match],
IF(
IO(reference),
IF(IO(if_omitted),FALSE,if_omitted),
LET(
k,TOCOL(IF(reference="","",reference)),
l,TOCOL(IF(match_set="","",match_set)),
m,IF(IO(extend_match),0,extend_match),
Mλ,LAMBDA(w,IF(PRODUCT(ISNUMBER(w)*1),TRUE,FALSE)),
Aλ,LAMBDA(u,IFERROR(ROWS(TOCOL(u)),1)),
Bλ,LAMBDA(x,LET(v,Aλ(x),IF(v=1,IF(IFERROR(IF(IO(x),0,x),#VALUE!),TRUE,FALSE),#VALUE!))),
Cλ,LAMBDA(r,s,
IF(
PRODUCT(NOT(ISERROR(IF(
ISERROR(r),
MATCH(ERROR.TYPE(r),ERROR.TYPE(s),0),
MATCH(r,s,0)
)))*1),
TRUE,
FALSE
)
),
Nλ,LAMBDA(o,LET(
p,ROWS(o)*COLUMNS(o),
IF(
IFERROR(p,1)>1,
FALSE,
IF((IF(ISERROR(o),IF(ERROR.TYPE(o)=14,1,0),0))+(IF(ISERROR(p),1,0))=2,TRUE,FALSE)
)
)
),
f,Bλ(single),
i,IF(
IO(match_set),
Mλ(k),
SWITCH(m,
0,LET(g,FILTER(k,NOT(ISNUMBER(k))),IF(Nλ(g),Cλ(k,l),FALSE)),
1,LET(g,FILTER(k,ISNUMBER(k)),IF(Nλ(g),FALSE,Cλ(k,l))),
OR(
LET(g,FILTER(k,ISNUMBER(k)),IF(Nλ(k),1,Mλ(k))),
LET(q,FILTER(k,NOT(ISNUMBER(k))),IF(Nλ(q),1,Cλ(q,l)))
)
)
),
b,IF(f,IF(Aλ(k)=1,i,FALSE),i),
IF(AND(ISLOGICAL(f),Cλ(m,{0,1,2}),ISLOGICAL(Bλ(m))),b,#VALUE!)
)
)
)IO (shortened form of ISOMITTED)
Excel Formula:
=LAMBDA(parameter,
ISOMITTED(parameter)
)| ISNUMBERS.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | ||||||||||||||
| 2 | #CALC! | |||||||||||||
| 3 | ||||||||||||||
| 4 | (data type) | reference | output | reference | output | |||||||||
| 5 | 1 | 3 | TRUE | {1,2,3} | TRUE | |||||||||
| 6 | 2 | book | FALSE | {1,2,"book"} | FALSE | |||||||||
| 7 | 31 | FALSE | {1,2,""} | FALSE | ||||||||||
| 8 | 32 | FALSE | {1,2,TRUE} | FALSE | ||||||||||
| 9 | 33 | FALSE | {1,2,FALSE} | FALSE | ||||||||||
| 10 | 34 | FALSE | {1,2,#VALUE!} | FALSE | ||||||||||
| 11 | 41 | TRUE | FALSE | |||||||||||
| 12 | 42 | FALSE | FALSE | |||||||||||
| 13 | 162 | #DIV/0! | FALSE | |||||||||||
| 14 | ||||||||||||||
| 15 | ||||||||||||||
| 16 | single | output | extend_match | output | extend_match | output | ||||||||
| 17 | omitted | TRUE | omitted | TRUE | omitted | TRUE | ||||||||
| 18 | 1 | FALSE | 1 | TRUE | 1 | TRUE | ||||||||
| 19 | omitted | TRUE | 2 | TRUE | 2 | TRUE | ||||||||
| 20 | 1 | TRUE | omitted | FALSE | omitted | FALSE | ||||||||
| 21 | omitted | TRUE | 1 | TRUE | 1 | FALSE | ||||||||
| 22 | 1 | TRUE | 2 | TRUE | 2 | FALSE | ||||||||
| 23 | omitted | FALSE | omitted | FALSE | omitted | FALSE | ||||||||
| 24 | 1 | FALSE | 1 | TRUE | 1 | FALSE | ||||||||
| 25 | 2 | TRUE | 2 | FALSE | ||||||||||
| 26 | omitted | FALSE | omitted | FALSE | ||||||||||
| 27 | 1 | TRUE | 1 | FALSE | ||||||||||
| 28 | 2 | TRUE | 2 | FALSE | ||||||||||
| 29 | ||||||||||||||
ISNUMBERS | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2 | B2 | =LAMBDA(reference,[if_omitted],[single],[match_set],[extend_match],IF(IO(reference),IF(IO(if_omitted),FALSE,if_omitted),LET(k,TOCOL(IF(reference="","",reference)),l,TOCOL(IF(match_set="","",match_set)),m,IF(IO(extend_match),0,extend_match),Mλ,LAMBDA(w,IF(PRODUCT(ISNUMBER(w)*1),TRUE,FALSE)),Aλ,LAMBDA(u,IFERROR(ROWS(TOCOL(u)),1)),Bλ,LAMBDA(x,LET(v,Aλ(x),IF(v=1,IF(IFERROR(IF(IO(x),0,x),#VALUE!),TRUE,FALSE),#VALUE!))),Cλ,LAMBDA(r,s,IF(PRODUCT(NOT(ISERROR(IF(ISERROR(r),MATCH(ERROR.TYPE(r),ERROR.TYPE(s),0),MATCH(r,s,0))))*1),TRUE,FALSE)),Nλ,LAMBDA(o,LET(p,ROWS(o)*COLUMNS(o),IF(IFERROR(p,1)>1,FALSE,IF((IF(ISERROR(o),IF(ERROR.TYPE(o)=14,1,0),0))+(IF(ISERROR(p),1,0))=2,TRUE,FALSE)))),f,Bλ(single),i,IF(IO(match_set),Mλ(k),SWITCH(m,0,LET(g,FILTER(k,NOT(ISNUMBER(k))),IF(Nλ(g),Cλ(k,l),FALSE)),1,LET(g,FILTER(k,ISNUMBER(k)),IF(Nλ(g),FALSE,Cλ(k,l))),OR(LET(g,FILTER(k,ISNUMBER(k)),IF(Nλ(k),1,Mλ(k))),LET(q,FILTER(k,NOT(ISNUMBER(k))),IF(Nλ(q),1,Cλ(q,l)))))),b,IF(f,IF(Aλ(k)=1,i,FALSE),i),IF(AND(ISLOGICAL(f),Cλ(m,{0,1,2}),ISLOGICAL(Bλ(m))),b,#VALUE!)))) |
| C8 | C8 | =IF(1,"") |
| G5 | G5 | =ISNUMBERS({1,2,3}) |
| G6 | G6 | =ISNUMBERS({1,2,"book"}) |
| G7 | G7 | =ISNUMBERS({1,2,""}) |
| G8 | G8 | =ISNUMBERS({1,2,TRUE}) |
| G9 | G9 | =ISNUMBERS({1,2,FALSE}) |
| G10 | G10 | =ISNUMBERS({1,2,#VALUE!}) |
| B5:B13 | B5 | =XTYPE(C5,3) |
| D5:D13 | D5 | =ISNUMBERS(C5) |
| C13 | C13 | =1/0 |
| C17 | C17 | =ISNUMBERS({3,4.75,-2,0,63}) |
| C18 | C18 | =ISNUMBERS({3,4.75,-2,0,63},,1) |
| C19 | C19 | =ISNUMBERS({3}) |
| C20 | C20 | =ISNUMBERS({3},,1) |
| C21 | C21 | =ISNUMBERS(-52.6) |
| C22 | C22 | =ISNUMBERS(-52.6,,1) |
| C23 | C23 | =ISNUMBERS({3,7,12,-3,""}) |
| C24 | C24 | =ISNUMBERS({3,7,12,-3,""},,1) |
| G17 | G17 | =ISNUMBERS({2,5,9,4},,,{1,2,3,4,5,6,7,8,9,10,"",#DIV/0!}) |
| G18 | G18 | =ISNUMBERS({2,5,9,4},,,{1,2,3,4,5,6,7,8,9,10,"",#DIV/0!},1) |
| G19 | G19 | =ISNUMBERS({2,5,9,4},,,{1,2,3,4,5,6,7,8,9,10,"",#DIV/0!},2) |
| G20 | G20 | =ISNUMBERS({2,5,"",9,4},,,{1,2,3,4,5,6,7,8,9,10,"",#DIV/0!}) |
| G21 | G21 | =ISNUMBERS({2,5,"",9,4},,,{1,2,3,4,5,6,7,8,9,10,"",#DIV/0!},1) |
| G22 | G22 | =ISNUMBERS({2,5,"",9,4},,,{1,2,3,4,5,6,7,8,9,10,"",#DIV/0!},2) |
| G23 | G23 | =ISNUMBERS({2,5,#DIV/0!,9,4},,,{1,2,3,4,5,6,7,8,9,10,"",#DIV/0!}) |
| G24 | G24 | =ISNUMBERS({2,5,#DIV/0!,9,4},,,{1,2,3,4,5,6,7,8,9,10,"",#DIV/0!},1) |
| G25 | G25 | =ISNUMBERS({2,5,#DIV/0!,9,4},,,{1,2,3,4,5,6,7,8,9,10,"",#DIV/0!},2) |
| G26 | G26 | =ISNUMBERS({2,5,TRUE,9,4},,,{1,2,3,4,5,6,7,8,9,10,"",TRUE}) |
| G27 | G27 | =ISNUMBERS({2,5,TRUE,9,4},,,{1,2,3,4,5,6,7,8,9,10,"",TRUE},1) |
| G28 | G28 | =ISNUMBERS({2,5,TRUE,9,4},,,{1,2,3,4,5,6,7,8,9,10,"",TRUE},2) |
| K17 | K17 | =ISNUMBERS({2,5,9,4},,,{1,2,3,4,5,6,7,8,9,10}) |
| K18 | K18 | =ISNUMBERS({2,5,9,4},,,{1,2,3,4,5,6,7,8,9,10},1) |
| K19 | K19 | =ISNUMBERS({2,5,9,4},,,{1,2,3,4,5,6,7,8,9,10},2) |
| K20 | K20 | =ISNUMBERS({2,5,"",9,4},,,{1,2,3,4,5,6,7,8,9,10}) |
| K21 | K21 | =ISNUMBERS({2,5,"",9,4},,,{1,2,3,4,5,6,7,8,9,10},1) |
| K22 | K22 | =ISNUMBERS({2,5,"",9,4},,,{1,2,3,4,5,6,7,8,9,10},2) |
| K23 | K23 | =ISNUMBERS({2,5,#DIV/0!,9,4},,,{1,2,3,4,5,6,7,8,9,10}) |
| K24 | K24 | =ISNUMBERS({2,5,#DIV/0!,9,4},,,{1,2,3,4,5,6,7,8,9,10},1) |
| K25 | K25 | =ISNUMBERS({2,5,#DIV/0!,9,4},,,{1,2,3,4,5,6,7,8,9,10},2) |
| K26 | K26 | =ISNUMBERS({2,5,TRUE,9,4},,,{1,2,3,4,5,6,7,8,9,10}) |
| K27 | K27 | =ISNUMBERS({2,5,TRUE,9,4},,,{1,2,3,4,5,6,7,8,9,10},1) |
| K28 | K28 | =ISNUMBERS({2,5,TRUE,9,4},,,{1,2,3,4,5,6,7,8,9,10},2) |
Upvote
0
