Conditional Functions

CASE

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

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

Parameter	Type	Description
`Value`	`Text/Joined`	Value to check.
`Match1`, `Match2`, ..., `MatchN`	`Text/Joined`	Text 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.
`Result1`, `Result2`, ..., `ResultN`	`Any`	Values to return from the function, each value corresponds to the preceding `Match` parameter.
`Default` (Optional)	`Any`	Optional default value, to be returned if none of the patterns match. If not specified, `undefined` is returned.
→ Result	`Any`	Result1, 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.

Parameter	Type	Description
`Index`	`Number`	Numeric index, with 1 corresponding to `Value1`, 2 corresponding to `Value2` and so on.
`Value1`, `Value2`, ..., `MatchN`	`Any`	The values to pick from.
→ Result	`Any`	The 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.

Parameter	Type	Description
`Value`	`Any`	Value to check.
→ Result	`Boolean`	Returns 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.

Parameter	Type	Description
`Value`	`Any`	Value to check.
`DefaultValue`	Any	Value to be returned if `Value` is `undefined`.
→ Result	`Any`	If `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.

Parameter	Type	Description
`Condition1, Condition2, ..., Condition3`	`Any`	Value 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.
`Result1`, `Result2`, ..., `ResultN`	`Any`	Results to be returned, each result corresponding to the preceding check.
`Default` (Optional)	`Any`	Optional default value, to be returned if none of the patterns match. If not specified, `undefined` is returned.
→ Result	`Any`	Result1, 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.

Parameter	Type	Description
`Value`	`Any`	Value to check.
`FallbackValue`	`Any`	Value to be returned if calculating `Value` produces an error.
→ Result	`Any`	If `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.

Parameter	Type	Description
`Value`	`Any`	Value to check.
`ErrorCode` (Optional)	`Integer`	Optional error code. See Expr Error Codes for a list.
→ Result	`Boolean`	Returns 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