Purpose

Return value

Syntax

=ISLOGICAL(value)
  • value - The value to test as logical.

Using the ISLOGICAL function

The ISLOGICAL function returns TRUE when a cell contains the logical values TRUE or FALSE, and returns FALSE for cells that contain any other value, including empty cells.

The ISLOGICAL function takes one argument, value , which can be a cell reference, a formula, or a hardcoded value. When value is TRUE or FALSE, the ISLOGICAL function will return TRUE. If value is any other value, ISLOGICAL will return FALSE.

Examples

The ISLOGICAL function returns TRUE if value is TRUE or FALSE:

=ISLOGICAL(TRUE) // returns TRUE
=ISLOGICAL(FALSE) // returns TRUE

If value is a formula, ISLOGICAL checks the result of the formula:

=ISLOGICAL(100>50) // returns TRUE
=ISLOGICAL(2+2) // returns FALSE
=ISLOGICAL(A1=B1) // returns TRUE

Note that 1 and 0 (zero) are not evaluated as TRUE and FALSE.

=ISLOGICAL(1) // returns FALSE
=ISLOGICAL(0) // returns FALSE

Count logicals

To count cells in a range that contain logicals, you can use the SUMPRODUCT function like this:

=SUMPRODUCT(--ISLOGICAL(range))

The double negative coerces the TRUE and FALSE results from ISLOGICAL into 1s and 0s and SUMPRODUCT sums the result.

Notes

  • Only the logical values TRUE and FALSE return TRUE

Purpose

Return value

Syntax

=ISNA(value)
  • value - The value to check if #N/A.

Using the ISNA function

The ISNA function returns TRUE when a cell contains the #N/A error and FALSE for any other value, or any other error type. The ISNA function takes one argument, value , which is typically a cell reference.

Examples

If A1 contains the #N/A error, ISNA returns TRUE:

=ISNA(A1) // returns TRUE

ISNA returns FALSE for other values and errors:

=ISNA(100) // returns FALSE
=ISNA(5/0) // returns FALSE

You can use the ISNA function with the IF function test for #N/A and display a friendly message if the error occurs. For example, to display a message if A1 contains #N/A and the value of A1 if not:

=IF(ISNA(A1),"message",A1)

The IFNA function is a more efficient way to trap the #N/A error. See VLOOKUP without NA error for an example.

Return #N/A

To explicitly return the #N/A error in a formula, you can use the NA function :

=NA() // returns #N/A error

The following will return true:

=ISNA(NA()) // returns TRUE

Count #N/A errors

To count cells in a range that contain #N/A errors, you can use the SUMPRODUCT function like this:

=SUMPRODUCT(--ISNA(range))

The double negative coerces the TRUE and FALSE results from ISNA into 1s and 0s and SUMPRODUCT sums the result.

Notes

  • The IFNA function is a more efficient way to trap and handle the #N/A error.