Modeling
Functions
A complete overview of all supported functions, including example use cases.
Your number format in Francis determines whether you’ll use commas (US) or semicolons (EU) to separate function parameters. All examples below use the US format with commas.
avg("row_name"[range_start:range_end])
avg("row_name"[range_start:range_end])
The
avg() function calculates the average of the values in the specified time series from range_start to range_end.avg_ytd("row_name")
avg_ytd("row_name")
The
avg_ytd() function returns the average of the values in a time series from the beginning of the fiscal year until and including the current month.avg_last_year("row_name")
avg_last_year("row_name")
The
avg_last_year() function returns the average of the values in the previous fiscal year. This function is influenced by your fiscal year settings.avg_last("row_name",num_months)
avg_last("row_name",num_months)
The
avg_last() function returns the average of the values in the time series going back n months, as defined by the num_months parameter.median("row_name"[range_start:range_end])
median("row_name"[range_start:range_end])
The
median() function returns the median of the values in the specified time series from range_start to range_endmedian_ytd("row_name")
median_ytd("row_name")
The
median_ytd() function returns the median of the values in a time series from the beginning of the fiscal year until and including the current month.median_last_year("row_name")
median_last_year("row_name")
The
median_last_year() function returns the median of the values in the previous fiscal year. This function is influenced by your fiscal year settings.median_last("row_name", num_months)
median_last("row_name", num_months)
The
median_last() function returns the median of the values in the time series going back X months, as defined by the num_months parameter.sum("row_name"[range_start:range_end])
sum("row_name"[range_start:range_end])
The
sum() function calculates the total of the values in the specified time series from range_start to range_end.sum_ytd("row_name")
sum_ytd("row_name")
The
sum_ytd() function returns the sum of the values in a time series from the beginning of the fiscal year until and including the current month.sum_last_year("row_name")
sum_last_year("row_name")
The
sum_last_year() function returns the sum of the values in the previous fiscal year. This function is influenced by your fiscal year settings.sum_last("row_name", num_month)
sum_last("row_name", num_month)
The
sum_last() function returns the sum of the values in the time series going back X months, as defined by the num_months parameter.receivables("row_name", payment_days)
receivables("row_name", payment_days)
The
receivables() function accumulates revenue amounts from previous months based on an assumption of payment days. Based on the number of payment days, the function loops back through previous months to collect revenue as part of receivables.The function assumes 30 days per month. The payment days input must be hardcoded and cannot reference a cell.ignore_div_zero(a/b)
ignore_div_zero(a/b)
The
ignore_div_zero() returns a zero if the function’s output is a #DIV!/0 error. The function is used for margin and percentage calculations, where the denominator is occasionally zero.power(num, exponent)
power(num, exponent)
The
power() function is a mathematical function that returns the value of the parameter num to the power of the exponent parameter.min(a,b)
min(a,b)
The
min() function returns the smallest numeric value of the input values.max(a,b)
max(a,b)
The
max() function returns the largest numeric value of the input values.abs(num)
abs(num)
The
abs() function returns the absolute value of a number.round(num, decimals)
round(num, decimals)
The
round() function rounds the number value up or down to the nearest integer. Optionally, provide desired number of decimals.round_down(num)
round_down(num)
The
round_down() function rounds numbers down to the nearest integer.round_up(num)
round_up(num)
The
round_up() function rounds numbers up to the nearest integer.if(condition, value_if_true, value_if_false)
if(condition, value_if_true, value_if_false)
The
if() function allows you to create a conditional calculation, similar to what is available in Excel or Google Sheets.if_month(a,b)
if_month(a,b)
The
if_month function returns 1 if the time period is equal to the month number input in the formula, else 0.if_month(1)returns1if the month is January, else0if_month(1, 6)returns1if the month is January or June, else0if_month(1,6 , 12)returns1if the month is January, June, or December, else0
if_quarter_month(num)
if_quarter_month(num)
The
if_quarter_month function returns 1 if the time period is equal to the month number inside a given quarter. E.g., you can input [1;3] into the formula.if_quarter_month(1)returns1if the month is January, April, July, or Octoberif_quarter_month(2)returns1if the month is February, May, August, or Novemberif_quarter_month(3)returns1if the month is March, June, September, or December
weekdays()
weekdays()
The
weekdays() functions returns the numbers of weekdays (Monday through Friday) in a given month.