Conditional Operators

When using conditional operators, consider the following:

  • Expression can be any valid formula expression. The expression can be any combination of a constant (integer or real number), a reference, or another function.

  • Reference can be any valid reference; thus the IFNN reference property can be utilized as part of the reference.

  • Condition can be any valid condition applied to the complex conditions And, Not, and Or. These operators can have embedded conditions. (And, Not, and Or operators require surrounding parentheses.)

  • When any expression within the condition returns an #error or #missing value, the If function returns #missing or #error. This does not apply when you use the IsMissing, IsError, or IsNonNumeric conditions.

Table 11-9 Conditional Operators

Conditional Operator Syntax Logic

Equal To

expression = expression

Tests if the left expression is equal to the right expression.

Example:

1=4

Returns false

Note: If rounding is required, use the Round function.

Greater Than

expression > expression

Tests if the left expression is greater than the right expression.

Example:

1 > 4

Returns false

Greater Than or Equal To

expression >= expression

Tests if the left expression is greater than or equal to the right expression.

Example:

1>=4

Returns false

Note: The correct syntax is ">=". The syntax "=>" is not supported.

Less Than

expression < expression

Tests if the left expression is less than the right expression.

Example:

1<4

Returns true

Less Than or

Equal To

expression <= expression

Tests if the left expression is less than or equal to the right expression.

Example:

1<=4

Returns true

Note: The correct syntax is "<=". The syntax "=<" is not supported.

Not Equal To

expression <> expression

expression != expression

Tests if the left expression is not equal to the right expression.

Example:

1<>4

Returns true

1!=4

Returns true

Note: If rounding is required, use the Round function.

IsMissing

IsMissing (reference)

IsMiss (reference)

Tests if the reference contains a #missing result.

Example:

IsMissing([1])

Returns true if row 1 has a #missing value.

Note: If the reference is an expanded row or column, then all resulting cells must be #missing in order for the condition to be true.

IsError

IsError (reference)

IsErr (reference)

Tests if the reference contains an #error result.

Example:

IsError([2])

Returns true if row 2 has a #error value.

Note: If the reference is an expanded row or column, all resulting cells must be #error in order for the condition to be true. Only formula rows and columns can result in #error.

IsNonNumeric

IsNN (reference)

IsNonNumerid (reference)

IfNN (reference)

IfNonNumber (reference)

Tests if the reference contains a #missing or #error results.

Example:

IsNN([3])

Returns true if row 3 has a #missing or #error value.

Note: If the reference is an expanded row or column, all resulting cells must be #missing and/or #error in order for the condition to be true.

Parenthesis

(condition)

Groups a condition.

Example:

(1 > 4)

Returns false