Not every part of the formula language works everywhere. A formula behaves differently depending on what it is attached to, and a few features only work in one kind of table. There are three places a formula can live.

Because page layout totals and Financial Table rows behave identically, this article treats them as one thing and calls them Financial. Custom Tables are called Custom.

Every reference and pipe in this article carries one of these labels:

Calculated totals on report page layouts. When you add or edit a total on a report page layout, the formula field is part of the total's definition. This is how the standard profit subtotals in the financial statements are defined.

Row and column formulas in tables. In a report table, a calculated row or column shows a small formula pill. Clicking it opens the Formula Editor in a pop-up window with Save and Cancel buttons.

The editor has two modes, and it switches between them automatically.

By default, you get a two panel layout. On the left is the formula itself, where account references appear as coloured chips and everything else (numbers, operators, brackets) is ordinary text you type. On the right is an Insert Formula panel.

To add an account reference, choose the type of element from the Select type of element to insert dropdown, pick the specific account or preset formula, then click Insert.

The chip is inserted where your cursor was. To change an existing reference, click its chip: the panel switches to Edit Formula, and you can pick a different account and click Save, or click Cancel to leave it alone. To delete a reference, click the small × on its chip.

Chips are helpful because they show the account name rather than its number, so a formula reads as something like "MasterAccountId:Income + MasterAccountId:Cost of Sales" even though what is stored is {MasterAccountId:40000} + {MasterAccountId:50000}.

Use this mode when your formula is just accounts and arithmetic. If it needs any pipe, read the next section first. Chips and pipes don't mix, and it's much easier to start the right way than to unpick it afterwards.

When a formula contains a pipe character (|), the Insert Formula panel no longer applies and the formula is edited as plain text. The panel does not disappear the moment you type the pipe. It clears once you save the formula and reopen the total or row. This is expected behaviour, not a fault. Everything described in this article from Pipes onwards must be typed by hand.

So, the rule is simple: decide up front. If the formula is a plain combination of accounts and arithmetic, use the picker. If it needs any pipe at all, type it all yourself, braces included. The appendix at the end of this article lists every master account number so you don't have to look them up.

Around your account references you can use ordinary arithmetic. These work in both kinds of table.

Normal mathematical precedence applies: multiplication and division happen before addition and subtraction, and brackets override that.

Functions can be nested, so roundTo(abs({MasterAccountId:40000}), 2) is valid.

Every account reference is written inside curly braces as {Type:Value}. The type says what kind of thing you're pointing at; the value identifies it.

Remember that in a Custom Table, everything marked Both above gives a single value repeated across the table, only LinkedAccount varies.

The most common reference. Points at a master account and totals every account underneath it.

Master account numbers you will meet often:

The complete list of master account numbers is in the Appendix at the end of this article. Keep it on hand when writing formulas with pipes, since you'll need numbers rather than names.

Points at a standard account from the standard chart and totals every ledger account mapped to it. The value is a long internal identifier rather than a number, so always insert this one with the chip picker.

The same idea but identified by number instead.

Points at a single account in the current file, with no totalling of anything beneath it. This option only appears in the picker when a file is open. Like StandardAccountId, the value is a long internal identifier, insert it with the picker, and contact Support if you need it inside a formula that uses pipes.

Points at an account by the role it plays, rather than by number. Useful where the underlying account differs between charts.

The available roles are:

Totals every account from the master account you name through to the end of its section of the chart. This is how running subtotals down a profit and loss statement are built.

A specialist reference that totals all accounts carrying a particular account type.

Two values describing the report column rather than any account.

Days is the number of days in the period, useful for measures such as debtor days. TaxRate is the tax rate configured for the column, so a tax charge can be written as {EarningsBeforeTax} * {Report:TaxRate}.

Covered in its own section, Row and column linking, below.

Rather than writing out a profit subtotal by hand, you can use a preset. A preset is written as a single name in braces with no colon, and expands to the full sum of the relevant master accounts.

Presets can be combined with anything else, and are safely bracketed when they expand, so {GrossProfit} * 2 behaves as you'd expect.

Six presets are offered in the Standard Formula picker:

Two further presets work if you type them, but are not in the picker:

A pipe refines an account reference. Pipes go inside the braces, after the value, each introduced by a |:

You can combine pipes from different families in one reference.

By default a reference gives you the closing balance. Two pipes change that.

These follow the relationship you'd expect: closing balance = opening balance + movement. So, both of these describe the same figure:

A typical use is showing the movement in a balance sheet class explicitly:

