ISINT allows for checking if the reference contains integer value(s) and provides logical output
ISINT takes three parameters, one required and two optional, as follows:
A) The first parameter, required, specifies the input and can be a single cell, range, or array.
B) The second parameter, optional, specifies the type of logical output data and takes one of two arguments:
0 or omitted or FALSE, for logical TRUE/FALSE output
1 or TRUE or any other number besides 0, for numeric 1/0 output
C) The third parameter, optional, specifies what output to display for function entries that are not numbers and can take a single entry of any data type (i.e. number, text, blank, logical, or error). If omitted, an #N/A error will be displayed by default
The following helper functions are also used in the code. Full code and descriptions are included in my "Blank power workbook" template, which you can download through the link in my signature below:
ISARRAY (Returns TRUE if the reference is an array [i.e. is not a single element or is not omitted] and FALSE otherwise)
PLSL (Returns 1 if the parameter is a single TRUE entry; 0 if the parameter is a single FALSE entry or is omitted; and #N/A otherwise)
OT (Returns "TRUE" if the reference is TRUE, "1" if the reference is FALSE, and #N/A otherwise)
OF (Returns "FALSE" if the reference is TRUE, "0" if the reference is FALSE, and #N/A otherwise)
T1F0 (Returns 1 if the reference is TRUE and 0 if the reference is FALSE)
IO (shortened form of ISOMITTED)
ISINT takes three parameters, one required and two optional, as follows:
A) The first parameter, required, specifies the input and can be a single cell, range, or array.
B) The second parameter, optional, specifies the type of logical output data and takes one of two arguments:
0 or omitted or FALSE, for logical TRUE/FALSE output
1 or TRUE or any other number besides 0, for numeric 1/0 output
C) The third parameter, optional, specifies what output to display for function entries that are not numbers and can take a single entry of any data type (i.e. number, text, blank, logical, or error). If omitted, an #N/A error will be displayed by default
VBA Code:
=LAMBDA(reference,[output],[if_not_number],
IF(
OR(ISARRAY(output),ISARRAY(if_not_number)),
NA(),
LET(
r,IF(ISNUMBER(reference),reference,NA()),
o,PLSL(output),
e,IF(MOD(r,1),OF(o),OT(o)),
IF(
ISERROR(e),
IF(IO(if_not_number),e,if_not_number),
e
)
)
)
)The following helper functions are also used in the code. Full code and descriptions are included in my "Blank power workbook" template, which you can download through the link in my signature below:
ISARRAY (Returns TRUE if the reference is an array [i.e. is not a single element or is not omitted] and FALSE otherwise)
VBA Code:
=LAMBDA([reference],[output],LET(r,IFERROR(IF(reference="","",reference),""),o,PLSL(output),m,IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE)),IF(m,OT(o),OF(o))))PLSL (Returns 1 if the parameter is a single TRUE entry; 0 if the parameter is a single FALSE entry or is omitted; and #N/A otherwise)
VBA Code:
=LAMBDA([parameter],IF(IO(parameter),0,IF(ISARRAY(parameter),NA(),LET(p,INDEX(parameter,1),IF(ISERROR(p),NA(),T1F0(p))))))OT (Returns "TRUE" if the reference is TRUE, "1" if the reference is FALSE, and #N/A otherwise)
VBA Code:
=LAMBDA(reference,IF(ISERROR(IF(reference="",NA(),reference)),NA(),IFERROR(IF(reference,1,TRUE),NA())))OF (Returns "FALSE" if the reference is TRUE, "0" if the reference is FALSE, and #N/A otherwise)
VBA Code:
=LAMBDA(reference,IF(ISERROR(IF(reference="",NA(),reference)),NA(),IFERROR(IF(reference,0,FALSE),NA())))T1F0 (Returns 1 if the reference is TRUE and 0 if the reference is FALSE)
VBA Code:
=LAMBDA(reference,[analysis_type],LET(a,PLSL(analysis_type),b,IF(ISNUMBER(reference)+ISLOGICAL(reference),reference,SWITCH(a,0,NA(),1,0)),IF(b,1,0)))IO (shortened form of ISOMITTED)
VBA Code:
=LAMBDA(parameter,ISOMITTED(parameter))| ISINT.xlsx | ||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||
| 1 | ||||||||||||||||||||||||||||
| 2 | #CALC! | |||||||||||||||||||||||||||
| 3 | ||||||||||||||||||||||||||||
| 4 | 43 | book | 46.89 | 20 | TRUE | |||||||||||||||||||||||
| 5 | #CALC! | 0 | notebook | 28 | ||||||||||||||||||||||||
| 6 | 14 | 23 | #DIV/0! | 24 | if_not_number | |||||||||||||||||||||||
| 7 | 64 | FALSE | 56 | notebook | 37 | not num | ||||||||||||||||||||||
| 8 | ||||||||||||||||||||||||||||
| 9 | output | omitted | output | omitted | output | omitted | output | omitted | ||||||||||||||||||||
| 10 | References entered individually | References entered as range (for spill) | References entered individually | References entered as range (for spill) | ||||||||||||||||||||||||
| 11 | TRUE | #N/A | FALSE | TRUE | #N/A | TRUE | #N/A | FALSE | TRUE | #N/A | TRUE | not num | FALSE | TRUE | not num | TRUE | not num | FALSE | TRUE | not num | ||||||||
| 12 | #N/A | TRUE | #N/A | #N/A | TRUE | #N/A | TRUE | #N/A | #N/A | TRUE | not num | TRUE | not num | not num | TRUE | not num | TRUE | not num | not num | TRUE | ||||||||
| 13 | TRUE | TRUE | #N/A | TRUE | #N/A | TRUE | TRUE | #N/A | TRUE | #N/A | TRUE | TRUE | not num | TRUE | not num | TRUE | TRUE | not num | TRUE | not num | ||||||||
| 14 | TRUE | #N/A | TRUE | #N/A | TRUE | TRUE | #N/A | TRUE | #N/A | TRUE | TRUE | not num | TRUE | not num | TRUE | TRUE | not num | TRUE | not num | TRUE | ||||||||
| 15 | ||||||||||||||||||||||||||||
| 16 | output | TRUE | output | TRUE | output | TRUE | output | TRUE | ||||||||||||||||||||
| 17 | References entered individually | References entered as range (for spill) | References entered individually | References entered as range (for spill) | ||||||||||||||||||||||||
| 18 | 1 | #N/A | 0 | 1 | #N/A | 1 | #N/A | 0 | 1 | #N/A | 1 | not num | 0 | 1 | not num | 1 | not num | 0 | 1 | not num | ||||||||
| 19 | #N/A | 1 | #N/A | #N/A | 1 | #N/A | 1 | #N/A | #N/A | 1 | not num | 1 | not num | not num | 1 | not num | 1 | not num | not num | 1 | ||||||||
| 20 | 1 | 1 | #N/A | 1 | #N/A | 1 | 1 | #N/A | 1 | #N/A | 1 | 1 | not num | 1 | not num | 1 | 1 | not num | 1 | not num | ||||||||
| 21 | 1 | #N/A | 1 | #N/A | 1 | 1 | #N/A | 1 | #N/A | 1 | 1 | not num | 1 | not num | 1 | 1 | not num | 1 | not num | 1 | ||||||||
| 22 | ||||||||||||||||||||||||||||
| 23 | output | FALSE | output | FALSE | output | FALSE | output | FALSE | ||||||||||||||||||||
| 24 | References entered individually | References entered as range (for spill) | References entered individually | References entered as range (for spill) | ||||||||||||||||||||||||
| 25 | TRUE | #N/A | FALSE | TRUE | #N/A | TRUE | #N/A | FALSE | TRUE | #N/A | TRUE | not num | FALSE | TRUE | not num | TRUE | not num | FALSE | TRUE | not num | ||||||||
| 26 | #N/A | TRUE | #N/A | #N/A | TRUE | #N/A | TRUE | #N/A | #N/A | TRUE | not num | TRUE | not num | not num | TRUE | not num | TRUE | not num | not num | TRUE | ||||||||
| 27 | TRUE | TRUE | #N/A | TRUE | #N/A | TRUE | TRUE | #N/A | TRUE | #N/A | TRUE | TRUE | not num | TRUE | not num | TRUE | TRUE | not num | TRUE | not num | ||||||||
| 28 | TRUE | #N/A | TRUE | #N/A | TRUE | TRUE | #N/A | TRUE | #N/A | TRUE | TRUE | not num | TRUE | not num | TRUE | TRUE | not num | TRUE | not num | TRUE | ||||||||
| 29 | ||||||||||||||||||||||||||||
| 30 | output | 43 | (TRUE) | output | 43 | (TRUE) | output | 43 | (TRUE) | output | 43 | (TRUE) | ||||||||||||||||
| 31 | References entered individually | References entered as range (for spill) | References entered individually | References entered as range (for spill) | ||||||||||||||||||||||||
| 32 | 1 | #N/A | 0 | 1 | #N/A | 1 | #N/A | 0 | 1 | #N/A | 1 | not num | 0 | 1 | not num | 1 | not num | 0 | 1 | not num | ||||||||
| 33 | #N/A | 1 | #N/A | #N/A | 1 | #N/A | 1 | #N/A | #N/A | 1 | not num | 1 | not num | not num | 1 | not num | 1 | not num | not num | 1 | ||||||||
| 34 | 1 | 1 | #N/A | 1 | #N/A | 1 | 1 | #N/A | 1 | #N/A | 1 | 1 | not num | 1 | not num | 1 | 1 | not num | 1 | not num | ||||||||
| 35 | 1 | #N/A | 1 | #N/A | 1 | 1 | #N/A | 1 | #N/A | 1 | 1 | not num | 1 | not num | 1 | 1 | not num | 1 | not num | 1 | ||||||||
| 36 | ||||||||||||||||||||||||||||
Sheet1 | ||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2 | B2 | =LAMBDA(reference,[output],[if_not_number],IF(OR(ISARRAY(output),ISARRAY(if_not_number)),NA(),LET(r,IF(ISNUMBER(reference),reference,NA()),o,PLSL(output),e,IF(MOD(r,1),OF(o),OT(o)),IF(ISERROR(e),IF(IO(if_not_number),e,if_not_number),e)))) |
| B5 | B5 | =XERROR(14) |
| D6 | D6 | =1/0 |
| F6 | F6 | =IF(1,"") |
| B11:F14 | B11 | =ISINT(B4) |
| H11:L14 | H11 | =ISINT(B4:F7) |
| O11:S14 | O11 | =ISINT(B4,,$T$7) |
| U11:Y14 | U11 | =ISINT(B4:F7,,$T$7) |
| B18:F21 | B18 | =ISINT(B4,TRUE) |
| H18:L21 | H18 | =ISINT(B4:F7,TRUE) |
| O18:S21 | O18 | =ISINT(B4,TRUE,$T$7) |
| U18:Y21 | U18 | =ISINT(B4:F7,TRUE,$T$7) |
| B25:F28 | B25 | =ISINT(B4,FALSE) |
| H25:L28 | H25 | =ISINT(B4:F7,FALSE) |
| O25:S28 | O25 | =ISINT(B4,FALSE,$T$7) |
| U25:Y28 | U25 | =ISINT(B4:F7,FALSE,$T$7) |
| B32:F35 | B32 | =ISINT(B4,43) |
| H32:L35 | H32 | =ISINT(B4:F7,43) |
| O32:S35 | O32 | =ISINT(B4,43,$T$7) |
| U32:Y35 | U32 | =ISINT(B4:F7,43,$T$7) |
| Dynamic array formulas. | ||
Upvote
0
