1. Calculation functions
  2. Functions
  3. Edit formulas

A formula is an expression that calculates the value of a cell. You can write and edit formulas in the formula editor or the formula bar, or type directly into the Formula cell.

Note: If you want to edit formulas in the new modeling experience, go to Home > Models and select Use the new model experience.

When you edit a formula, you can: 

  • Switch between modules or other model settings.
  • Toggle in and out of Blueprint  as you edit.
  • Complete a formula.
  • Exit edit mode easily.

Enter and edit formulas

When you write formulas, ensure that you follow these requirements:

  • Close open parentheses.
  • Enter all required arguments for any functions that you use.
  • Do not nest more than 100 functions in a single formula.
  • Enclose line item names that contain hyphens, numbers, or operators (+ - / *) with single quotation marks.

Note: When you reference line items in a formula, you do not need to use the correct case for line item names. For example, you can refer to the Units line item as units in a formula.

To enter and edit a formula:

  1. Select the line item you want to apply a formula to, then double-click in the formula bar above the grid. When the formula bar is activated, the Cancel  and OK  icons appear to the right of the formula editing bar.
  2. Enter the formula:
    • Type functions and operators directly into the formula bar.
    • To add references to line items in the same module, select the line item. The line item reference is automatically added to the formula.
  3. Select OK , or press Enter to add the formula.

If you need more space for viewing or editing a formula, select the chevron to the left of the formula bar to open the formula editor. The formula editor includes buttons for common operators. Learn more in Calculation functions.

If you want to view different areas in your model as you edit a formula, open another model element to undock the formula editor. Use the header bar in the formula editor to drag it around the screen. Select the chevron to dock the formula editor.

Which formula level applies to a line item?

When you select a line item, you can read the formula and determine which level of formula is applied from the formula bar:

  • If an individual line item formula applies, the name of the line item is shown.
  • If an individual line item formula applies and you've selected the Formula Scope to be any option other than All Versions, then the version it applies to is also shown. 
  • If a module scope version formula applies, the version and line item subset the formula applies to is shown.
  • If a model scope version formula applies, the version the formula applies to is shown.

Learn more in Set up formulas at different levels.

Formula precedence

The order of precedence when you try to enter formula at all three levels that apply to the same line item is as follows:

  • If you enter a formula for an individual line item but there's no module scope version formula, then a version formula for the entire model takes precedence.
  • If you enter an individual line item formula and you then try to enter a module scope version formula that also applies to the line item, then you'll be blocked.
  • If a module scope version formula applies to a line item and you try to enter an individual line item formula also for the line item, then you'll be blocked.
  • If module scope version formula already exists and you try to enter a version formula for the entire model that clashes with an existing module scope version formula, then you'll be blocked.
  • If version formulas for the entire model already exist and you try to enter a module scope version formula that clashes with a version formula for the entire model, then you'll be blocked.

Learn more in Add version formulas to a model and Add version formulas to line item subsets.

Warning: If a line item is an Is Summary line item for other line items and uses a simple sum formula, this formula takes precedence and can co-exist with a module scope version formula that also applies to the Is Summary line item.

Using calculation functions in a formula

A rich set of calculation functions is provided to match the modeling flexibility of Excel. Calculation syntax is consistent with Excel for familiar functions, such as conditional calculations.

Calculation functions include basic operators (+ - * / ^), bracketing, financial functions (such as depreciation and loan payments), and time and date functions.

You can link line items across modules. A line item in one module can be linked to another module, or they can be related using conventional remote calculation references.

Mappings may be based on dimension item properties, for example Product Pack Size, or on line item data, for example an assignment of Employee to Cost Center, which might for example vary by time.

By default, calculations apply to all model versions: Current, Forecast, Actual, and any prior forecasts included for comparison, but differing calculations may be specified for the Actual version.