ISINT

ISINT(reference,[output],[if_not_number])
reference
Required. Specifies the input and can be a single cell, range, or array
output
Optional. Specifies the type of logical output data and takes one of two arguments: 0 or omitted or FALSE, for logical TRUE/FALSE output; or 1 or TRUE or any other number besides 0, for numeric 1/0 output
if_not_number
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
ISINT allows for checking if the reference contains integer value(s) and provides logical output
R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
528
Office Version
  1. 365
Platform
  1. Windows
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

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
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2#CALC!
3
443book46.8920TRUE
5#CALC!0notebook28
61423#DIV/0!24 if_not_number
764FALSE56notebook37not num
8
9outputomittedoutputomittedoutputomittedoutputomitted
10References entered individuallyReferences entered as range (for spill)References entered individuallyReferences entered as range (for spill)
11TRUE#N/AFALSETRUE#N/ATRUE#N/AFALSETRUE#N/ATRUEnot numFALSETRUEnot numTRUEnot numFALSETRUEnot num
12#N/ATRUE#N/A#N/ATRUE#N/ATRUE#N/A#N/ATRUEnot numTRUEnot numnot numTRUEnot numTRUEnot numnot numTRUE
13TRUETRUE#N/ATRUE#N/ATRUETRUE#N/ATRUE#N/ATRUETRUEnot numTRUEnot numTRUETRUEnot numTRUEnot num
14TRUE#N/ATRUE#N/ATRUETRUE#N/ATRUE#N/ATRUETRUEnot numTRUEnot numTRUETRUEnot numTRUEnot numTRUE
15
16outputTRUEoutputTRUEoutputTRUEoutputTRUE
17References entered individuallyReferences entered as range (for spill)References entered individuallyReferences entered as range (for spill)
181#N/A01#N/A1#N/A01#N/A1not num01not num1not num01not num
19#N/A1#N/A#N/A1#N/A1#N/A#N/A1not num1not numnot num1not num1not numnot num1
2011#N/A1#N/A11#N/A1#N/A11not num1not num11not num1not num
211#N/A1#N/A11#N/A1#N/A11not num1not num11not num1not num1
22
23outputFALSEoutputFALSEoutputFALSEoutputFALSE
24References entered individuallyReferences entered as range (for spill)References entered individuallyReferences entered as range (for spill)
25TRUE#N/AFALSETRUE#N/ATRUE#N/AFALSETRUE#N/ATRUEnot numFALSETRUEnot numTRUEnot numFALSETRUEnot num
26#N/ATRUE#N/A#N/ATRUE#N/ATRUE#N/A#N/ATRUEnot numTRUEnot numnot numTRUEnot numTRUEnot numnot numTRUE
27TRUETRUE#N/ATRUE#N/ATRUETRUE#N/ATRUE#N/ATRUETRUEnot numTRUEnot numTRUETRUEnot numTRUEnot num
28TRUE#N/ATRUE#N/ATRUETRUE#N/ATRUE#N/ATRUETRUEnot numTRUEnot numTRUETRUEnot numTRUEnot numTRUE
29
30output43(TRUE)output43(TRUE)output43(TRUE)output43(TRUE)
31References entered individuallyReferences entered as range (for spill)References entered individuallyReferences entered as range (for spill)
321#N/A01#N/A1#N/A01#N/A1not num01not num1not num01not num
33#N/A1#N/A#N/A1#N/A1#N/A#N/A1not num1not numnot num1not num1not numnot num1
3411#N/A1#N/A11#N/A1#N/A11not num1not num11not num1not num
351#N/A1#N/A11#N/A1#N/A11not num1not num11not num1not num1
36
Sheet1
Cell Formulas
RangeFormula
B2B2=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))))
B5B5=XERROR(14)
D6D6=1/0
F6F6=IF(1,"")
B11:F14B11=ISINT(B4)
H11:L14H11=ISINT(B4:F7)
O11:S14O11=ISINT(B4,,$T$7)
U11:Y14U11=ISINT(B4:F7,,$T$7)
B18:F21B18=ISINT(B4,TRUE)
H18:L21H18=ISINT(B4:F7,TRUE)
O18:S21O18=ISINT(B4,TRUE,$T$7)
U18:Y21U18=ISINT(B4:F7,TRUE,$T$7)
B25:F28B25=ISINT(B4,FALSE)
H25:L28H25=ISINT(B4:F7,FALSE)
O25:S28O25=ISINT(B4,FALSE,$T$7)
U25:Y28U25=ISINT(B4:F7,FALSE,$T$7)
B32:F35B32=ISINT(B4,43)
H32:L35H32=ISINT(B4:F7,43)
O32:S35O32=ISINT(B4,43,$T$7)
U32:Y35U32=ISINT(B4:F7,43,$T$7)
Dynamic array formulas.
 
Upvote 0
Updated code:
1) fixes an issue where incorrect entries for the "output" parameter did not lead the function to crash to a single #N/A
2) simplifies the code

ISINT
VBA Code: 
=LAMBDA(reference,[output],[if_not_number],
   IF(
      OR(JAN(output),JAN(if_not_number)),
      NA(),
      LET(
         o,PLSL(output),
         IF(
            ISERROR(o),
            NA(),
            LET(
               r,IF(ISNUMBER(reference),reference,NA()),
               e,IF(MOD(r,1),OF(o),OT(o)),
               IF(
                  ISERROR(e),
                  IF(IO(if_not_number),e,if_not_number),
                  e
               )
            )
         )
      )
   )
)

JAN (Helper function: 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))))

PLSL (stands for "parameter limit, single, logical")
(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(JAN(parameter),NA(),LET(p,INDEX(parameter,1),IF(ISERROR(p),NA(),T1F0(p))))))

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

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())))

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())))

T1F0 (Returns 1 if the reference is TRUE, 0 if the reference is FALSE, and #N/A otherwise)
VBA Code: 
=LAMBDA(expression,IF(ISNUMBER(expression)+ISLOGICAL(expression),IF(expression,1,0),NA()))
 
You must log in or register to reply here.

Forum statistics

Threads
1,215,111
Messages
6,123,152
Members
449,098
Latest member
Doanvanhieu

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