Create Formulas

Enter a Formula

You can enter a formula by typing directly in the cell, or using the formula bar. Select the green check to apply the formula and the red x to cancel:

After you enter a formula, an f(x)?
symbol appears in the cell. This means that the formula hasn't been calculated, yet. Save the sheet and the cell displays the evaluated result of the formula with a blue triangle in the corner to indicate that the value is formula-driven. See Reference: Colors and Notations in Sheets.

Using the Capture Method to Reference Other Cells

A formula typed into a cell can reference other cells on the sheet. Start by typing a equal sign (

=

) into a cell. The cell formula contains an equal sign and nothing else. Now, select another cell in the sheet to include it in the formula. The cell now contains the code of the referenced account, as well as any other pertinent information about the selected cell.

The formula can be further modified by typing in simple operators symbols such as plus (

+

), minus (

-

), or multiply (

*

). When the formula is complete, select the green check from the formula bar.

The Capture method only works when you are entering the information directly in a cell. It does not work if you edit or update the formula in the formula bar.

Formula Errors

If a formula contains improper syntax (for example, a circular reference) the cell appears outlined in red with an exclamation mark when the formula is saved. Hovering over the cell displays a tooltip to explain the cause of the error. For information on formula errors, refer to Troubleshooting Formulas.

Use Formula Assistant

Anywhere a formula can be written, the Formula Assistant is available. The Formula Assistant helps users construct syntactically-correct formulas. You can use it to insert valid accounts, assumptions, qualifiers, and functions into a formula.

To open the formula assistant:

Select a cell to contain a formula, then select the Formula Assistant
button from the toolbar.

Select the Formula Assistant
link in Account Details, etc.

Explore a Formula in a Cell

A small triangle in the lower right corner of the cell indicates that the cell value was generated by a formula.

To view the formula associated with a cell:

Select the cell that contains the formula.

Right-click the cell and select Explore Cell.

Recommended Development Process

Plan what areas of your model will be impacted by the formula you're creating.

You can create simple formulas that are used locally on a sheet or in a report. Or, you can create shared formulas and account formulas that are used globally across your model. Formulas can be used in:

Accounts metric, modeled, cube

Standard sheet data entry

Cube data entry

Formulas page (for level-based/shared formulas)

Report calculations

Allocation Rules

For more information, refer to Locations for Creating Formulas and Shared Formulas.

Get familiar with formula functions and syntax.

You can use a broad range of formula functions (mathematical, logical, date, and string) and account references as building blocks. Refer to Reference: Formula Syntax for Account References and Modifiers and Reference: Formula Functions.

Don't forget that a formula will often include both a calculation and some conditions that must be met to apply the calculation. For example, a pay raise may be defined as a simple 10% pay increase, but you may also need to include other conditions in the formula. For example: Is the person a full-time employee?; Have they reached their anniversary date?; and so on.

Get familiar with 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. Refer to Analyzing a Pay Rate Formula - A Detailed Walkthrough.

Pick your formula tools. You can:

Create simple formulas by typing directly into a cell or formula bar on a sheet. Or, by typing into a Formula
text field.

Use the Formula Assistant. The Formula Assistant can help you construct formulas by guiding you in the use of formula syntax including terms, modifiers, and functions.

Define shared formulas by navigating to Formulas.

Import or paste a formula from an outside source (Excel, Word)

Use the Formula Assistant to prevent syntax and naming errors. See Using the Formula Assistant.

Start debugging your formula and correct errors.

As a formula developer, you may be inclined to write the complete formula and then begin debugging and checking for errors. This approach can save time for smaller, simpler formulas. However, more complicated formulas can include many variables, functions, and dependencies that need more attention. Therefore, it's a good idea to test and debug each function separately to make sure it's working as expected before combining the functions into a single formula. This saves time in the long run and helps you to quickly pinpoint the cause of issues if they occur.

Consider using a sandbox instance (if you have one) or a test account to try out your new formulas before rolling out the formula to a wider audience. Alternatively, have a rollback strategy (or backup) so you can easily restore an earlier version if things aren't working as expected.

Also, add comments liberally through your formulas. Adding comments and spacing (tabs, spaces, and new lines) can make formulas much more readable and maintainable in the future, both for other users who encounter your formulas as well as for yourself in the future.

Validate your formulas and troubleshoot any issues.

You can run Formula Validation to automatically check the current working version and list any formula errors, then use Explore Cell to drill-down to the cause of the error. For more information, refer to Validating Formulas.