Formula Syntax

This page provides information about the different options that you can use when building a formula in Extended Planning and Analysis. For more information about formula syntax errors, see Plan Errors.

Operators

Operators are used to perform simple mathematical operations between two segments. Here is a description of the operators available in formulas.

Operators Available in Formulas
Operator Description
+ Adds the second segment to the first segment.
- Subtracts the second segment from the first segment.
* Multiplies the first segment by the second segment.
/ Divides the first segment by the second segment.
^ Raises the first segment to the power of the second segment.
( ) Groups mathematical operations together to establish precedence.

Functions

Functions enable you to aggregate the values of cells that match the specified criteria. The cells can belong to the current plan or another plan.

In Extended Planning and Analysis, formulas have the following syntax:

FORMULA(["Plan or Scenario Name"], [true], [filter])

Example:

SUM(true, "Company" = "Finance, Inc.", "GLA" = "1020 - Cash in bank", "Year" = "2024", "Period" = "001", "Measure" = $"Expected") * 1.5

Here is a description of the functions available in formulas.

Functions Available in Formulas
Function Text Mode Syntax Description
Average AVG Returns the average of cells that match the specified filter criteria.
Count COUNT Returns the total number of cells that match the specified filter criteria and contain a value.
Maximum MAX Returns the highest value among the cells that match the specified filter criteria.
Minimum MIN Returns the lowest value among the cells that match the specified filter criteria.
Sum SUM Returns the total of cells that match the specified filter criteria. If only a single cell matches the filter criteria, the value of the cell is returned.

Here is a description of the function arguments.

Function Arguments in Formulas
Argument Position Required Description
1 No The name of the plan that the function references. You can use this to retrieve values from another plan.
2 No The name of the scenario that the function references. If you omit this and the plan name is provided, it references the default scenario.
3 No Only accepts true and false. Determines whether to include any cells retrieved by the filter that are themselves are formulas. When true, the formula cells are included, when false they are excluded. When the argument is omitted, it defaults to false.
4 No The filter criteria used to retrieve cells. If you omit this, all the cells in the plan are retrieved. For more information, see Filters.