SECTIONSCalculations in Plan Cells
The Formula Builder enables you to create formulas 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 a given criteria
- When you want to find the average, highest, or lowest value among cells that match a 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 image details the following:
- Labeled components of the Formula Builder, including buttons and icons
- Labeled components of a formula created in builder mode
| Element Number | Description |
|---|---|
| 1 | Switches the Formula Builder to text mode. For more information, see Text Mode. |
| 2 | Adds a new function to the formula. For more information, see Functions. |
| 3 | Adds a new numeric value to the formula. |
| 4 | Adds a plan reference. For more information, see Referencing Plans in Formula Builder. |
| 5 | The function used in the calculation. For more information, see Functions. |
| 6 | The dimensions that the formula is filtered by. |
| 7 | The dimension referenced by the applied formula filters. |
| 8 | Indicates whether to include or exclude the value in 9. |
| 9 | The value of the dimension referenced by the applied formula filters. For more information, see Filters. |
| 10 | The measure referenced by the applied formula filters. |
| 11 | When selected, the cells retrieved by the formula are included in the calculation. When deselected, these cells are omitted. |
| 12 | 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. In the example, the Measure filter is an absolute reference. If you copy the cell to the Worst Case value for the 2000 - Account Payable GLA, the filter references the Expected measure for the 2000 - Account Payable GLA. |
| 13 | Deletes the highlighted segment in the Formula Builder. |
| 14 | The mathematical operator applied between two segments. |
| 15 | A segment representing a numeric value. |
| 16 | Deletes the highlighted segment in the Formula Builder. |
| 17 | Discards changes. |
| 18 | Applies changes. |
| 19 | The selected cell. |
| 20 | Provides a direct link to this Help page. |
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 sum of the cell in which company is Finance, Inc., GLA is 1020 - Cash in bank, year is 2024, and period is 001, and measure is Expected is being multiplied by 1.5. The Absolute References and Include Calculations options have also been selected.
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.