¶ 1. Overview
The Cell Value Breakdown panel allows you to configure values composed of multiple entries, with arithmetic operations and hierarchical logic. This feature turns the cell into a calculated cell, with specific editing and display rules.
¶ 1.1. Prerequisites
To use the Cell Value Breakdown feature, you must:
- Have permission to edit data in forms;
- Be working with an editable cell in a form;
- Have access to the tool via the cell's context menu.
For more information on required permissions, see: Features and Permissions.
¶ 1.2. Cell Value Breakdown
To access the feature:
- Select the desired cell and click Cell Value Breakdown on the ribbon;
- A side panel will open with:
-
The context of the selected cell;
-
A table with the following fields:
- Description: Free text field with an explanation of the value;
- Operation: Operator applied (+, -, *, / or ~);
- Value: Number associated with the row.
Initially, no data will be displayed. The fields must be filled in manually:
At the top of the panel is the Total field, which shows the calculated value of the cell based on the defined items.
After saving, the cell becomes calculated and can no longer be edited via the form.
¶ 2. Features
Through the Cell Value Breakdown panel, you can perform various actions:
-
History
Displays the cell's change history, showing what was modified, by whom, and when.
-
Remove
Deletes the selected row or item from the breakdown.
-
Copy
Copies the selected item, including its children.
-
Paste
Pastes previously copied content to another point in the breakdown.
-
Move Left
Decreases the row's indentation (moving up one hierarchical level).
-
Move Up
Moves the item one position up on the same level.
-
Move Down
Moves the item one position down on the same level.
-
Move Right
Increases the indentation, making the item a child of the one above.
¶ 3. Usage
¶ 3.1. Operators and Calculation Rules
¶ How It Works
- The user can input values and reopen the panel for adjustments;
- For each edit, the Apply button must be clicked again;
- The breakdown is saved automatically when saving the worksheet.
The Refresh button restores the cell’s original data. When used, all unsaved changes will be lost.
After applying the breakdown, the cell is locked for direct editing;
Once the worksheet is saved, the cell remains uneditable.
¶ Available Operators
+(Addition): Adds the value to the subtotal or total;-(Subtraction): Subtracts the value from the subtotal or total;*(Multiplication): Multiplies the current subtotal;/(Division): Divides the current subtotal;~(Reference): Informative only, does not affect the calculation.
¶ Application Rules
- The root-level operator determines how children are aggregated;
- Operators on child rows affect the value of the immediate upper level;
*and/require children to use only+or-;~does not affect the result and serves as a visual reference.
Root value with children
- It is not possible to define a direct value on the root if it has children.
¶ 4. Breakdown Example
Below is the description of the panel displayed above, along with the breakdown calculation:
| Level | Item | Operator | Value | Note |
|---|---|---|---|---|
| Root | Revenue | + | 1000 | Sum of children |
| Child | Product A | + | 600 | |
| Child | Product B | + | 400 | |
| Root | Expenses | + | -300 | Negative sum of children |
| Child | Fixed | - | 200 | |
| Child | Variable | - | 100 | |
| Root | Adjustment | * | 2 | Multiplies subtotal (applied after sum) |
| Child | Extra | + | 2 | |
| Root | Allocation | / | 1 | Divides final subtotal |
| Child | Base | + | 1 | |
| Root | Note | ~ | 3000 | Reference value, no impact on total |
Total: 1400
¶ 4.1. Example Calculation
Revenue:
Product A (600) + Product B (400) = 1000
Expenses:
Fixed (200) + Variable (100) = 300
→ Subtotal: 1000 - 300 = 700
Adjustment:
Subtotal (700) × 2 = 1400
Allocation:
1400 ÷ 1 = 1400
Note:
~3000 → Informative only, not included in calculation
Final Total: 1400