ISBLANK

Description

The ISBLANK function tests whether a value is blank.

Usage

ISBLANK(value)

Argument Type Required Description
value Any Yes The value you wish to test is blank.

Result

  • TRUE if the value is a blank.
  • FALSE if the value is not blank.

Remarks

All ActivityInfo formula types include a "blank" value. Blank values can result from an optional form field that hasn't been filled in. Some functions like SEARCH can also return a blank value in some conditions.

Examples

Missing data analysis

The ISBLANK function can be used to test whether a form field has been completed.

For example, you might have a form with an optional field for AGE. For the purposes of analysis, and you are confident that this data is Missing Completely at Random (MCAR), you might want to use your population's average age if the field value is missing.

The formula below will evaluate to 22 if the AGE field is blank, otherwise we'll use the age provided in the form.

IF(ISBLANK(AGE), 22, AGE)

You can also use the ISBLANK function to analyze missing-ness in your data. You could count the number of blank values in the AGE field using a pivot table to determine whether there is the same proportion of missing data between other variables, like urban or rural locations, or between men and women.

IF(ISBLANK(AGE), 1, 0)

You can add these formulas to a pivot table by adding a calculated field:

Screenshot of pivot table editor
Screenshot of pivot table editor

Blank strings and numbers

In many database systems and programming languages, blank or NULL values are distinct from an empty text string. In SQL, for example, the expression "" IS NULL is always false. And in R, the expression is.na("") is also false.

ActivityInfo, however, always treats empty strings as blanks:

ISBLANK("") == TRUE
ISBLANK("  ") == FALSE
ISBLANK("Bob") == FALSE
ISBLANK(LEFT("Alice",0)) == TRUE

Arithmatic errors like division by zero are also treated as blank values.

ISBLANK(1/0) == TRUE
Next item
ISNUMBER