# Formulas, and how series total up

axmo-format-version: ed0892bbbdf4

**The functions** — signatures, argument shapes, reserved names — are in
[`../generated/functions.md`](../generated/functions.md). This page covers the grammar you need most often.

## Writing a formula

- **Names are written as they are**, spaces included: `Production Volume * Production Time / 60`.
- **Put a name in brackets** when it holds punctuation or would otherwise be misread: `[Stage load, h]`,
  `[Margin %]`. Brackets right after a series are a **filter**: `Cost[Actual]`.
- **Names are case-insensitive**, functions too.
- **Series line up on the dimensions they share.** `Volume * Usage * Price` multiplies a series over product × month
  by one over product × stage × resource and one over resource × month. The result has every dimension of all
  three, with no lookups.
- **The formula may not have a dimension its series lacks.** A calculated series' dimensions describe the business
  situation, and the formula's result must fit inside them. A series may have **more** dimensions than its formula
  (it is then the same across the extra ones), never fewer. To drop a dimension, read its total or filter it (below).

## Filters: picking elements

| Written | Means |
|---|---|
| `Cost[Actual]` | Cost for Version *Actual*; the result no longer varies over Versions. |
| `Resource cost[Frying pans, Europe, 2027-Q3]` | Several dimensions pinned at once, one element each. |
| `Cost[Versions/Actual]` | The same, with the dimension named, when an element name is ambiguous. |
| `Revenue[All_Products]` | **The total element**: the value the model already holds for all products. |
| `Closing Balance[Month[-1]]` | The previous month (relative offset); `[Month[+1]]` is the next. |
| `AVG(Receipts[Month[-2:0]])` | A set (here the last three months) reduced by a function. At the start it uses the months that exist. |
| `Month@Quarter` | The current month's position within its quarter (1–3); `Month@` is its position overall. |

**Dropping a dimension: read the total element.** `[Stage load, h][All_Products]` reads the total the model has
already calculated. It equals a sum of the members only when the series totals up with
`SUM` on that dimension. With any other roll-up it is that roll-up's total.

## More forms

