CONFIGURE COLUMNS
Expression functions
Choose a function in With help, or write it in Advanced. Function names and signatures are the same in every language.
Use a function
Choose functions in the guided editor, or write them directly in Advanced.
- 1
Open Calculated columns
In Columns, choose Add column, then Calculated columns.
- 2
Choose a function
In With help, choose Use a function in a value selector. Search the catalog and fill its arguments with your columns or values.
- 3
Check the result
Review the first-row preview, enter a heading, and select Add calculation. Save the macro when finished.
Function reference
Aggregates use the complete dataset. Other functions operate on the selected value in the current row. ROUND defaults to zero decimal places; TODAY returns YYYY-MM-DD and MID positions start at 1.
| Function | What it does |
|---|---|
| Aggregate | |
| SUM(expr) | Sum across all rows |
| AVG(expr) | Average across all rows |
| MIN(expr) | Lowest value across all rows |
| MAX(expr) | Highest value across all rows |
| COUNT(expr) | Count non-empty values |
| COUNT_DISTINCT(expr) | Count distinct non-empty values |
| Number | |
| ROUND(n, decimals?) | Round to decimal places |
| ABS(n) | Absolute value |
| FLOOR(n) | Round down |
| CEIL(n) | Round up |
| SQRT(n) | Square root |
| NUM(v) | Convert formatted text to a number |
| Date | |
| TODAY() | Today’s date |
| DAY(date) | Day of the month (1–31) |
| MONTH(date) | Month (1–12) |
| YEAR(date) | Year |
| QUARTER(date) | Quarter (Q1–Q4) |
| DAYS_TO(date) | Days until a date; negative for past dates |
| Text | |
| LEFT(x, n) | First characters |
| RIGHT(x, n) | Last characters |
| MID(x, start, len) | Characters from a position (starting at 1) |
| BEFORE(x, str) | Text before a separator |
| AFTER(x, str) | Text after a separator |
Examples to adapt
Names in square brackets represent column headings. Choose your actual columns from the suggestions.
Line total
Multiply the quantity by the unit price.
=[Quantity] * [Unit Price]Discounted total
Reuse the calculated Total column. Discount is a fraction: 0.1 means 10%.
=[Total] * (1 - [Discount])Share of total
Returns a number from 0 to 100. A zero total returns 0. Aggregates use the complete dataset, not just the current page.
=SUM([Total]) = 0 ? 0 : ROUND([Total] / SUM([Total]) * 100, 1)Days until due
Use a date column. Negative values mean that the date is in the past.
=DAYS_TO([Due Date])