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.

This function is useful for creating dynamic logic in models, which is such as applying different formulas depending on data values (e.g. approved vs. rejected, or profit vs. loss).

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.

This function is useful for tiered logic such as pricing levels, rating bands, or date-based conditions like fiscal period ranges.

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.

This function is useful for filling missing values in reports and models, ensuring calculations always return usable outputs.

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.

This function is useful for safeguarding against division by zero, failed lookups, or invalid expressions in financial models.

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).

This function is useful for complex multi-tiered decisions, such as scoring models, risk categorization, or budget allocation rules.

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.

This function is useful for replacing zero values with defaults, which is ideal for avoiding distortion in ratios or ensuring fallback amounts in reports.

IFZERO(data, then)

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