Functions for conditional statements

These functions test if the data meets a given condition and enable you specify an action dependent on the outcome of that condition.

IF

Tests a condition and returns the given value of then if the condition is TRUE, and returns the given value of else if the condition is FALSE.

IF(data, then, else)

Example:
data = "yes"
IF(data = "yes", 10, 20) = 10

IFBETWEEN ❖ XLReporting

If the value is between the given from and to parameters, then returns the given value of then, else returns the given value of else. If then and else are ommitted, it returns the tested value as then and an empty value as else.

IFBETWEEN(data, from, to, then, else)

Example:
data = "2019-02"
IFBETWEEN(data, "2019-02", "2019-03", "yes", "no") = "yes"

IFBLANK

Returns the result of an expression, but if the result is empty, then returns the given value of the then parameter. This enables you to use a default value if a column, field, or expression is empty.

IFBLANK(data, then)

Example:
data = ""
IFBLANK(data, "no") = "no"

IFERROR

Returns the result of an expression, or the given value of then if the expression results in an error. This enables you to use a default value if an expression results in an error.

IFERROR(data, then)

Examples:
IFERROR(12 / 3, 5) = 4
IFERROR(12 / 0, 5) = 5

IFS

Tests multiple expressions and returns the then parameter of the first expression that is TRUE. The number of expressions and results is unlimited. Returns an empty value if no expression is met.

The parameters always need to be given in pairs (the condition with the associated outcome). This function has no "else" parameter by design. You can combine this function with IFBLANK() if needed, for example: IFBLANK(IFS(if, then, if, then), else).

IFS(data, then, data, then, ..)

Example:
data = 12
IFS(data > 10, "more then 10", data <= 10, "less than 10) = "more then 10"

IFZERO

Returns the result of an expression, but if the result is empty, zero, or not a numeric value, then returns the given value of the then parameter. This enables you to use a default value if a column, field, or expression is effectively zero.

IFZERO(data, then)

Examples:
data = "aa"
IFZERO(data, 4) = 4
IFZERO(10 - 10, 4) = 4