I. Intro
These 12 IS functions can be used for inspecting and validating value types. The results returned are either TRUE or FALSE depending on the parameter value.
ISNUMBER function
ISBLANK function
ISERR function
ISERROR function
ISLOGICAL function
ISNA function
ISNONTEXT function
ISREF function
ISTEXT function
ISFORMULA function
ISEVEN function
ISODD function
II. About the function
- Formula:
- =ISNUMBER(value)
- =ISBLANK(value)
- =ISERR(value)
- =ISERROR(value)
- =ISLOGICAL(value)
- =ISNA(value)
- =ISNONTEXT(value)
- =ISREF(value)
- =ISTEXT(value)
- =ISFORMULA(value)
- =ISEVEN(number)
- =ISODD(number)
- Parameters:
- Value: The value that needs to be validated.
- Number: The number that needs to be validated.
- Formula explanation:
- =ISNUMBER(value) returns TRUE when the value is a number. If not, FALSE is returned.
- =ISNBLANK(value) returns TRUE when the cell is blank. If not, FALSE is returned.
- =ISERR(value) returns TRUE when the value is any error value except #N/A. If not, FALSE is returned.
- =ISERROR(value) returns TRUE if the value is any error value (including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!). If not, FALSE is returned.
- =ISLOGICAL(value) returns TRUE when the value is a logical value. If not, FALSE is returned.
- Logical value means TRUE or FALSE.
- =ISNA(value) returns TRUE when the value is the error value #N/A (the value doesn't exist). If not, FALSE is returned.
- =ISNONTEXT(value) returns TRUE when the value is any item that is not text. If not, FALSE is returned.
- =ISREF(value) returns TRUE when the value is a reference. If not, FALSE is returned.
- =ISTEXT(value) returns TRUE when the value is text. If not, FALSE is returned.
- =ISFORMULA(value) returns TRUE when the value contains a formula. If not, FALSE is returned.
- =ISEVEN(number) returns TRUE when the number is even. If not, FALSE is returned.
- =ISODD(number) returns TRUE when the number is odd. If not, FALSE is returned.
- Example:
- =ISNUMBER(6) results in TRUE.
- =ISNUMBER("good") results in FALSE.
- =ISERR(#REF!) results in TRUE.
- =ISERROR(#NUM!) results in TRUE.
- =ISLOGICAL(TRUE) results in TRUE.
- =ISNA(#N/A) results in TRUE.
- =ISNONTEXT(45) results in TRUE.
- =ISREF(A1) results in TRUE.
- =ISTEXT(6) results in FALSE.
- =ISFORMULA(=SUM(B1:C5)) results in TRUE.
- =ISEVEN(4) results in TRUE.
- =ISODD(4) results in FALSE.
III. Steps
Use the IS function
- Select a cell and click Formulas in the toolbar, select Info, and then one of the IS functions. You can also directly enter one of the IS functions in a cell.
- Enter the parameters in the cell. For example, =ISNUMBER(B2).
- Press Enter to display the result, which is TRUE in this example.
250px|700px|reset
Delete the IS function
Select the cell with the IS function and press Delete.
IV. Scenarios
Auditing: Quickly find blank cells
Some required fields may have been missed by clients. ISBLANK can be used to check if there is any missing information.
- Formula used below: =ISBLANK(C2)
- About the parameters: This function checks whether a cell is empty. If it is empty, TRUE will be returned, and you can take a look at the TRUE results to see what is missing.
250px|700px|reset
Education: Sort grades
Sometimes a letter grade is given as a score instead of a number. In this case, the ISTEXT function can quickly identify the letter grades from the numbers.
- Formula used below: =ISTEXT(B2)
- About the parameters: The function checks whether the score given is a letter grade. If it is a letter grade, TRUE will be returned. If you'd rather filter numerical results instead of grades, you can use the ISNUMBER function instead.
250px|700px|reset