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: 
="Revenue"[-1]
A driver-based cost: 
="Headcount"[0] * "Average salary"[0]
A balance sheet movement (last period + addition - detraction): 
="Inventory"[-1] + "Inventory purchases"[0] - "COGS"[0]
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: 
="Revenue"[0] + "Direct costs"[0]
Not this: 
="Revenue"[0] - "Direct costs"[0]
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.

​
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: 
= "Cost VAT"[-1] + "VAT"!"VAT calculations"."Cost VAT addition"[0]
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. 
=if("Revenue"[0] = 0, 0, "Gross profit"[0] / "Revenue"[0]) # Returns zero if revenue is zero to avoid dividing by zero
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.
avg("Revenue"[-3:0])
Average from the start of the fiscal year to the current month.
avg_ytd("Revenue")
Average across all months in the previous fiscal year.
avg_last_year("Revenue")
Average over the last n months.
avg_last("Revenue", 6)
Median of values across a period range.
median("Revenue"[-3:0])
Median from the start of the fiscal year to the current month.
median_ytd("Revenue")
Median across all months in the previous fiscal year.
median_last_year("Revenue")
Median over the last n months.
median_last("Revenue", 6)
Sum of values across a period range.
sum("Revenue"[-3:0])
Sum from the start of the fiscal year to the current month.
sum_ytd("Revenue")
Sum across all months in the previous fiscal year.
sum_last_year("Revenue")
Sum over the last n months.
sum_last("Revenue", 6)
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.
receivables("Revenue", 30)
Returns zero instead of a #DIV/0 error. Use for margin and percentage calculations where the denominator may occasionally be zero.
ignore_div_zero("Direct costs"[0] / "Revenue"[0])
Returns num raised to the power of exponent.
power(10, 2) // returns 100
Returns the smaller of two values.
min(5, 10) // returns 5
Returns the larger of two values.
max(5, 10) // returns 10
Returns the absolute value of a number.
abs(-10) // returns 10
Rounds to the nearest integer. Optionally provide a number of decimal places.
round(10.4386) // returns 10
Rounds down to the nearest integer.
round_down(10.6) // returns 10
Rounds up to the nearest integer.
round_up(10.6) // returns 11
Conditional calculation, similar to IF in Excel or Google Sheets.
if("Revenue"[0] = 0, 0, "Gross profit"[0] / "Revenue"[0])
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.
weekdays() // January 2025 returns 23

​
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.