It's best to add formulas using the formula editor, particularly for complex formulas. This is because the editor has syntax highlighting which makes it easier to read the code. You can also switch between modules while writing formulas.
Always format the line item before entering a formula in it. The results of the formula must match the format of the cell. For example, if the formula results in a date, the line item must have a date format. The formula is rejected if the formats do not match.
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.
To find information on functions, operators, and how to write formulas, see All functions.
To add a formula to a line item using the formula editor:
- In Blueprint view, select the line item. In the formula bar above the line items, the line item name appears next to the editing field.
- In the formula bar, select the line item name or
Expand to the left of the line item name. The formula editor opens in a separate pane at the bottom of the window.
Note: you can also open the formula editor while you're editing in the formula bar by switching to another module. The formula editor opens at the bottom of the window.
- Enter the formula in the editor:
- Type functions, operators, and references directly into the editor.
- To add references to line items in the same module, select the line item. The line item is automatically added to the formula editor.
- To add references to line items in different modules, open the modules that you want to refer to and select the line items. The formula editor stays fixed at the bottom of the window when you switch between modules.
- Click Apply when the formula is complete. The formula is added to the Formula cell. If you do not want to apply the formula, select
- Optionally, specify which versions of the model the formula applies to in the Formula Scope column in Blueprint. The Formula Scope option defaults to All Versions.