Column
Calculated Column
Adds a column whose value is worked out per row from a formula.
When to use it
Use it whenever a value has to be derived from other columns, such as a line total, a label built from text fields, or a flag based on a threshold.
In a pipeline
File InputCalculated ColumnExport Excel
Settings
| Setting | Type | Required | Default or hint |
|---|---|---|---|
| Generate New Column | checkbox | — | true |
| New Column Name | text | — | Calculated |
| Formula | textarea | Yes | e.g. {Price} * {Quantity} |
| Insert After | select | — | End of table |
Example
Work out a line total
- New Column Name=Line Total
- Formula={Qty} * {Price}
- Insert After=Price
| Item | Qty | Price |
|---|---|---|
| Bolt | 4 | 2.5 |
| Nut | 10 | 0.4 |
| Item | Qty | Price | Line Total |
|---|---|---|---|
| Bolt | 4 | 2.5 | 10 |
| Nut | 10 | 0.4 | 4 |
Insert After places the new column straight after Price; leave it empty and the column is appended at the end.
Worth knowing
- Columns are referenced in braces, as in {Price}, and a value from a Variable node is referenced as $Name.
- Spreadsheet functions are available, including IF, ROUND, CONCAT, LEFT, UPPER, DATEDIF and FORMATDATE.
- A row whose formula fails gets an empty cell and the node reports how many rows failed; wrap risky maths in IFERROR({Total} / {Qty}, 0).
- Turn Generate New Column off to write the result back into an existing column instead of adding one.