Each of these is shown working in the [formula-forms](04-recipes.md#formula-forms) recipe.

| Written | Means |
|---|---|
| `Sales > 100`, `=`, `<>`, `<`, `<=`, `>=` | A comparison: true or false. Comparisons do not chain (`a < b < c`). |
| `IF(Sales > 100 and Units >= 5, 1, 0)` | A choice. `and`, `or` and `not (…)` combine conditions. |
| `Units ^ 2` | A power. `2 ^ 3 ^ 2` is 64 (left to right) and `-2 ^ 2` is 4, as in Excel. |
| `ROUND(Sales / 7, 2)`, `MAX(Sales, Units * 30)` | Functions of values; the full list is in [`../generated/functions.md`](../generated/functions.md). |
| `ISEMPTY(Units)` | True where an input holds no value (a loaded 0 is not empty). |
| `SUM(Sales[2027-01:2027-02])` | An element range: from one element to another. |
| `Sales[Month[$1]]` | An absolute position: the **first month of the horizon**. |
| `SUM(Sales[Month[$1:0]])` | From the first month of the horizon to this one: a running total that does **not** restart each year. Give it `LAST` across time, so a quarter shows its last month. |
| `SUM(Sales[Year[0]/Month[$1:0]])` | **Year to date**: `Year[0]/` scopes `$1` to the current year, so it restarts each January. Give it `LAST` across time too. |
| `SUM(Marketing[Region])` | **A layer as a set**: every element of that layer in the whole dimension, each read at its own total. The result is the same at every element of the dimension. It needs an aggregation. |
| `Month[$1]`, not `Sales[$1]` | A position must name its layer; `Sales[$1]` is refused (`UNANCHORED_POSITIONAL`). |

**There are no text literals**, and `Dimension.Element` is not a form: write
`Cost[Versions/Actual]`. The parser accepts more — sets, anchors, `WHERE` (one is in
[pl-from-ledger](04-recipes.md#a-pl-from-a-general-ledger)) — that this guide does not cover yet; check any other
form with the tool before relying on it.

## Empty, zero and failed

- **To leave a result empty**, give `IF` no third argument: `IF(Sales > 0, Marketing / Sales)` is empty where the
  condition fails, not 0. With `FORMULA` totals the guard works at every level: a
  quarter's figure over empty months gives the quarter its share, and the months stay empty.
- **An empty cell counts as zero in arithmetic.** `Nz(x, fallback)` replaces an empty value. A reference before the
  first month is empty, which is how a balance is seeded: `Nz(Balance[Month[-1]], Opening)`.
- **A division by zero fails the cell**, and everything calculated from it fails too. `IFERROR(x, fallback)` catches
  it; `Nz` does not. `ISERROR(x)` tests for it.
- **Inputs stay sparse; calculated series are dense.** An empty input stays empty; `ISEMPTY` tells it from a 0. A calculated series has a value at
  every combination, with empties counted as zero.

## How a series totals up

Every series says, **for each of its dimensions**, how its value at a total is formed. That is the `rollUp`.
**A calculated series totals like an input:** with `SUM`, a total adds up its members' values; only `FORMULA`
re-evaluates the formula at the total. So `Nights Available = Days`, over apartments and months, sums to days ×
apartments at the business total. Choose the roll-up from what the quantity is, not by habit:

| The quantity is… | rollUp | Example |
|---|---|---|
| An amount or a count that adds up | `SUM` (the default) | volume, cost, hours |
| A 0/1 flag (`IF(Occupancy < 0.5, 1, 0)`) | `SUM`: at a total, a count | apartment-months below a threshold |
| A stock or balance, across time | `LAST` across time; `SUM` across everything else | cash, inventory, outstanding loan |
| An opening position, across time | `FIRST` across time | opening balance |
| A ratio of two series | `FORMULA`: the formula is evaluated at the total from the total's own inputs, whatever its members hold. An **input** series needs `rollUpFormula`; a calculated one uses its own | margin %, utilisation, average price, share |
| A per-unit input that totals as a weighted average | `WEIGHTED_AVG` with `weightSeries` | unit cost weighted by volume |
| A rate or price, across time | `AVG` | monthly exchange rate, price |
| Values that must not be combined | `NONE`: the total stays empty | kWh + m³; Actual + Budget; local currencies |
| A period-on-period change, across time | `NONE` across time | month-on-month growth, which has no meaning at a quarter |

**Rules that bite:**

- **The order of a series' `dimensions` list is the order in which totals are calculated** where methods differ.
  An average of sums is not a sum of averages.
- **A `FORMULA` total uses the totals of the series it reads, each by its own rule.** `Cost[Actual] / Headcount`,
  with headcount averaged across time, gives a quarter's cost over its average headcount.
- **A `FORMULA` total needs an input on all of the series' dimensions.** An input bound on fewer (a rate by Time
  only) reaches no total by itself, so a formula guarded on such an input alone stays empty at its totals.
- **A ratio is never `SUM` or `AVG`.** Adding margins gives nonsense; averaging them ignores size.
- **A `FORMULA` total must have an answer at every level.** A formula that reaches for something a total does not
  have, such as a *previous month* at a quarter, fails there, and the update reports
  `SERIES_CELLS_FAILED … in the roll-up formula for "Time"`. Use `NONE` on that dimension, or guard with `IFERROR`.
- **A weight or driver series must drive every dimension it spans.** If *Volume* varies over product and time and
  weights *Unit Cost* on product, it must weight it on time too. Naming a driver on a dimension whose values are
  loaded at its own elements moves none of them: a split-down splits only what is entered above. **And it may not have a dimension its target
  lacks:** a price by product and month cannot be weighted by units sold by *shop*, product and month. Then give the
  input a plain rule (`NONE` across products, `AVG` across time), and show the true average as a calculated ratio
  with `FORMULA` totals: `Average Price = Revenue / Units Sold`.

## Entering above the grain: split-down

An input loaded at a total — a target per quarter, a price per country — is split to the members below it by its
`splitDown` rule on that dimension:

| Rule | Use for |
|---|---|
| `COPY` | **Rates and prices**: every member gets the same value (a country's price for each of its plants). |
| `EQUAL` | **Amounts** shared evenly (a quarter's target to its three months). |
| `PROPORTIONAL` + `driverSeries` | **Amounts** shared by a key (a target to products by last year's sales). |
| `FIRST` / `LAST` | Everything to the first or last member. |
| `NONE` (the default) | No split: the value stays where it was entered. With an active roll-up the load warns. |

A split's total stays the value entered. Choose split and roll-up as a pair: `EQUAL` or `PROPORTIONAL` with `SUM`,
and `COPY` with `AVG` or `NONE`.