# Sheet formulas

> Every operator, function, error and number format a report sheet formula understands, with worked examples on a small table.

Formulas add a column of your own to a [table block](https://docs.sanda-os.com.au/reports/sheets) on a report. The language is Excel's, without the parts that need a grid: the operators are Excel's, the function names are Excel's and each means what it means in Excel. That is what lets an [exported workbook](https://docs.sanda-os.com.au/reports/sheets#take-it-to-excel) carry the same formula and keep calculating.

## The sample table

The examples on this page run against this table, which is what a table block might return. The `net revenue` column is a sum, and `ikura` has no revenue.

| product | net revenue | cost |
|---|---|---|
| chai | 1,200 | 700 |
| aniseed syrup | 800 | 900 |
| chang | 2,000 | 500 |
| ikura | (empty) | 0 |

Where an example gives one result, it is the result on the `chai` row unless the text says otherwise.

## Syntax

A formula is an expression. A leading `=` is allowed and ignored, and spaces are ignored everywhere except inside text.

| Piece | Written as | Notes |
|---|---|---|
| Column | `[net revenue]` | The column's name in square brackets, spaces and all |
| Number | `12`, `3.5`, `.5`, `1e3` | No thousands separators. Write a negative with a minus sign: `-4`. |
| Text | `"chai"` | In double quotes. Write a double quote inside text by doubling it: `"say ""hi"""`. |
| Truth value | `TRUE`, `FALSE` | Shown as TRUE and FALSE |
| Function | `ROUND([cost], 1)` | A name, then arguments in brackets separated by commas |
| Grouping | `([a] + [b]) * 2` | Brackets change the order of work |

Function names are not case-sensitive: `sum`, `Sum` and `SUM` are the same function. A formula can be up to 500 characters. The editor says so under the box and will not save a longer one.

## References

A **column reference** in square brackets is the column's name as its header shows it. It is matched without regard to case, so `[Net Revenue]` finds `net revenue`.

A reference means one of two things depending on where it sits:

- **On its own or inside an operator or ordinary function**, it is **this row's value**. `[net revenue] - [cost]` is 500 on the `chai` row.
- **As a bare argument of `SUM`, `AVERAGE`, `MIN`, `MAX`, `COUNT` or `COUNTA`**, it is **the whole column**. `[net revenue] / SUM([net revenue])` is each row's share of the total: 0.3 on the `chai` row.

Only a *bare* `[column]` inside an aggregate reads the whole column. Any other argument is worked out for the current row. `SUM([net revenue] * 2)` is therefore not the total of a doubled column; on the `chai` row it is 2,400, the doubled value of that one row. To total a computed value, give it a column of its own and then sum that: add `revenue x2` as a formula, then use `SUM([revenue x2])`.

Some more rules:

- A formula can read the columns before it, including formula columns you added earlier. It cannot read itself or a column added after it.
- Whole-column functions cover the rows the block returned, no more. If the table says **of more**, the block's row limit cut the result short and a total covers only the rows you can see.
- A formula keeps reading a column you hide after writing it. The formula editor only accepts columns that are showing.
- A reference to a column that does not exist makes the whole formula unreadable: its column shows `#NAME?`, and the sentence under the table says which column is missing.

### Empty cells and text that looks like numbers

Formulas treat the data the way Excel does:

- An **empty cell is 0 in arithmetic**, so `[net revenue] - [cost]` on the `ikura` row is 0, not an error. An empty cell compared with text is the empty text.
- **Text that reads as a number is a number.** The warehouse often sends numbers as text (`2,000` or `0.00`). `"12" + 1` is 13, and `[net revenue] = 0` is true for a value that arrives as `"0.00"`. Two texts still compare as text, so the codes `"007"` and `"7"` are different.
- **Other text in arithmetic is an error.** `[product] * 2` is `#VALUE!`.
- **TRUE is 1 and FALSE is 0** in arithmetic.

## Operators

From the one that binds tightest to the one that binds loosest. Operators at the same level work from left to right, and brackets override the order.

| Operator | What it does | Example | Result |
|---|---|---|---|
| `%` after a value | Divides by 100 | `50%` | 0.5 |
| `-` or `+` before a value | Sign | `-[cost]` | -700 |
| `^` | Power | `2^3` | 8 |
| `*` and `/` | Multiply and divide | `[cost] * 10%` | 70 |
| `+` and `-` | Add and subtract | `[net revenue] - [cost]` | 500 |
| `&` | Joins text | `[product] & " (" & [cost] & ")"` | chai (700) |
| `=` `<>` `<` `>` `<=` `>=` | Compare, giving TRUE or FALSE | `[net revenue] > 1000` | TRUE |

As in Excel, a sign binds tighter than a power, so `-2^2` is 4 and not -4.

### Comparing

- Two numbers compare as numbers, even when one or both arrived as text.
- A number and an empty cell compare as if the empty cell were 0, so `IF([net revenue] = 0, ...)` is true on a row with no revenue. That is the guard people write against dividing by zero, and it works.
- Two texts compare **without regard to case**: `[product] = "CHAI"` is TRUE on the `chai` row, and `<` and `>` compare alphabetically.

## Functions


| Function | Arguments | Reads a whole column |
| --- | --- | --- |
| `SUM` | One or more | Yes |
| `AVERAGE` | One or more | Yes |
| `MIN` | One or more | Yes |
| `MAX` | One or more | Yes |
| `COUNT` | One or more | Yes |
| `COUNTA` | One or more | Yes |
| `ROUND` | 1 to 2 | No |
| `ROUNDUP` | 1 to 2 | No |
| `ROUNDDOWN` | 1 to 2 | No |
| `ABS` | 1 | No |
| `INT` | 1 | No |
| `MOD` | 2 | No |
| `POWER` | 2 | No |
| `SQRT` | 1 | No |
| `IF` | 2 to 3 | No |
| `IFERROR` | 2 | No |
| `AND` | One or more | No |
| `OR` | One or more | No |
| `NOT` | 1 | No |
| `ISBLANK` | 1 | No |
| `ISNUMBER` | 1 | No |
| `LEN` | 1 | No |
| `UPPER` | 1 | No |
| `LOWER` | 1 | No |
| `TRIM` | 1 | No |
| `CONCAT` | One or more | No |
| `TEXT` | 1 to 2 | No |
| `VALUE` | 1 | No |
| `LEFT` | 1 to 2 | No |
| `RIGHT` | 1 to 2 | No |

Column summaries: `sum`, `avg`, `min`, `max`, `count`. Formats: `auto`, `integer`, `number`, `percent`, `currency`, `compact`, `text`.



The functions marked as reading a whole column are the six aggregates. Functions that take any number of arguments (`SUM`, `AVERAGE`, `MIN`, `MAX`, `COUNT`, `COUNTA`, `AND`, `OR` and `CONCAT`) take at least one. The sections below say what each does.

### Whole-column functions

| Function | Returns | Example | Result |
|---|---|---|---|
| `SUM(x, ...)` | The total of the numbers | `SUM([net revenue])` | 4,000 |
| `AVERAGE(x, ...)` | The mean of the numbers. `#DIV/0!` when there are none. | `AVERAGE([net revenue])` | 1,333.33 |
| `MIN(x, ...)` | The smallest number, or empty when there are none | `MIN([net revenue])` | 800 |
| `MAX(x, ...)` | The largest number, or empty when there are none | `MAX([net revenue])` | 2,000 |
| `COUNT(x, ...)` | How many numbers | `COUNT([product])` | 0 |
| `COUNTA(x, ...)` | How many values are not empty | `COUNTA([product])` | 4 |

These read numbers only and skip anything else, so `AVERAGE([net revenue])` divides 4,000 by 3: the empty `ikura` cell is left out, not counted as 0. Give an aggregate two columns and it reads both in full: `SUM([net revenue], [cost])` is 6,100. A value that is not a bare column, such as `SUM([cost], 10)`, is worked out for the row and counted once.

### Rounding and arithmetic

| Function | Returns | Example | Result |
|---|---|---|---|
| `ROUND(x, [places])` | `x` rounded, halves away from zero. `places` defaults to 0 and may be negative. | `ROUND(1.005, 2)` | 1.01 |
| `ROUNDUP(x, [places])` | `x` rounded away from zero | `ROUNDUP([net revenue] / 7, 1)` | 171.5 |
| `ROUNDDOWN(x, [places])` | `x` rounded towards zero | `ROUNDDOWN(-2.349, 2)` | -2.34 |
| `ABS(x)` | The size of `x`, without its sign | `ABS([cost] - [net revenue])` | 500 |
| `INT(x)` | `x` rounded down to a whole number | `INT(-1.5)` | -2 |
| `MOD(x, d)` | The remainder of `x` divided by `d`, with the sign of `d`. `#DIV/0!` when `d` is 0. | `MOD(-7, 3)` | 2 |
| `POWER(x, y)` | `x` to the power `y` | `POWER(2, 10)` | 1,024 |
| `SQRT(x)` | The square root. `#NUM!` for a negative. | `SQRT(16)` | 4 |

`ROUND` works on the number as written, so it agrees with Excel where plain arithmetic would not: `ROUND(1.005, 2)` is 1.01, `ROUND(-2.5, 0)` is -3 and `ROUND(1234.5, -2)` is 1,200. `ROUND(x)` with no second argument rounds to a whole number.

### Logic

| Function | Returns | Example | Result |
|---|---|---|---|
| `IF(test, then, [else])` | `then` when `test` is true, otherwise `else`, or FALSE when `else` is left out | `IF([net revenue] > [cost], "profit", "loss")` | profit |
| `IFERROR(x, fallback)` | `x`, unless working it out gives a formula error, then `fallback` | `IFERROR([cost] / [net revenue], "n/a")` | 0.5833 (and n/a on `ikura`) |
| `AND(x, ...)` | TRUE when every argument is true | `AND([net revenue] > 1000, [cost] < 800)` | TRUE |
| `OR(x, ...)` | TRUE when any argument is true | `OR([net revenue] > 1500, [cost] > 800)` | FALSE |
| `NOT(x)` | TRUE when `x` is false | `NOT([net revenue] > 1000)` | FALSE |
| `ISBLANK(x)` | TRUE when `x` is empty | `ISBLANK([net revenue])` | TRUE on `ikura` |
| `ISNUMBER(x)` | TRUE when `x` is a number, or text that reads as one | `ISNUMBER("12")` | TRUE |

A test counts as true when it is TRUE, a number other than 0, or text that is not the number 0. It is false when it is FALSE, 0 or empty.

`IF` only works out the branch it returns, so `IF([net revenue] = 0, 0, [cost] / [net revenue])` never divides by zero. `IFERROR` rescues `#DIV/0!`, `#VALUE!` and `#NUM!`. It cannot rescue `#NAME?`, because a formula that cannot be read is never run.

### Text

| Function | Returns | Example | Result |
|---|---|---|---|
| `LEN(x)` | The number of characters | `LEN([product])` | 4 |
| `UPPER(x)` | Upper case | `UPPER([product])` | CHAI |
| `LOWER(x)` | Lower case | `LOWER("Chai")` | chai |
| `TRIM(x)` | Removes leading and trailing spaces and squeezes runs of spaces to one | `TRIM("  a   b ")` | a b |
| `CONCAT(x, ...)` | The arguments joined as text | `CONCAT([product], ": ", [cost])` | chai: 700 |
| `LEFT(x, [n])` | The first `n` characters. `n` defaults to 1. | `LEFT([product], 3)` | cha |
| `RIGHT(x, [n])` | The last `n` characters. `n` defaults to 1. | `RIGHT([product], 2)` | ai |
| `TEXT(x, format)` | A number written as text with thousands separators | `TEXT([net revenue], "0.00")` | 1,200.00 |
| `VALUE(x)` | Text read as a number. `#VALUE!` when it is not one. | `VALUE("1,200")` | 1,200 |

`TEXT` reads only how many zeros follow the decimal point in its format. `"0.00"` means two decimals and `"0"` means none. It always adds thousands separators, and it does not understand percent, currency or date formats. Text that is not a number comes back unchanged. To show a value as a percent in a column, set the column's [format](#number-formats) instead.

## Errors

A formula that cannot be worked out on a row shows an error word in that cell, and the rest of the table carries on. Errors are Excel's words.

| Cell shows | Meaning | Typical causes |
|---|---|---|
| `#DIV/0!` | Divided by zero | `[cost] / [net revenue]` on a row with no revenue. `MOD(x, 0)`. `AVERAGE` of no numbers. |
| `#VALUE!` | A text value where a number was needed | `[product] * 2`. `VALUE("abc")`. |
| `#NUM!` | Not a real number | `SQRT` of a negative. A result too large to hold. |
| `#NAME?` | The formula cannot be read at all | An unknown column or function, a missing closing bracket or quote, or the wrong number of arguments |

A `#NAME?` fills the whole column, marks the column's header with a red `!`, and the first problem is written under the table after the column's name. Some of the sentences you may see:

| Under the table | What to fix |
|---|---|
| there is no column called “units” | Spell the column as its header shows it |
| FOO is not a function this sheet knows | Check the function's name against the list above |
| SUM is missing its closing ) | Close the bracket |
| a column reference needs a closing ] | Close the square bracket |
| a text value needs a closing quote | Close the double quote |
| the formula ends too soon | Something is missing after an operator |

