Formulas
Reminder:
- Formula allows to implement business rules using pre-defined operators
- Quote header is made up of tabs which fields can be computed by means of a formula.
- Quote spreadsheet is made up of columns and total cells whose associated values can be computed by means of a formula.
- Update of spreadsheet columns can trigger Up propagations and/or Down propagations rules which can be computed by means of a formula.information: Formula are directly input in the corresponding property pop-up.
Understanding the syntax:
Getting contextual settings
The table below details the variables automatically initialized before the execution of the formula:
Note: In formulas, constant values must be enclosed in matching single quotes ('). E.g. ‘My Value’ (and not “My Value” )
Computation Operator Table:
The table below details the formula operators in accordance with the different uses cases (header fields, spreadsheet columns, spreadsheet total cells):
Notice:
- Operators with condition:
- Fields & Total cells: if the condition is satisfied, its result becomes the value of the cell, otherwise, the cell takes the value 'elsevalue'
- Columns: only the cell lines fulfilling the condition are used in the calculation
- Formula used for columns: can only use column values of the current line (colName[aliasLine])
- "FCT" stands for "Field, Column or Total cell". One of these three indicators precedes every syntax item.
| OPERATOR | EXPLANATION | SYNTAX (FCT) |
|---|---|---|
| +, -, *, / | Regular Arithmetic Operator | F: aliasName1 + aliasName2 (same with -, /, *) |
| C: colName1[aliasLine] + colName2[aliasLine] (same with -, /, *) | ||
| T: totCellName1 + totCellName2 (same with -, /, *) | ||
| SUM | Sum of 2 operands | F: SUM(aliasName1, aliasName2) --> (eqv, to a + b) |
| C: SUM(colName1[aliasLine], colName2[aliasLine]) --> (eqv, to a + b) | ||
| T: SUM(totCellName1, totCellName2) --> (eqv, to a + b) | ||
| Sum of a column of values | T: SUM("colName1") | |
| SUM_IF | Sum of 2 operands with condition | F: SUM_IF(aliasName1, aliasName2, condition, elsevalue) |
| C: SUM_IF(colName1[aliasLine], colName2[aliasLine], condition, elsevalue) | ||
| T: SUM_IF(totCellName1, totCellName2, condition, elsevalue) | ||
| Sum of a column of values with condition | T: SUM_IF("colName1", "condition") | |
| SUM_PROD | Product sum of 2 columns of values | T: SUM_PROD("colName1", "colName2") |
| SUM_PROD_IF | Product sum of 2 columns of values with condition | T: SUM_PROD_IF("colName1", "colName2", "condition") |
| AVERAGE | Average of 2 operands | F: AVERAGE(aliasName1,aliasName2) |
| C: AVERAGE(colName1[aliasLine], colName2[aliasLine]) | ||
| T: AVERAGE(totCellName1, totCellName2) | ||
| Average of a column with condition | T: AVERAGE("colName1") | |
| AVERAGE_IF | Average of two operands with condition | F: AVERAGE_IF(aliasName1, aliasName2, condition, elsevalue) |
| C: AVERAGE_IF(colName1[aliasLine], colName2[aliasLine], condition, elsevalue) | ||
| T: AVERAGE_IF(totCellName1, totCellName2, condition, elsevalue) | ||
| Average of a column with condition | T: AVERAGE_IF("colName1", "condition") | |
| MIN | Minimum of 2 operands | F: MIN(aliasName1, aliasName2) |
| C: MIN(colName1[aliasLine], colName2[aliasLine]) | ||
| T: MIN(totCellName1, totCellName2) | ||
| Minimum of a column of values | T: MIN("colName1") | |
| MIN_IF | Minimum of 2 operands with condition | F: MIN_IF(aliasName1, aliasName2, condition, elsevalue) |
| C: MIN_IF(colName1[aliasLine], colName2[aliasLine], condition, elsevalue) | ||
| T: MIN_IF(totCellName1, totCellName2, condition, elsevalue) | ||
| Minimum of a column of values with condition | T: MIN_IF("colName1") | |
| MAX | Maximum of 2 operands | F: MAX(aliasName1, aliasName2) |
| C: MAX(colName1[aliasLine], colName2[aliasLine]) | ||
| T: MAX(totCellName1, totCellName2) | ||
| Maximum of a column of values | T: MAX("colName1") | |
| MAX_IF | Maximum of 2 operands with condition | F: MAX_IF(aliasName1, aliasName2, condition, elsevalue) |
| C: MAX_IF(colName1[aliasLine], colName2[aliasLine], condition, elsevalue) | ||
| T: MAX_IF(totCellName1, totCellName2, condition, elsevalue) | ||
| Maximum of a column of values with condition | T: MAX_IF("colName1", "condition") | |
| VALUE_IF | Value (of the operand) with condition | F: VALUE_IF (aliasName1, condition, elsevalue) |
| C: VALUE_IF(colName1[aliasLine], condition, elsevalue) | ||
| T: VALUE_IF(totCellName1, condition, elsevalue) |
Up Propagation Operator Table:
Table below details the operators available in formula of type UpPropagation:
Down Propagation Operator Table:
Table below details the operators available in formula of type DownPropagation: