Conditional statements let you perform calculations based on specified conditions. These functions evaluate expressions and return different values depending on whether the conditions are true or false. They are commonly used to apply business logic, categorize data, filter results, and handle exceptions.
In planning, conditional statements help you create dynamic calculations that adapt to different scenarios, such as applying thresholds, categorizing data, or managing error conditions.
The AVERAGEIF function returns the average of values in a list that meet a specified condition.
AVERAGEIF(list, condition)list: The list of values to evaluate.condition: The condition used to filter the values.Returns the average of the values that satisfy the given condition.
AVERAGEIF([Sales,Sales1,Sales2], ">0.1m")Returns the average of Sales, Sales1, and Sales2 column values when they are greater than 0.1 million.
The COUNTIF function returns the count of values that match a specified condition.
COUNTIF(list, condition)list: The list of values to evaluate.condition: The condition used to filter the values.Returns the number of values that satisfy the given condition.
COUNTIF([[2024.Plan_Variance %], [2025.Plan_Variance %], [2026.Plan_Variance %]], ">0")Returns the count of Plan Variance % values across 2024, 2025, and 2026 that are greater than 0.
The FILTERIF function filters a list of values based on a specified condition and returns the filtered results as an array.
Because it returns an array, it is typically used with aggregate functions such as SUM or AVERAGE. It can also be used to populate a calculated row.
FILTERIF(list, condition)list: The list of values to evaluate.condition: The condition used to filter the values.Returns an array of values that meet the specified condition.
SUM(FILTERIF([[Sales], [Sales1], [Sales2]], ">0.1m"))Filters values from Sales, Sales1, and Sales2 that are greater than 0.1 million and returns their sum.
The IF function returns one value if a condition is true and another value if it is false.
IF(logical_test, value_if_true, value_if_false )logical_test: The condition to evaluate.value_if_true: The value returned if the condition is true.value_if_false: The value returned if the condition is false.Returns either value_if_true or value_if_false based on the result of the condition.
IF(PY > 0, (AC - PY) / PY, 0)Returns (AC - PY) / PY if PY is greater than 0; otherwise, returns 0.
The IFNA function returns the specified value if an expression results in the #N/A error; otherwise, it returns the result of that expression.
IFNA(value, value_if_na)value: The expression to evaluate for the #N/A error.value_if_na: The value to return if the expression results in the #N/A error.Returns either the result of the value or value_if_na if the #N/A error occurs.
IFNA((Actuals - Plan) / Plan, 0)Returns (Actuals - Plan) / Plan if the expression evaluates successfully; otherwise, returns 0 if the #N/A error occurs.
The SUMIF function returns the sum of values in a list that satisfy a specified condition.
SUMIF(list, condition, [range])list: The list of values to evaluate.condition: The condition used to filter values in the list.range (optional): The list of values to sum if the values in the list meet the condition. If not specified, the values in the original list are summed.Returns the sum of values that satisfy the condition.
Without range
SUMIF([10, 20, 30, 40], ">20")In this example, the SUMIF function returns the sum of values greater than 20 from the list and returns 70 (30 + 40).
With range
SUMIF([10, 20, 30, 40], ">20", [1, 2, 3, 4])The SUMIF function evaluates the values in the first list and returns the sum of corresponding values from the second list where the condition is met. In this example, the result is 7 (3 + 4).
The SWITCH function evaluates an expression against a list of values and returns the result corresponding to the first matching value. If no match is found, an optional default value is returned.
SWITCH(expression, val1, res1, val2, res2, ..., val_n, res_n, default)expression: The expression to evaluate.val1, val2, ... val_n: The values to compare against the expression.res1, res2, ... res_n: The result to return for each matching value.default (optional): The value to return if no match is found.Returns the result corresponding to the first matching value. If no match is found, returns the default value (if specified).
SWITCH(Sub-Category, "Bookcases", 4.55, "Chairs", 2.67, "Tables", 2.81, "Art", 1.35, "Paper", 3.79, "Machines", 10.55, "Envelopes", 1.23, "Storage", 2.53, 0)Evaluates the Sub-Category value and returns the corresponding numeric value for each match; if no match is found, returns 0.