`IF` and `IFERROR` are how you handle the first three. A `#NAME?` is always a mistake in the formula itself.

## Number formats

A format changes how a value is **shown** and never the value itself, so a formula that reads a formatted column sees the underlying number. Use `ROUND` to change the value. A column's format is set from its menu on the table.

| Format | Value | Shown as |
|---|---|---|
| **automatic** | 1234.567 | 1,234.57 |
| **whole number** | 1234.567 | 1,235 |
| **number** | 1234.567 | 1,234.57 |
| **percent** | 0.256 | 25.6% |
| **currency** | 1234.5 | $1,234.50 |
| **compact (1.2M)** | 12500 | 12.5K |
| **compact (1.2M)** | 1250000 | 1.3M |
| **text** | 1234.567 | 1234.567 |

Detail worth knowing:

- **percent** multiplies by 100, so it expects a ratio. 0.256 shows as 25.6%, and 25.6 shows as 2,560.0%.
- **compact** leaves numbers under 10,000 in full (9,500 stays 9,500) and shortens larger ones to K, M or B.
- **automatic** leaves text alone and keeps a value such as `00123` as text, since a code with leading zeros is not a number.
- Numeric formats right-align the column.
- TRUE and FALSE always show as TRUE and FALSE.

## In the exported workbook

