# Formula

> Use a Formula column to calculate a value from other columns on the same record, such as a line total, a full name, or a due-date label.

A Formula column calculates a value from other columns on the same record and saves the result with it. When one of those columns changes, the result updates. People can read a formula but can't edit it.

## When to use it

Use a Formula for values that follow directly from the record itself:

- Line Total, from Quantity times Unit Price.
- Full Name, from First Name and Last Name.
- A "High" or "Low" label based on an amount.

Pick a different type when:

- The inputs come from linked records, such as a sum of order lines: use a [Rollup](/docs/columns/rollup).
- The value depends on other tables, the person saving, or the moment of saving: use a [computation rule](/docs/business-rules/computation).

## Settings

| Setting | What it does |
| --- | --- |
| **Formula Expression** | The calculation. It can be up to 2,000 characters long. |
| **Return Type** | What the formula produces: **Text**, **Number**, **Boolean** (true or false), or **Date**. |
| **Decimal Places** | How many decimal places to keep, from 0 to 10. Only for the Number return type. |

Pick the return type that matches what your expression produces. If SAPUGO can tell that the expression produces a different type, it suggests the right one. The return type decides how the column sorts and filters.

## Write a formula

Refer to other columns by their **Field Name**, the internal name generated when the column was created. A column named "Unit Price" has the field name `unit_price`. Click a column under **Available Fields** to insert it, or start typing to get suggestions for columns and functions.

You can combine columns with operators:

| Operator | Meaning |
| --- | --- |
| `+` `-` `*` `/` | Add, subtract, multiply, divide |
| `=` `!=` | Equal, not equal |
| `>` `<` `>=` `<=` | Greater than, less than, and so on |
| `\|\|` | Join two pieces of text |

Put text in double quotes, like `"High"`. Some examples:

```text
quantity * unit_price
```

```text
CONCAT(first_name, " ", last_name)
```

```text
IF(GREATER_THAN(amount, 1000), "High", "Low")
```

```text
ROUND(DIVIDE(hours_spent, hours_planned) * 100, 1)
```

SAPUGO checks the formula as you type and shows any errors before you save.

> **Tip:** Not sure how to write it? Describe what you want in the **AI Formula Assistant**, for example "multiply quantity by price and add tax", and click **Generate**. If you already have a formula, describe the change and click **Modify**.

## Functions

Every function is available on every plan. Browse them in the **Functions Library** below the editor, or search by name.

### Math

| Function | What it does |
| --- | --- |
| `ADD(a, b)` | Adds two numbers. |
| `SUBTRACT(a, b)` | Subtracts the second number from the first. |
| `MULTIPLY(a, b)` | Multiplies two numbers. |
| `DIVIDE(a, b)` | Divides the first number by the second. Returns nothing if the second number is 0. |
| `ABS(a)` | The absolute value of a number. |
| `ROUND(a, places)` | Rounds a number to the given decimal places. |
| `FLOOR(a)` | Rounds down to the nearest whole number. |
| `CEIL(a)` | Rounds up to the nearest whole number. |
| `MOD(a, b)` | The remainder after dividing. Returns nothing if the second number is 0. |
| `POWER(a, b)` | Raises the first number to the power of the second. |
| `SQRT(a)` | The square root. |
| `LN(a)` | The natural logarithm. |
| `SIN(a)`, `COS(a)`, `TAN(a)` | Sine, cosine, and tangent, with the input in radians. |
| `GREATEST(a, b, ...)` | The largest of the values. |
| `LEAST(a, b, ...)` | The smallest of the values. |

### Text

| Function | What it does |
| --- | --- |
| `CONCAT(a, b, ...)` | Joins text values together. |
| `UPPER(text)` | Converts text to uppercase. |
| `LOWER(text)` | Converts text to lowercase. |
| `LENGTH(text)` | The number of characters. |
| `TRIM(text)` | Removes spaces from the start and end. |
| `LEFT(text, n)` | The first n characters. |
| `RIGHT(text, n)` | The last n characters. |
| `SUBSTRING(text, start, length)` | Part of the text, from a position and with a length. |
| `REPLACE(text, find, replacement)` | Replaces every occurrence of some text. |
| `REVERSE(text)` | Reverses the text. |
| `POSITION(find, text)` | The position of the first value inside the second. |
| `SPLIT_PART(text, separator, n)` | Splits the text and returns the part at position n. |

### Logic

| Function | What it does |
| --- | --- |
| `IF(condition, then, else)` | Returns one value if the condition is true and another if it is false. |
| `EQUALS(a, b)` | Checks whether two values are equal. |
| `GREATER_THAN(a, b)` | Checks whether the first number is greater than the second. |
| `COALESCE(a, b, ...)` | Returns the first value that isn't empty. |

### Date

| Function | What it does |
| --- | --- |
| `EXTRACT_YEAR(date)` | The year of a date. |
| `EXTRACT_MONTH(date)` | The month of a date. |
| `EXTRACT_DAY(date)` | The day of the month. |
| `EXTRACT_HOUR(date)` | The hour of a date and time. |
| `EXTRACT_MINUTE(date)` | The minute of a date and time. |

### Conversion

| Function | What it does |
| --- | --- |
| `TO_TEXT(value, places)` | Converts a value to text. For a number, the optional second argument sets the decimal places, for example `TO_TEXT(price, 2)`. `TO_STRING` does the same. |
| `TO_NUMBER(value)` | Converts a value to a number. Returns nothing if it isn't a valid number. |

## What a formula can read

A formula can use most columns on the same record. It can't use other Formula columns, Rollups, Location columns, or the other side of a Relation. Checkbox columns are read as true or false, Number columns as numbers, Date columns as dates, and most other columns as text.

## Tips

- Write the calculation in words first ("line total is quantity times unit price"), then as a formula.
- If any input comes from another table, it isn't a formula. Use a Rollup or a computation rule.
- Changing the expression of an existing formula recalculates the column for every existing record when you save.
- Formula columns can't be converted to another type, and can't be added to time series tables.

---

Source: https://community.sapugo.app/docs/columns/formula