Price * Quantity or Revenue[-1] * 1.1. This page lists each part of the formula language and what it means. For the functions, see Formula functions. For the steps to write one, see Write a formula. To learn how cfo.ai picks which formula fills a cell, read How formulas work.
Names of Variables and Dimensions
Names match exactly, including capital letters:
`Deferred revenue` does not find a Variable named Deferred Revenue. A formula stores the Variable itself, not its name, so renaming a Variable updates every formula that uses it.
Numbers, text, and empty values
A cell that no formula or value covers has a default. A number reads 0, so sums keep working. A date or text Variable reads empty, so
`End Date` = null is true for a person with no end date.
Arithmetic treats an empty value as 0: null + 1 is 1. Dividing by an empty value is a division by zero. A minus sign in front, and abs, keep an empty value empty. A comparison with an empty value, other than = null, is itself empty, so if takes its third argument.
Operators
% between two numbers is the remainder, so 17 % 5 is 2. A number written with %, such as 50%, is one value, read before any operator applies: 75% - x is 0.75 - x and 2 ^ 50% is 2 ^ 0.5, as in a spreadsheet. 100 % 3 is still the remainder, because a value follows the %. A % after anything else, such as (a + b)%, is a percentage only when no value follows it, and it takes its place in the order below.
Order of operations
From first to last, cfo.ai applies operators in this order:- Parentheses and function calls
- A minus sign in front of a value
^%as a percentage after a parenthesized value or a reference, such as(a + b)%; a number written with%is already one value*,/, and%as a remainder+and-- Comparisons:
<,<=,>,>=,=,<> inandnot innotandor
^ works from right to left, unlike in Excel: 2 ^ 3 ^ 2 is 2 to the power of 9, which is 512. Use parentheses when the order matters.
When and or or already has its answer, an error on the other side does not matter: false and an error is false, and true or an error is true.
Lookups and filters
A lookup in brackets after a name changes which items the reference reads. Every Dimension you do not name still follows the cell being calculated.
When a lookup reads several items, wrap it in a function that says how to combine them, such as
sum, average, or count: average(Revenue[Department in any]). See Aggregation functions.
A lookup key matches items by the same = rule as the rest of the formula, whatever kind the item is. The number key in Amount[Entity = 3019681] reads a text item named 3019681, a text key such as "1" reads the number item 1, and an aggregation over a lookup, such as sum(Amount[Entity = 1]), adds every item the key equals this way. A formula’s condition, which says where the formula applies, matches only items of the key’s own kind; see Ari’s save warnings.
Inside where, a plain name refers to the item being tested, and this. to the cell being calculated: sum(Revenue[Department in any] where Revenue > this.Revenue) adds the departments with more revenue than this one.
A lookup or offset attaches to a Variable’s name, not to a calculation. (Revenue - Cost)[-1] is an error; write Revenue[-1] - Cost[-1].
Lookups through a Dimension mapping
When a mapping assigns each item of one Dimension to an item of another, such as accounts to reporting lines, a dot follows the mapping:Account.Bucket is the line the cell’s account belongs to. A lookup on the mapped Dimension gathers every item the mapping puts there: sum(Amount[Bucket = "COGS"]) adds every account in COGS. See Dimension mappings and shared Dimensions.
Absolute references
A$ right after a name makes an absolute reference. It reads only the items you name and does not adjust to the cell being calculated.
In the formula editor, an absolute reference’s chip keeps its icon and shows a pin after the name, where the
$ sits in the text. Pointing at the pin reads Absolute reference: doesn’t adjust to the current row or period. F4 or the pin button in the reference’s panel makes the reference at the cursor absolute or releases it, and the saved text is the same as typing the $. A column of an imported table cannot be made absolute.
Period offsets
A number in brackets reads a nearby period, at the cell’s own grain.
A period offset is how a running balance reads its own previous value:
`Cash Balance`[-1] + `Net Cash Flow`. Without the offset, the formula reads itself and the cell shows #CIRC!.
Dates
Date grains
Date with a grain names the period a formula works in. Write the grain with a capital letter.
Fixed periods and date text
Date text names the start of a period. These forms work:
2026, 2026-01, 2026-01-31, 2026-Q1, 2026-H2, July 2026, Jul 1, 2026, 1 July 2026, 7/1/2026, and 2026-07-01T13:30:12Z. In 7/1/2026 the month comes first, unless the first number is over 12, as in 13/7/2026. These do not work: Q1 2026, Sept 2026, July with no year, and two-digit years such as 7/1/26.
Revenue["2026"] is not a filter for 2026. Write Revenue[Date.Year = "2026"].
Date ranges
A range includes both ends. A range written from the later date to the earlier one matches nothing.Date filter presets
When you add a date filter to a reference in the formula editor, the presets write a range for you. What a preset covers depends on whether you are editing an Actuals formula.
On a reference by quarter, half-year, or year, the presets offer the last 2, 3, or 4 of those periods instead, such as Last 4 quarters of actuals.
For example, a Forecast formula that averages Revenue with the Last 3 months of actuals filter reads a run rate: every Forecast month uses the average of the last three closed months.
Dates against imported timestamps
An imported Dimension can hold timestamps, such as one item per sync at2026-07-01 00:00:00+00. Where you write a date decides what it matches:
- In a lookup,
date(2026, 7, 1)or a date Variable selects the item at midnight of that day, as long as every item of the Dimension comes from the source. - In a formula’s condition, a date never equals a timestamp item, so
[synced_month = date(2026, 7, 1)]reaches no cell. - Date text such as
"2026-07-01"reaches the item at that moment in both places. synced_month.Month = startofmonth(`Snapshot Month`$)compares two dates, so it matches every item in that month.
Conditions: where a saved formula applies
Every saved formula has a condition that says which cells it fills. You usually set it by choosing where to open the editor, or by telling Ari, rather than typing it. The formula editor’s title shows the condition as pills.
Ari’s formula cards in the chat show the same condition a little differently. A Dimension that applies to every item shows its name, such as Department. A + any pill marks a condition that names a Dimension and also applies to finer breakdowns, which is every such condition without a leading
$. [] reads Any Segment and $[] reads Empty Segment.
A $ in front of a condition limits it to exactly the Dimensions it names. A $ after a name in a formula makes an absolute reference. They are different things.
When several formulas could fill a cell, the more specific one wins; see How formulas work.
Comments
// starts a comment that runs to the end of the line. /* */ wraps a comment of any length.
A leading equals sign
You can start a formula with=, as in a spreadsheet. cfo.ai removes it when it saves, so =Revenue * 1.1 saves as Revenue * 1.1. In a cell of a text Variable, start a formula with =; without it, cfo.ai saves the words as text.
Inside a formula, = compares two values. Docs and Ari sometimes write a relationship with = to say which Variable a formula belongs to:
= as the formula of the Variable before it. Typed as one formula, the whole line would be a comparison.
Preferred operator forms
Older formulas may write some operators another way. Those forms still work, but write the preferred ones in new formulas.
Older function names, such as
avg for average, are listed in Formula functions.
How cfo.ai formulas differ from Excel and other spreadsheets
- A formula names Variables, not cells. There are no addresses such as
B7; use a period offset such asRevenue[-1]for the period before. and,or, andnotare operators, not functions.AND(a, b)shows the message ‘and’ joins conditions: (condition) and (condition). It cannot be called as a function.ifneeds all three arguments.^works from right to left.- There is no
&operator and no text function. Keep text as the values of a text Variable or as the items of a Dimension. - Some spreadsheet functions have no cfo.ai equivalent. See Excel functions that are not available.