Skip to main content
When you edit a cell, you write a formula. In Francis, a hardcoded value is also considered a formula - just a simple one. Francis formulas reference rows by name and period rather than by cell position. The benefit is that you can rename a row, move a section, or reorganize an entire sheet without any formulas breaking. This is the main practical difference from Excel, and it matters most when your model evolves mid-cycle.

​
Syntax

Francis formulas follow the pattern "ROW_NAME"[RELATIVE_PERIOD]. The period offset is always relative to the current cell:
  • [0]: current period
  • [-1]: previous period
  • [-12]: 12 months ago
A prior-period reference:
A driver-based cost:
A balance sheet movement (last period + addition - detraction):
Because all references are relative, rolling forecasts work without any maintenance. As time moves forward, every formula automatically references the right period.
A group sums its contents, so reference the group rather than the individual rows inside it. When you add a new row to the group, any formula pointing at the group picks it up automatically, with no manual updates as the model grows.

​
Sign conventions

Income and balance sheet items are entered as positive values. Expenses are entered as negative values. This is how Francis presents actuals from your accounting system, so follow this convention to compare apples to apples. The practical effect: subtotals add rather than subtract. Gross profit looks like this:
Not this:
Direct costs are already negative, so adding them reduces the total correctly. The same logic applies down the P&L: every subtotal is a sum, and the signs in the source rows do the work.

​
Fill right

Write the formula logic once, then fill right to apply it to every future period. You don’t need to repeat the formula cell by cell. The same formula carries forward, and because references are relative, each period resolves against its own neighbors. It’s a small mechanic that most users come to rely on heavily once it’s part of their workflow. Fill right applies to rows. A calculation already uses one formula across every period by definition, so there is nothing to fill right. Override individual cells whenever you need to. To make a step change at a point in time, write a new formula in that period and fill right again. Everything from that period forward picks up the new logic, and the periods before it keep the old. Fill right works within the layer you’re in. Fill right in the actuals layer and the formula applies to all future periods of the actuals layer, so when you close a new month the formula is already in place. Fill right in the forecast layer and it applies to future forecast periods. To run the same formula across both layers, enter it in each layer and fill right in both. In the actuals layer, start from far enough back, because fill right only carries forward, as the name says. Begin at the earliest period you want the formula to cover, otherwise the periods to its left stay untouched.

​
Overriding imported actuals

Actuals cells hold values from your mapped data sources, but they are still cells you can write to. Write a value or a formula into an actuals cell and it replaces the imported value for that month. Francis keeps what you wrote, so the cell shows your number rather than the one your accounting system holds. Override deliberately, not as a habit. The legitimate cases are narrow: an accrual your accounting system hasn’t booked yet, a reclassification you don’t want to make in the ledger, or a known posting error you’re correcting ahead of the fix. Add a comment explaining why, because an override is otherwise invisible to whoever reads the number six months from now. Overridden cells carry a yellow corner indicator. That marker is the only thing separating a value you typed from a value Francis imported, so scan for it first whenever your model doesn’t tie to your accounting system. Two consequences matter:
  • The override survives every sync. Syncing imports new journal entries, but it does not reclaim a cell you’ve written to. Entries booked to the mapped accounts after you set the override change nothing in the model until you remove it. Inspect keeps listing those entries, because it reads the accounts mapped to the row rather than the value in the cell. An override is the usual explanation when an entry is visible in Inspect but the row total ignores it.
  • Everything downstream reads the override. Groups, calculations, and any formula referencing the row all take the overridden value, not the imported one. A single override propagates up the statement, so a small correction in one cell can move a subtotal you weren’t looking at.
Remove the override to hand the cell back to your data source. The imported value returns and every dependent total recomputes. Overrides are per layer. An override in the actuals layer leaves the forecast layer untouched, and the same holds in reverse. See Timeline and dates for how the two layers relate.

​
Formula paths

