Concept: Typing Basic Formulas in Sheet Cells

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:

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

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	After the equal sign, click any cell on the sheet or type a number. Without adding space, type a simple math operator. Without adding space, click another cell or type a number.
Capture cells and add complexity	After the equal sign, enter a math function, like divf . Type the opening parenthesis without adding space. Capture a cell or enter a number. Type a comma and add the next number or capture. Type the closing parenthesis. Check the formula bar to review what you have typed. Add more complexity from there if necessary.

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:

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:

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]

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:

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:

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.

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.