Math Function formula syntax, functions and messages
In Formula mode, a Math Function formula combines Number columns, numbers, operators, brackets, built-in functions and Number parameters. Mammoth checks it as you type and shows "Looks good" or the problem. For the steps to add the column, see Calculate with formulas.
Formula syntax and built-in functions
| Part | How to write it |
|---|---|
| Column | The column name in double quotes, such as "unit_price". Only Number columns are allowed. |
| Number | A plain number, such as 12 or 0.5 |
| Operators | +, -, * and /. Multiplication and division run before addition and subtraction. |
| Brackets | ( and ) to group parts of the formula |
| Parameter | A Number parameter in curly braces, such as {Max_Units} |
A formula can start with - or + but not with * or /. An empty cell in a column counts as 0. Dividing by 0 leaves the result cell empty.
| Function | Takes | Returns |
|---|---|---|
SUM("Column") | One Number column | The column total |
AVG("Column") | One Number column | The column average |
MIN("Column") | One Number column | The smallest value in the column |
MAX("Column") | One Number column | The largest value in the column |
STDDEV("Column") | One Number column | The column's standard deviation |
COUNT() | Nothing | The number of rows |
INT(expression) | Any formula | The whole-number part. It drops the decimals rather than rounding, so INT(7.9) is 7 and INT(-7.9) is -7. |
ABS(expression) | Any formula | The value without its sign, so ABS(-4) is 4 |
SUM, AVG, MIN, MAX, STDDEV and COUNT look at the whole column, not the current row, so every row gets the same value. If the step has a condition built in Builder, they cover only the matching rows. INT and ABS take a full formula, so they can wrap other functions: INT(SUM("units") / COUNT()).
Use a parameter in a formula
Click Insert Parameter above the formula, or pick the parameter from the autocomplete list. Only Number parameters can be used in a formula. Each run uses the parameter's current value. When the value changes, the pipeline is marked stale until you rerun it. See Parameters and snippets.
Warnings about IDs and money in a formula
In Formula mode, Mammoth adds a note when a formula does arithmetic on a column marked as an ID ("IDs are labels, not amounts, so doing arithmetic on them isn't meaningful.") or mixes an amount of money with a percentage ("This formula mixes an amount of money with a percentage. Check that the result is what you mean."). The notes never block the step. See Column types, IDs, currency and percentages.
Troubleshooting formula messages
| Message | What to fix |
|---|---|
| "Empty expression" | Type a formula. |
| "Expression can not begin with * or /" | Start with a column, number, function, bracket or minus sign. |
| "Expression can not end with any of +, -, *, /" | Finish the formula after the last operator. |
| "Operators +, -, *, / can not be followed by other operators" | Remove the doubled operator. |
| "Expected an operator +, -, *, / before ..." | Add an operator between two values. |
| "Syntax error: unmatched brackets" | Every ( needs a matching ). |
| "Only numeric columns allowed" | Use a Number column, or convert the column first with Convert Column Type. |
| "Column ... is missing" | The column was renamed or removed earlier in the pipeline. |
| "Function ... takes no arguments" | Write COUNT() with nothing inside. |
| "Function ... takes exactly 1 argument" | Give SUM, AVG, MIN, MAX or STDDEV one column. |
| "Invalid arguments for ..." | The function needs a column in double quotes. |
| "Invalid: ..." | The text isn't a column, number, operator, function or parameter. Check the spelling and quotes. |
| "Parameter ... not found" | No parameter has that name. |
| "Only numeric parameters allowed, ... is not one" | Use a Number parameter. |
In the formula's column list, a column can be marked "(column missing)" or "(not a numeric column)".
Other problems:
| Symptom | Cause | Fix |
|---|---|---|
| Every row shows the same value | SUM, AVG, MIN, MAX, STDDEV or COUNT looks at the whole column | Combine it with a column, for example "revenue" / SUM("revenue") |
| Result cells are empty | Division by 0, or rows outside the step's condition in a new column | Check divisors and the condition |
Related
- Calculate with formulas: create the column in SQL or Formula mode.
- Convert text to dates and numbers: make a column numeric first.
- Column types, IDs, currency and percentages: why IDs and money trigger notes.
Something wrong? support@mammoth.io