SECTIONSCalculations in Plan Cells
The Formula Builder enables you to create calculations in plan cells. You can use formulas to perform simple or complex calculations, referencing cells within a plan or cells within another plan.
This might be useful in the following cases:
- When you want to sum the values of two cells
- When you want to increase or decrease the value of another cell
- When you want to count the number of cells that contain values and match given criteria
- When you want to find the average, highest, or lowest value among cells that match given criteria
You can use the Formula Builder in text mode and builder mode. For more information, see Text Mode and Builder Mode.
The Formula Builder consists of the following elements.
| Element Number | Description |
|---|---|
| 1 | Switches the Formula Builder to text mode. For more information, see Text Mode. |
| 2 | Opens an AI assistant to help you build the formula using generative AI. For more information, see Formula Builder AI Assistant Overview. |
| 3 | Adds a new function to the formula. For more information, see Functions. |
| 4 | Adds a new numeric value to the formula. |
| 5 | Adds a plan reference. For more information, see Referencing Plans in Formula Builder. |
| 6 | Deletes the highlighted segment in the Formula Builder. |
| 7 |
Provides a direct link to this Help page. |
| 8 | The function used in the calculation. For more information, see Functions. |
| 9 | The dimensions that the formula is filtered by. |
| 10 | The dimension referenced by the applied formula filters. |
| 11 | Indicates whether to include or exclude the value in 12. |
| 12 | The value of the dimension referenced by the applied formula filters. For more information, see Filters. |
| 13 | The measure referenced by the applied formula filters. |
| 14 | When selected, the cells retrieved by the formula are included in the calculation. When deselected, these cells are omitted. |
| 15 | Indicates whether the filters used for each of the dimensions and the measure are absolute references. As you copy the cell to another cell, absolute references remain unchanged. For more information, see Copying Cells. |
| 16 | Deletes the highlighted segment in the Formula Builder. |
| 17 | The mathematical operator applied between two segments. |
| 18 | A segment representing a numeric value. |
| 19 | Discards changes to the formula. |
| 20 | Applies changes to the formula. |
| 21 | The selected cell. |
Filters
You can filter by dimensions to restrict and reference a particular cell. In addition, you can enable the use of absolute references. Absolute references maintain the formula structure that you've created, regardless of the cell location.
Text Mode
Text mode enables you to create a formula by manually entering text. This is useful if you have experience creating formulas and want to build them from scratch.
This is an example of a formula created in text mode:
In this example, the value of the cell in which trial balance 1 is Assets, trial balance 2 is Current Assets and the GLA is 1020 - Cash in Bank, period is 001, balance type is Budget, and measure is Home is being multiplied by 1.25.
Builder Mode
Builder mode enables you to create a formula using segments. We recommend you use builder mode if you have less experience creating formulas from scratch. For an example of a formula created in builder mode, see Formula Builder image.
AI Assistant
The Formula Builder AI Assistant is designed to streamline the creation of complex plan calculations. If the AI Assistant is enabled, it appears on the Formula Builder. For more information, see Formula Builder AI Assistant Overview and Generating Plan Calculations Using AI Assistant.