Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2023-06-23
Concept: Typing Basic Formulas in Sheet Cells

Concept: Typing Basic Formulas in Sheet Cells

You can enter formulas directly into the cells of sheets. We recommend that you always use Formula Assistant to avoid syntax error. Valid formulas have at least 1 operand. Operands pull data. The most basic formula calculation must have at least 1 operand and 1 operator. The most basic operand is a number. The most basic operator is a math symbol, such as plus or minus. Example: 1+1.
In most cases, you can add a formula to any editable, numeric cell. To type the formula in the cell, you must first click the cell. Then, you can:
  • Type the equation directly in the cell.
  • Type the equation into the formula bar at the top of the sheet.
  • Click Formula Assistant button from the toolbar to build an error-free formula. See Use the Formula Assistant .

Creating Basic Mathematical Equations

You can use basic mathematical formulas with the keys on your keyboard. The simplest operands of simple math formulas are numbers. The simplest operators are those that you can find on your keyboard. You can also build simple math into more complex formulas that reference accounts and modifiers. In Formula Assistant, the first set of buttons provide simple math operators that you can insert into the formula.
When you enter formulas into cells:
  • Always start with the equal sign.
  • Keep it simple for best results.
For simple math, it’s easier to enter the formula directly into the cell. However, when using Formula Assistant, you don’t need to enter the equal sign.
Math Operators
Example Formula
Addition
Formula: =760+500
Result: 1260
Subtraction
Formula: =760-500
Result: 140
Multiplication
Formula: =760*10
Result: 7,600
Division
Formula: =divf(760,10) Result: 76
Percentage
Formula =200*.05
(5 percent of 200)
Result: 10
Order of Operation
Formula: =divf(70,10)*12
Result: 84

Using the Capture Method

You can create equations that reference other cells on the sheet. To do this you click on the cells, the same way you do with Excel with some minor differences:
You can’t use the formula bar. Type directly into the cell. Always start the formula with an equal sign. You must type the operators and commas between cell captures. You can’t use capture method from Formula Assistant.
For best results, use the capture method to grab the values. Then use the formula bar to check the syntax and add complexity.
Use Case
Steps
Capture a single cell.
After the equal sign, click any cell on the sheet.
Capture cells and add math
  1. After the equal sign, click any cell on the sheet or type a number.
  2. Without adding space, type a simple math operator.
  3. Without adding space, click another cell or type a number.
Capture cells and add complexity
  1. After the equal sign, enter a math function, like divf .
  2. Type the opening parenthesis without adding space.
  3. Capture a cell or enter a number.
  4. Type a comma and add the next number or capture.
  5. Type the closing parenthesis.
  6. Check the formula bar to review what you have typed.
  7. Add more complexity from there if necessary.

Adding References to Formulas

A formula reference points to data. When you point to data, you pull the value into the formula. On the left side of Formula Assistant, you can find all the account hierarchies with the correct account codes to help you add references in the appropriate syntax.
In the documentation, we provide the syntax with variables inside chevrons. You must replace the chevrons and the text inside with the real elements of your specific model.
These are the most common references and their syntax:
Reference
Syntax
Example
Accounts (general ledger, custom, metric)
ACCT.<account_code>
ACCT.6113
Where 6113 is the account code.
We don't recommend that you reference a system account from cube sheet cells. See FAQ: What's the best way to reference a system account from a cube sheet?
Accounts (cube and modeled)
ACCT.<sheetcode.account_code>
ACCT.RevenueCube.003
Where RevenueCube is the sheet code and 003 is the account code.
Assumption accounts
ASSUM.<account_code>
ASSUM.123
Where 123 is the account code.
Constants
Any number
500

Adding Modifiers to Formulas

Modifiers filter the value that the formula pulls from the account or column. You can modify a reference by following it with square brackets. You then add the modifier inside the brackets. Example: ACCT.001[level=this]. Formula Assistant provides a series of drop-down prompts on the right side to help you add modifiers using the appropriate syntax.
Here are common modifiers:
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]

Creating Basic Iff Statements for Formulas

Iff statements have 2 possible outcomes. If an expression is true, the formula calculates 1 expression, but if it’s false, the formula calculates the other expression. The basic syntax for Iff statements is: Iff (EXPR, T, F). Formula Assistant provides these buttons in the toolbar that you can insert into your expressions:
Button
Example
Description
=
Iff (ACCT.001 = 100, 0, 1)
If the value of an expression equals the exact value of another expression.
The example calculates:
  • 0 if the value equals 1000.
  • 1 for all other values.
!=
Iff (ACCT.001 != 1000, 0, 1)
If the value of an expression doesn’t equal the value of another expression.
The formula calculates:
  • 1 if the value equals 1000.
  • 0 for all other values.
<
Iff (ACCT.001 < 1000, 0, 1)
If the value of an expression is less than the value of another expression.
The formula calculates:
  • 0 for all values less than 1000.
  • 1 for all values greater than or equal to 1000.
<=
Iff (ACCT.001 <= 1000, 0, 1)
If the value of an expression is less than or equal to the value of another expression.
The formula calculates:
  • 0 for all values of 1000 or less.
  • 1 for all values greater than 1000.
>
Iff (ACCT.001 > 1000, 0, 1)
If the value of an expression is greater than the value of another expression.
The formula calculates:
  • 0 for all values greater than 1000.
  • 1 for all values less than or equal to 1000.
>=
Iff (ACCT.001 >= 1000, 0, 1)
If the value of an expression is greater than or equal to the value of another expression.
The formula calculates:
  • 0 for all values of 1000 or greater.
  • 1 for all values less than 1000.
and
Iff (ACCT.001 = 1000 AND ACCT.002 = 2000, 0, 1)
If all expressions are true.
The formula calculates:
  • 0 if both ACCT.001 is 1000 and ACCT.002 is 2000.
  • 1 if ACCT.002 is 2000 and ACCT.001 is not 1000.
  • 1 if ACCT.001 is 1000, and ACCT.002 is not 2000.
or
Iff (ACCT.001 = 1000 OR ACCT.002 = 2000, 0, 1)
If at least 1 expression is true.
The formula calculates:
  • 0 if both ACCT.001 is 1000 and ACCT.002 are 2000.
  • 0 if ACCT.001 is 1000 and ACCT.002 is any value.
  • 0 if ACCT.002 is 2000 and ACCT.001 is any value.
  • 1 if neither account matches.
Not
Iff (NOT (Level = HQ, 0, 1)
Excludes the expression.
The formula calculates:
  • 0 if the level isn’t HQ.
  • 1 for all other levels.

Saving Formulas and Formula Errors

When you enter a formula, the cell displays (fx)?. After you save the sheet, the formula calculates and displays the value. The cell also displays a blue triangle in the corner to indicate that we calculated the value with a formula.
When you have syntax errors or unacceptable references in the formula, you won’t be able to save the sheet. When you have formula errors, you can save the sheet, but the cell displays an error.
To review the formula after you save the sheet, you can:
  • Click the cell and look in the formula bar.
  • Right-click the cell and select Explore Cell.
When cells have blue triangles and gray backgrounds, you can’t change the formula from the sheet. An administrator added these formulas to the account itself. For the particular version, the administrator has locked the calculations to avoid user edits to the value or formula.

Formula Examples

We've provided some common examples of formulas you can use for reference to see how formulas are typically constructed. In some cases, you can use these formulas as a starting point for your own models. Always make sure that when you copy a formula, you update the references to reflect real elements in your model.