CASE

CASE(Value, Match1, Result1, Match2, Result2, ..., Default)

Checks if the Value matches against several checks and returns a corresponding result. 

ParameterTypeDescription
ValueText/JoinedValue to check.
Match1, Match2, ..., MatchNText/JoinedText patterns to check against. The first matching pattern will define the result. A pattern can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
Result1Result2, ..., ResultN AnyValues to return from the function, each value corresponds to the preceding Match parameter.
Default (Optional)AnyOptional default value, to be returned if none of the patterns match. If not specified, undefined is returned.
ResultAnyResult1, Result2, etc. depending on which pattern matched, or Default, or undefined.

This function is typically used to map text values to numbers.

Examples:

  • CASE(Priority; "Highest"; 10; "High"; 5; "Medium"; 3; 1)
  • CASE(Version; "V1*"; 1; "V2*"; 2)
If the Value is undefined, the function immediately returns the Default result (or undefined if there's no default), so there is usually no need to use undefined as one of the matches.

CHOOSE

CHOOSE(Index; Value1; Value2; ...)

Based on the value of Index, returns the corresponding value from the argument list.

ParameterTypeDescription
IndexNumberNumeric index, with 1 corresponding to Value1, 2 corresponding to Value2 and so on.
Value1Value2, ..., MatchNAnyThe values to pick from.
ResultAnyThe Value corresponding to Index.

Examples:

  • CHOOSE(1; "A"; "B"; "C") → "A"
  • CHOOSE(2; "A"; "B"; "C") → "B"

DEFINED

DEFINED(Value)

Checks if the value is defined. 

ParameterTypeDescription
ValueAnyValue to check.
ResultBooleanReturns false (0) if Value is undefined and true (1) otherwise.

Example:

  • IF(DEFINED(Resolution); ...)

DEFAULT

DEFAULT(Value; DefaultValue)

Substitutes DefaultValue if the Value is undefined.

ParameterTypeDescription
ValueAnyValue to check.
DefaultValueAny

Value to be returned if Value is undefined.

ResultAnyIf Value is defined, returns Value. Otherwise, returns DefaultValue.

Examples:

  • DEFAULT(100; 500) → 100
  • DEFAULT(undefined; 500) → 500

IF

IF(Condition1; Result1; Condition2; Result2; ...; Default)

Checks one or several conditions and returns the result associated with the first true condition.

ParameterTypeDescription
Condition1Condition2, ..., Condition3AnyValue to check. The values are evaluated using "truthfulness check" – the first value that is "truthy" (not undefined, not zero and not an empty string), will define the returned value.
Result1Result2, ..., ResultN AnyResults to be returned, each result corresponding to the preceding check.
Default (Optional)AnyOptional default value, to be returned if none of the patterns match. If not specified, undefined is returned.
ResultAnyResult1, Result2, etc. depending on which pattern matched, or Default, or undefined.

Examples:

  • IF(Estimate > 0; Duration / Estimate; 0)
  • IF(N = 0; "No apples"; N = 1; "One apple"; CONCAT(N; " apples"))

IFERR

IFERR(Value; FallbackValue)

Checks if calculating Value produced an error and substitutes FallbackValue instead of the error value.

ParameterTypeDescription
ValueAnyValue to check.
FallbackValueAny

Value to be returned if calculating Value produces an error.

ResultAnyIf Value calculated without an error, returns Value. Otherwise, returns DefaultValue.

Normally, if an error occurs while calculating a formula, it is propagated upwards, and the result of the whole expression will be an error. This function helps circumvent that.

Example:

  • IFERR(100 / 0; 100) → 100

ISERR

ISERR(Value; ErrorCode)

Checks if calculating value produced an error. 

ParameterTypeDescription
ValueAnyValue to check.
ErrorCode (Optional)Integer

Optional error code. See Expr Error Codes for a list.

ResultBooleanReturns true (1) if there was an error. If ErrorCode is specified, returns true only if the error was of the specified error code.

Examples:

  • ISERR("Ham") → 0
  • ISERR(1 / 0) → 1
  • ISERR(1 / 0, 4) → 1  //Note: Error code 4 is an Arithmetic Error