When you [download a table as xlsx](https://docs.sanda-os.com.au/reports/sheets#take-it-to-excel), each formula column is written as a formula in Excel's own grid. `[net revenue] - [cost]` on the first data row of the sample table becomes `=(B2-C2)`, and `[net revenue] / SUM([net revenue])` becomes `=(B2/SUM(B$2:B$5))`, with the whole-column reference turned into a fixed range over every data row. Excel recalculates it, so a formula column keeps working after you change a number in the file. A column you hid on the report is written into the workbook as a hidden column, so a formula that reads it keeps its cell reference and keeps calculating.

## Limits

| Limit | Value |
|---|---|
| Formula columns on one table block, hidden ones included | 12 |
| Length of one formula | 500 characters |
| Length of a column's name | 60 characters |
| Rows a formula can see | The rows the block returned, at most 200 |

What a formula cannot do: refer to cells such as `A1`, look things up (`VLOOKUP`), add conditionally (`SUMIF`), work with dates, or keep a running total. A formula sees only the rows of its own table. If you need a shaped number in more than one report, define it as a [metric](https://docs.sanda-os.com.au/semantic-fluid/metrics) so every report gets it.

## Worked examples

**Margin, and margin as a percent of revenue.** Add two columns. The second reads the first.

```text
margin        [net revenue] - [cost]
margin %      IF([net revenue] = 0, 0, [margin] / [net revenue])
```

Set `margin %` to the **percent** format. On the `chai` row it shows 41.7%, and on `ikura`, which has no revenue, 0.0%.

**Each row's share of the total.**

```text
share         [net revenue] / SUM([net revenue])
```

With the **percent** format the sample table reads 30.0%, 20.0%, 50.0% and 0.0%.

**Flag the rows that need a look.**

```text
status        IF([cost] > [net revenue], "loss", "ok")
```

Text results are left-aligned like any other text column.

**A tidy label.**

```text
label         UPPER(LEFT([product], 3)) & " · " & TEXT([cost], "0")
```

On the `chai` row this reads `CHA · 700`.

**A ratio that never shows an error.**

```text
cost ratio    IFERROR([cost] / [net revenue], 0)
```

On `ikura` this is 0 instead of `#DIV/0!`.