Concept: Formula Building Blocks

Terms to Describe Formulas

A formula has:

Operands: The elements to be calculated. You separate operands with operators. Operands at Adaptive Planning include:

References: Accounts with modifiers, assumptions, rows (columns from modeled sheets).

Constants: Numbers or text values that are entered directly into a formula.

Modifiers: Additional instruction that specifies the reference to specific time, level, dimension value, or attribute value.

Operators: Specify the type of calculation to perform. You can use:

Symbols. Example: The asterisk (*) operator multiplies numbers. You can find most symbols in Formula Assistant or you can type them with your keyboard.

Functions: Templates with precise syntax that combine operands and operators. You must replace the variables with your own references, modifiers, and expressions. Example: For the rounding function, Round (N), replace the N with a complex calculation and the function rounds it to the nearest whole number.

In addition, these terms describe parts of a formula:

Expression: Describes a specific part of a formula. Example: In this logical formula Iff ((ACCT.001 > ACCT.002, 1, 0), "ACCT > ACCT.002" is an expression.

Term: The reference, combined with any modifiers. Example: ACCT.001 [time = this - 1] is a term.

Syntax: The way that you must reference data in your model to create a valid formula. Example: To reference an account, the syntax is ACCT.<account_code>.

Dot Notation: An advanced set of syntax rules that point to elements of your model and allow for additional functionality. The primary use case for dot notation is for Iff statements. Example: You can add this.Month.NumberOfDays < 30 to an Iff statement to apply a different calculation for the month of February.

Understanding Syntax in the Documentation

A few notes on syntax in documentation:

We use capital letters in parenthesis, like (N) or text inside chevrons, like <account_code> to indicate variables. You must replace the variables with valid constants, account references, or formula expressions.

We use commas to separate terms, according to the standard of many countries. If your browser uses commas as decimal points, you must separate the terms with semicolons.

We add spaces to make the formulas readable, although they're not necessary. You can also add spaces or leave them out of your formulas.

We capitalize functions, such as Divf, and use all caps for elements, like ACCT, but formulas aren't case sensitive.

Example: The syntax for adding a level modifier to an account is ACCT.<code> [level = <level_code>]. You must:

Use ACCT. to reference an account.

Replace <code> with an actual account code. Make sure you remove the chevrons too.

Use square brackets to define modifiers.

Use level = to modify by level.

Replace <level_name> with the actual code of a level and remove the chevrons.

Operands: References and Constants

References are a type of operand. In Adaptive Planning, the reference pulls a value from somewhere else in the model into the formula. Example: You can reference an account. The account reference pulls in the value for that account for a specific version, level, and time. You can add these basic references to formulas:

Reference	Syntax	Example
General ledger, custom, or metric accounts.	ACCT.<AccountCode>	ACCT.6113 Where 6113 is the account code.
Cube and modeled accounts.	ACCT.<SheetCode.AccountCode>	ACCT.RevenueCube.003 Where RevenueCube is the sheet code and 003 is the account code.
Assumption accounts	ASSUM.<AccountCode>	ASSUM.123 Where 123 is the account code.
Columns on a modeled sheet Only available for modeled calculated accounts.	ROW.<column_code>	ROW.StartDate Where StartDate is the column code for the Start Date field on the modeled sheet.
Spread and value lookups in modeled sheets Only available for modeled calculated accounts.	ROW.<lookup_name>	Row.BenefitLookup Where BenefitLookup is the name of the value lookup table.
Constants	Any number	500

You can also refer to cells on a sheet using the capture method, which populates the formula with the account and any present modifiers, such as time and levels. See Concept: Typing Basic Formulas in Sheet Cells.

Modifiers

You can add precision with modifiers when you reference an account or row. Modifiers find a specific slice of the account’s data. Example: You want the value of an account for a specific level or time period.

You add modifiers:

Next to the account reference without spaces.

Inside square brackets.

Separated with commas or semicolons if you use commas for decimal points.

Using the exact codes and names that exist in your model.

Modifier	Syntax	Example
Time	[time = <strata_code>]	ACCT.001 [time = 2023] Where 2023 is the year rollup strata. Pulls the 2023 rollup value of the account.
Levels	[level = <level_code>]	ACCT.001 [level = sales] Where sales is the level code. Pulls the value of the account at the Sales level only.
Custom Dimension	[<dimension_code> = <dimension_value_code>]	ACCT.001 [product = shirts] Where product is the dimension code and shirts is the dimension value code. Pulls the account data tagged with shirts.
Attributes	[<attribute_code> =<attribute_value_code>]	ACCT.001 [productgroup = tops] Where productgroup is the attribute of the Product dimension and tops is the attribute value. Pulls the account data tagged with any dimension values that corresponds to the tops attribute value.
Multiple Modifiers	[<modifier1> = <code>, <modifier2> = <code>, <modifier3> = <code>]	ACCT.001 [time = 2023, level = Sales, product = Shirts] Pulls the time rollup of 2023 for the Sales level for Shirts.

You can have multiple modifiers per reference, but you can only have 1 value per modifier. So, when you want to pull the value for more than 1 level:

Wrong: ACCT.001 [level = Sales, Marketing]

Wrong: ACCT.001 [level = Sales, level = Marketing]

Correct: ACCT.001 [level = Sales] + ACCT.001 [level = Marketing]

Adding a modifier might be all you need to do for an entire formula.

Operators: Common Math Symbols

Operators are math symbols that provide calculations. You can use basic mathematical formulas with the keys on your keyboard. In Formula Assistant, the first set of buttons in the toolbar provide simple math operators that you can insert into the formula in between terms.

The simplest operands of math formulas are numbers. The simplest operators are those that you can find on your keyboard. You don't need spaces between operands and operators, but you can add them to make the formula easier to read. You can also build simple math into more complex formulas that reference accounts and modifiers.

Here are some examples of simple math formulas that you can use:

Math Operator	Example Formula
Addition (+)	ACCT.001 + ACCT.002
Subtraction (-)	ACCT.001 - ACCT.002
Multiplication (*)	ACCT.001 * ACCT.002
Division (Divf)	Divf (ACCT.001, ACCT.002) Divides ACCT.001 (the dividend) by ACCT.002 (the divisor)
Modulo (%)	10 % 3 Calculates the remainder of the quotient. Returns 1 because 10 divided 3 = 3 remainder 1.
Parenthesis ()	(ACCT.001 + ACCT.002) * 12 Dictates the order of operation: Add the accounts before multiplying by 12.
Round(N)	Round (ACCT.001) Rounds to the nearest whole number. In the example if the value of ACCT.001 is 5.75, the formula returns 6.

Formula Functions

A formula function is a template that uses different operators and variables. When you use a function, you replace the variables with references, constants, or full formula expressions. Functions rarely work alone; instead, you string functions together to build your formula.

Example: For the Expense account you want to use the value of last year and apply an inflation rate that you typically store in an assumption account. To avoid referencing blank cells, you can create an IF statement using a logical statement. The template looks like this: Iff (EXPR, T, F).

Here’s how you can use the function to build formula:

Replace the Variable	Example	Description
Replace EXPR with the IsBlank logical function creating a function within a function.	Iff (isBlank (N) , T, F)	The either-or constant on which the True and False calculations rely.
Replace N with the Inflation Rate assumption, which has the account code: infl.	Iff (isBlank (ASSUM.Infl ), T, F)	If the Inflation Rate assumption is blank...
Replace T with a calculation that triggers if EXPR is true.	Iff (isBlank (ASSUM.Infl), *ACCT.this [time = this - 12] (1.05)** , F)	If the Inflation Rate assumption is blank, take the value of this account from 12 months ago and multiply by 1.05.
Replace F with a calculation that triggers if EXPR is false.	Iff (isBlank (ASSUM.Infl), ACCT.this [time = this - 12] * (1.05), *ACCT.this [time = this - 12] ASSUM.Infl** )	If the Inflation Rate assumption isn’t blank, take the value of this account from 12 months ago and multiply it by the Inflation Rate assumption.

In Formula Assistant, we categorize formula function into these groups:

Mathematical

Logical

Date

String

You can select a category and then prefill the formula field with the available templates. See Reference: Formula Functions.

Placement in the Grid

A formula exists in the grid of Adaptive Planning. A typical spreadsheet has 2 axes: Accounts and time. Accounts are down the rows and time is across the column. Each cell represents a specific intersection between the account and the time period.

In Adaptive Planning, the grid is multidimensional, meaning there are more than 2 axes beyond the 2 dimensional sheet you see on the screen. Example: The data for Revenue exists at the intersection of the Sales level, the Budget 2025 version, the Revenue account in the general ledger hierarchy, the Product custom dimension value Shirts, the time period for January 2025 and so on. When you refer to a data point in a formula, you are referring to each of the axes, or dimensions. Unless you specify the dimension, certain defaults apply, based on the location of of the formula. These dimensions contribute to each intersection of data:

Dimension	Default in Reference
Time and Level	The same as the current location unless you modify your reference with time or level. Example: A formula for ACCT.001 is ACCT.002 * 10. For Jan 2024 the formula takes the value of ACCT.002 for Jan 2024. For Feb 2024, it takes the value from Feb 24, and so on, unless you specify something different for time in the formula. For levels, the same is true. Calculations pull the values at the same level that is currently being calculated.
Currency	The defined currency of the current level.
Account	The current account unless you reference another account.
Each custom dimension	The dimension value at the current location. If no dimension value exists at the current location, the default is the Uncategorized value.