A row name alone is enough when it is unique in the model. When the same row name appears in multiple sheets, groups, or breakdowns, Francis qualifies the reference with a formula path. The path keeps every cell reference unique even when row names repeat across entities, dimensions, or sections. Two separators can appear in the path:
  • Sheet (!): the sheet name sits before ! when the row lives on a different sheet.
  • Container (.): a group or section name sits before . when the row lives inside a nested container.
Example:
This formula references two rows:
  • "Cost VAT"[-1]: the "Cost VAT" row on the current sheet, previous period.
  • "VAT"!"VAT calculations"."Cost VAT addition"[0]: the "Cost VAT addition" row inside the "VAT calculations" group on the "VAT" sheet, current period.
Francis builds the path automatically when you click a target cell in edit mode, so you rarely need to type one by hand.

​
Comments

Add # at the end of any formula to insert a comment. Everything after # is highlighted and ignored by the calculation.
Use comments to document non-obvious logic: a sign flip, a hardcoded assumption, a workaround for a specific edge case. A reader opening your model six months later will thank you.

​
Functions

Francis supports a set of built-in functions (avg(), sum(), if(), and others) for more complex logic.
Your number format setting in Francis determines whether to use commas (US) or semicolons (EU) to separate function parameters. All examples below use the US format with commas.
Average of values across a period range.
Average from the start of the fiscal year to the current month.
Average across all months in the previous fiscal year.
Average over the last n months.
Median of values across a period range.
Median from the start of the fiscal year to the current month.
Median across all months in the previous fiscal year.
Median over the last n months.
Sum of values across a period range.
Sum from the start of the fiscal year to the current month.
Sum across all months in the previous fiscal year.
Sum over the last n months.
Accumulates revenue from prior months based on a payment days assumption. Assumes 30 days per month. The payment_days argument must be hardcoded and cannot reference a row.
Returns zero instead of a #DIV/0 error. Use for margin and percentage calculations where the denominator may occasionally be zero.
Returns num raised to the power of exponent.
Returns the smaller of two values.
Returns the larger of two values.
Returns the absolute value of a number.
Rounds to the nearest integer. Optionally provide a number of decimal places.
Rounds down to the nearest integer.
Rounds up to the nearest integer.
Conditional calculation, similar to IF in Excel or Google Sheets.
Returns 1 if the current period matches any of the specified month numbers, else 0.
  • if_month(1) returns 1 in January
  • if_month(1, 6) returns 1 in January or June
  • if_month(1, 6, 12) returns 1 in January, June, or December
Returns 1 if the current period is the nth month within its quarter.
  • if_quarter_month(1) returns 1 in January, April, July, October
  • if_quarter_month(2) returns 1 in February, May, August, November
  • if_quarter_month(3) returns 1 in March, June, September, December
Returns the number of weekdays (Monday through Friday) in the current month.

​
Errors

Occurs when a formula attempts to divide by zero. Use ignore_div_zero() to return zero instead.
Occurs when a row using weighted average (W.AVG) aggregation contains a formula that is not a simple fraction (a/b).
Indicates invalid syntax or an incomplete formula. For example, entering 5+ throws #ERR because the expression is incomplete.
Triggered when a calculation references itself, creating an infinite loop. Calculations apply the same formula across all periods, so self-references are not supported.
Caused by a circular dependency between cells, such as two rows that reference each other, or a chain of references that loops back to the start.
Occurs when a formula references a future period. Francis only supports references to the current period or earlier.
Any period index greater than [0] throws this error. "Revenue"[1] is invalid; "Revenue"[0] and "Revenue"[-1] are valid.
Occurs when a formula references a row that no longer exists, typically because it was deleted.
Occurs when an argument passed to a function does not match the expected type, such as passing a boolean where a number is expected.
Occurs when an argument is of an incompatible type, such as passing a number where a range is expected.
Returned when a formula references a cell that itself contains an error. Resolve the error in the source cell first.

​
See in action

See formulas in practice in the Forecasting approaches masterclass.