Formulas
A formula column computes its values from other columns in the same table, like a spreadsheet formula written once for the whole column. When the columns it reads change, its values follow.
Create a formula column
- Add a new, empty column to the table, for example with Add column right.
- Select one of its cells and type
=. The cell editor turns into the ƒ Formula editor. You can also pick ƒ Formula at the top of the column type list. - Type the formula. As you type a column's name or a function, suggestions appear; pick one with Tab or Enter. A function arrives with placeholders for its arguments, and Tab moves to the next one.
- Press ⌘ Enter (Ctrl Enter on Windows and Linux) to save, or Esc to cancel.
While you type, the editor checks the formula and shows the type its result will have, or underlines what's wrong.
Every cell of the column is computed from the formula; you can't type over one. To see a formula later, click the column's header: a card shows it, with an option to edit it if you can edit the table. If a computation fails, the card says so and the affected cells show #ERROR.
Write a formula
A formula is one expression, written on one line.
| Part | Looks like |
|---|---|
| A column | Its name, picked from the suggestions. It shows as a chip, and keeps working if the column is renamed |
| A number | 42, 0.2 |
| Text | "EU" (double quotes) |
| True, false, empty | TRUE, FALSE, NULL |
| A function | ROUND(Price * 1.2, 2) |
| Grouping | (Price - Discount) * Quantity |
Operators
| Operator | Does |
|---|---|
+ - * / | Add, subtract, multiply, divide |
^ | Raise to a power |
& | Join text, like CONCATENATE |
= <> != < > <= >= | Compare, giving true or false |
Dates and durations take part in arithmetic too: subtracting two dates & times gives a duration, and a duration can be added to a date & time, multiplied or divided. To shift a plain Date, give it a time first; adding a duration to a date alone is refused.
Functions
Function names are written in capitals. Where it matters, the table says how an empty cell is treated.
Logic
| Function | Returns |
|---|---|
IF(condition, then, else) | then when the condition is true, otherwise else |
AND(a, b, …) | True only if every argument is true |
OR(a, b, …) | True if any argument is true |
NOT(value) | The opposite of a true or false value |
ISBLANK(value) | True when the value is empty |
Numbers
| Function | Returns |
|---|---|
SUM(a, b, …) | The arguments added together; empty cells count as 0 |
MIN(a, b, …), MAX(a, b, …) | The smallest or largest argument, ignoring empty cells |
ROUND(value, places) | The value rounded to that many decimal places |
TRUNC(value, places) | The value cut toward zero at that many decimal places |
CEILING(value), FLOOR(value) | Rounded up or down to a whole number |
ABS(value), SIGN(value) | The absolute value; −1, 0 or 1 by sign |
POWER(base, exponent), SQRT(value) | A power (same as ^); a square root |
MOD(dividend, divisor) | The remainder after division |
LN(value), LOG(value, base), EXP(value) | Natural log; log in a base (10 by default); e raised to the value |
Text
| Function | Returns |
|---|---|
CONCATENATE(a, b, …) | The values joined into one text (same as &); empty cells count as empty text |
UPPER(text), LOWER(text) | The text in capitals or lower case |
TRIM(text), LTRIM(text), RTRIM(text) | The text without spaces at both ends, the start or the end. A second argument names the characters to remove instead: TRIM("xyzhixyz", "xyz") gives hi |
LEN(text) | The number of characters |
LEFT(text, count), RIGHT(text, count) | The first or last count characters |
MID(text, start, count) | count characters starting at position start |
Dates and times
| Function | Returns |
|---|---|
DATE(year, month, day) | A date. DATE(timestamp) takes the date part of a date & time |
DATEBETWEEN(start, end, unit) | How many unit boundaries lie between two dates, like SQL's DATEDIFF: "days", "weeks", "months", "years", "hours", "minutes" or "seconds". It counts crossings, so 31 January to 1 February is 1 month. Negative when end is earlier |
DURATION(amount, unit) | A length of time to add to or subtract from a date & time: "seconds", "minutes", "hours", "days" or "weeks" |
Examples
| Goal | Formula |
|---|---|
| Line total | Quantity * Unit price |
| Price with 20% tax, to the cent | ROUND(Price * 1.2, 2) |
| Full name | First name & " " & Last name |
| Flag large orders | IF(Total > 10000, "Large", "Standard") |
| Days to ship | DATEBETWEEN(Ordered at, Shipped at, "days") |
| Due one week after ordering | Ordered at + DURATION(1, "weeks") |
| Country code in capitals | UPPER(TRIM(Country code)) |
In the editor each column name above is a chip you pick from the suggestions, not typed text.
Good to know
- A formula's result has a fixed type, worked out from the formula itself: a comparison gives a Boolean,
&gives Text, arithmetic on numbers gives a Number. - Deleting a column a formula reads deletes the formula column with it. Mycorr lists the formula columns that would go before you confirm, and you can restore them once the column they read is back.
- Columns can't change while a commit is open, formula columns included. See Edits and commits.
- Imported tables keep their formulas as the owner wrote them; you can't edit them in your model.