The two pipes are mutually exclusive, and if you write both then the last one wins. In {MasterAccountId:40000|OpeningBalance|Movement} you get the movement.

Normally each column of a Financial table calculates its own value. A column pipe pins the reference to one report column and repeats that figure across the whole row — which is how you show a prior-year comparative alongside current-year figures, or express every column as a percentage of one of them.

To show each column as a percentage of the primary column's income:

In a Custom Table these pipes still work, but they do something slightly different: they choose which report page column supplies the single value that gets repeated across your table. That's genuinely useful — it's how you bring a prior-period figure into a Custom Table as a constant.

These pipes are not case-sensitive, so |primary works as well as |Primary. If the column you name doesn't exist in the report, the reference gives zero rather than an error.

These pick out children of an account rather than totalling all of them. They are most often used to exclude a subtotal line that would otherwise be counted twice.

These pipe names are case-sensitive. AllButFirstChild works; allbutfirstchild is silently ignored.

Where the child selection pipes work by position, ChildMatch picks a child by what it is. The form is ChildMatch:Type:Value, where the type is any of MasterAccountId, MasterStandardAccountId, StandardAccountId, AccountId or SystemAccount.

That reads as: of the children of this column's linked account, take only the one that is master account 33000 (Retained Earnings). Like the child selection pipes, this only applies to {LinkedAccount:…}.

These narrow a reference to accounts that sit in a particular part of the chart. Each takes the form FilterName:Type:Value, using the same set of types as ChildMatch.

The pattern to reach for here is splitting one total into two complementary parts. Using SelfOrDescendantOfFilter for one line and NotSelfOrDescendantOfFilter for the other guarantees the two add back to the whole.

These work on ordinary account references and on {LinkedAccount:…}. They have no effect on {Cumulative:…} or {AccountTypeId:…}.

In a Custom Table, rows and columns can each be linked to an account. Linking lets you write one formula that works everywhere, instead of a separate formula per row. Rather than naming an account outright, you refer to "whatever account is linked here":

Both accept all the pipes above: balance pipes, column pipes, child selection, ChildMatch and hierarchy filters.

In a row formula, {LinkedAccount:Column} resolves separately for each column, giving a different figure per column, while {LinkedAccount:Row} resolves the row's single account once and repeats it across the row.

In a column formula, it's the mirror image: {LinkedAccount:Row} resolves separately for each row, while {LinkedAccount:Column} resolves once and repeats down the column.

This is what makes {LinkedAccount:Column} the only way to get a Custom Table row whose value genuinely changes from column to column.

In a row formula, to show the movement in whatever account each column is linked to:

Where a linked account's children end with a subtotal row you don't want to double count, take everything but the last child, from the primary column's opening balance:

Swapping Column for Row gives the row linked equivalent:

If you don't give a column pipe, a linked account reference uses the first column by default.

A calculated line shows zero. This is the usual symptom of a formula problem, because most mistakes fail quietly rather than raising an error. Check, in order:

A line in a Financial table produces nothing at all: Check for a {LinkedAccount:…} reference. It can only be calculated in a Custom Table.

Every column in a Custom Table shows the same number: That is expected for ordinary account references — they resolve once and repeat. Use {LinkedAccount:Column} if the figure should vary by column.

{Report:Days} or {Report:TaxRate} looks wrong in a Custom Table: These read from the report page's periods, not your table's columns. They are only meaningful in a Financial table.

A figure has the wrong sign: Remember that debits are positive and credits negative, so income and liabilities arrive negative. Gross profit is Income plus Cost of Sales, not minus.

Both OpeningBalance and Movement are in one reference: Only the last one applies. Remove whichever you don't want.

The Insert panel has disappeared: Your formula contains a |. This is normal — edit the formula as text.

The formula won't save: A formula cannot be empty, and cannot be longer than 2,024 characters.

GrossProfit · OperatingProfit · EBITDA · EBIT · EarningsBeforeTax · Profit · ProfitBeforeExceptionalItems* · ProfitFromContinuingOperations*

* Typed only, not in the picker

Types usable in ChildMatch and the hierarchy filters: MasterAccountId, MasterStandardAccountId, StandardAccountId, AccountId, SystemAccount.

Every master account, in chart order. A reference to a parent totals everything beneath it, so {MasterAccountId:10001} picks up all of Current Assets.

The top-level accounts above are listed in the order the preset formulas accumulate them — GrossProfit stops after Cost of Sales, EBITDA after Other Expenses, and so on down the list. That is also the order {Cumulative:…} follows.