Open the editor in the right place
Where you open the editor decides which cells the formula fills. Start with a formula for the whole row. Give one cell its own formula only for a real exception, because that cell stops following its row.- For a whole row, every period and item it shows: point at the row name and select the fx button, Edit variable formula. Or right-click the row name and choose Edit formula.
- For each item under a breakdown, such as each department under Revenue: right-click the Revenue row and choose Edit formula for Department. When there is more than one level below, choose Edit child formula and pick the level.
- For one cell only: double-click the cell, or select it and press Enter.
Write the formula
1
Open the editor
Open it from the place that matches the cells you want to fill.
2
Type the calculation
Type the formula with the names you see in cfo.ai:Wrap a name in backticks when it contains spaces or punctuation. Names must match exactly, including capital letters. As you type, autocomplete suggests Variables, Dimensions, items, functions, and keywords. Press Ctrl Space to open it, and ⌥ F12 (Alt F12) to see the syntax and an example of the function under the cursor.
3
Save
Press Enter. If Revenue is 100 and Cost of Goods Sold is 40, Gross Profit shows 60.
= is optional, and cfo.ai removes it when it saves: =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.
For every operator and lookup, see Formula syntax. For every function, see Formula functions.
Click or point at a cell to reference it
While the editor is open, you can insert a reference by clicking, the way a spreadsheet does. A reference can go where the cursor sits in an empty editor, or after an operator, a bracket, a comma, orand.
- With the cursor in such a place, click another cell, a row name, or a period header. Clicking the previous month’s Revenue cell inserts
Revenue[-1], and clicking a period header inserts a date such as Last month. When the cursor is anywhere else, a click saves the formula and closes the editor. - In the editor that opens on a cell, put the cursor in such a place and press an arrow key to point at the neighboring cell. Keep pressing to move. F2 also starts pointing. Press Enter to keep the reference, or Esc to cancel. Once you type after the reference, the arrow keys move the cursor again.
- Double-clicking a cell opens that cell for editing instead. The editor saves first, without the reference the first click inserted.
sum(Units[Channel in any]) outlines each channel’s Units cell, and a plain Price outlines the Price cell that matches the row you are editing. A reference with a where clause, a mapping path such as Employee.Department, a date range, or a calculated filter value outlines only a cell that a click would insert as exactly that reference.
When the cell you click has fewer Dimensions than the cell you are editing, such as a rate with no months, cfo.ai inserts an absolute reference. Every month then reads that one cell.
Filter a reference
A filter limits a reference to some items or dates, such as Revenue for the West region.- Select the reference in the editor. Its panel opens.
- Select Add filter, choose a Dimension, and choose the items. The filter reads like a sentence: Where Region is West.
- For a date, choose a preset: Last 3 months of actuals, Last 6 months of actuals, Last 12 months of actuals, Year to date, Quarter to date, or Custom range. In a Forecast formula, the “last months of actuals” presets read the months up to Last close, which gives you a run rate. See Date filter presets.
Revenue[Region = "West"].
Make a reference absolute so it reads one value everywhere
An absolute reference does not adjust to the row or period being calculated. With no filter or period offset, it reads the same value in every row and period, so use it for an input such as a single tax rate or a reported figure:$ is what makes the reference absolute, and the chip shows a pin after its name while it is. Filters you wrote stay either way, and a filter that reads the current cell, such as `List Price`$[Plan = this.Plan], still changes with that cell’s Plan. A column of an imported table cannot be made absolute.
Point a reference at something else
- Select the reference to open its panel.
- Select the reference’s name, and search for the Variable or Dimension you want.
- Choose a result. It replaces the reference.
Go to a reference’s source
Point at a Variable’s reference and select the arrow at its end to go to that Variable’s row. The arrow reads Go to its row in this table, Go to its row on this page, or Go to source in and the table’s name. Your draft stays open. In the full editor, Back to cell appears whenever the cell you are editing is off screen, after following the arrow or after scrolling away by hand, and returns you to it; it reads Back to table when you are editing a whole row or column. The editor that opens on a cell does not offer it. When the source is on another Page, a card holds your draft until you go back. For an imported Variable that is on no table, the arrow opens About this variable’s source, with Open connection.Save, cancel, or open the larger editor
- Enter saves. In the editor that opens on a cell, Tab and Shift Tab save and move right or left. Clicking elsewhere also saves.
- Esc closes the editor without saving. In the larger editor, the close button also discards your changes.
- To get more room, select the expand button (Open full editor) in the editor that opens on a cell.
#NAME? until you fix it. See Fix a formula that shows an error or a wrong number. When the server refuses a save, the editor reopens with your draft and a notice says why; see When a change is not saved.
Override one cell
When you open a cell that has no formula of its own, the editor shows the formula it inherits, dimmed, as a suggestion.- Point at the dimmed formula to see where it comes from: Inherited from and the address that owns it. Edit source formula opens that formula.
- Click the dimmed formula, press →, or press Tab to copy it into this cell and edit it.
- Start typing to replace it.
Remove an override or a row’s formula
To make one cell follow its row again, select the cell and press Delete or Backspace; see Clear a value or a cell’s own formula. To remove a row’s own formula, open the row’s formula, delete all the text, and press Enter. The row then follows the formula it inherits, or its imported values. To find who added a formula or an override, and when, open version history; see Undo a change or restore an earlier version.Propagate a formula to the rows above
After you save a formula on a lower row of a breakdown, the editor can ask Propagate? with Would you like to propagate this formula to parent rows?- Choose Yes to give the cells above it formulas that add up the rows beneath them, with
sum. - Choose No to leave the rows above as they are.
Show or hide the formula columns
The formula columns start hidden.- Rest the pointer on the fx button, Show formulas, at the top of the row-name column to peek at them. They stay while the pointer is on them.
- Click the button to keep them open. Click it again, Hide formulas, to hide them. The choice is saved for this browser.
- In a phone browser, open the table’s … menu and choose Show formula columns.
Common patterns
Replace the example names with your own Variables and Dimensions. Before you save a pattern, check the Variable it belongs to, where it applies, and whether it is for Actuals, Forecast, or both.Gross profit and gross margin
Change from the previous month
[-1] reads the previous month. The same formula on a quarterly row reads the previous quarter.
Growth rate
Same month last year
[-12] reads the same month a year earlier. On a quarterly row it reads twelve quarters back, so use [-4] there.
Running balance
[-1] is a circular reference.
Total across a Dimension
Share of the total
Revenue reads that department, so the result is its share.
Average of chosen items
Trailing 12 months and trailing average
Year to date
Read a per-item input that has no months
`Annual Salary` in a monthly formula looks for it in each month, finds nothing, and reads 0. Inside the filter, this.Employee means the employee of the cell being calculated, so each employee’s row reads that employee’s salary. For more on this, see Lookups and filters.