Formulas in Farseer
In Farseer, formulas are tied to variables, not individual cells. When you set a formula on a variable, it applies to all dimensional intersections of that variable. If you edit the formula, the change automatically propagates everywhere.
Example: Three variables — QUANTITY, PRICE, and REVENUE:
REVENUE = QUANTITY * PRICE
You can also reverse it: QUANTITY = REVENUE / PRICE
Formulas support integrated functions: REVENUE = round(QUANTITY, 0) * PRICE
Note: Farseer uses > instead of = in dimension references within formulas. Think of it as an arrow pointing from a dimension table to a specific member: Region>North America.
Math Functions
round
Rounds values to the specified number of decimal places. If no second argument is provided, defaults to 0 (rounds to nearest integer).
Syntax: round(expression, decimal_places)
Example: round(REVENUE, 2) — rounds to 2 decimal places
floor
Rounds down to the nearest integer.
Syntax: floor(expression)
Example: floor(PRICE) — 9.7 becomes 9
ceil
Rounds up to the nearest integer.
Syntax: ceil(expression)
Example: ceil(PRICE) — 9.2 becomes 10
abs
Returns the absolute (positive) value.
Syntax: abs(expression)
Example: abs(A - B) — always returns a positive result
power
Calculates the power of the base raised to the exponent.
Syntax: power(base, exponent)
Example: power(PRINCIPAL, 2) or power(sum(X, Y), Z)
max
Returns the larger of two values. Both arguments can be variables, formulas, or numbers.
Syntax: max(value1, value2)
Example: max(REVENUE, 0) — ensures the result is never negative
min
Returns the smaller of two values.
Syntax: min(value1, value2)
Example: min(BUDGET, ACTUAL)
Aggregation Functions
sum
Sums values of the expression where all specified dimension filters are intersected. Filters are combined with AND logic — every filter must match. Use the > operator to reference specific dimension members.
Syntax: sum(expression, filter1, filter2, ...)
Examples:
sum(REVENUE, Region>North America)— sum Revenue for North Americasum(REVENUE, Region>North America, TV SETS)— sum Revenue for North America AND TV SETS
avg
Calculates the moving average of an expression over the previous N months.
Syntax: avg(expression, number_of_months) or avg(expression, number_of_months, skip_nulls)
Arguments:
expression — the variable or formula to average
number_of_months — how many months to look back (can be a number or a variable). This argument cannot be a variable that has Months or Years as its dimensions.
skip_nulls (optional) — set to
1to exclude empty cells from the count. By default (0), null months count toward the divisor, which lowers the average.
Examples:
avg(REVENUE, 3)— 3-month moving average (nulls count as zero)avg(REVENUE, 3, 1)— 3-month average, skipping null monthsdelay(avg(REVENUE, 3), -1)— align moving average to the current month
cumsum
Calculates a cumulative sum over time. The calculation runs forward through each period in the timeline.
Syntax: cumsum(expression) or cumsum(expression, factor) or cumsum(expression, factor, reset_variable)
Does not work on measures (variables without time dimensions).
Three modes:
Simple cumulative sum:
cumsum(NEW SUBSCRIPTIONS - CHURN)Running total: each period adds its value to the accumulated result. Useful for subscriber counts, cash balances, etc.
With growth factor:
cumsum(BASE, INTEREST RATE)Each period's result = current value + previous accumulated result × (1 + factor). This creates compound growth, ideal for interest or loan balance calculations.
Example with factor 0.1: Values of 10 each period → 10, 21, 33.1, 46.41... (compound accumulation)
With reset state:
cumsum(BASE, FACTOR, STATE VARIABLE)The third argument provides a variable that can override and reset the accumulated total. When the state variable has a value, the accumulation restarts from that value. Useful for resetting cumulation at year boundaries or restoring state from another variable.
Example:
cumsum(0, 0, OPENING BALANCE)— carry forward a state value from another variable
Time-Shift Functions
delay
Shifts values forward in time by the specified number of months.
Syntax: delay(expression, number_of_months)
Requires: System dimension Months.
Fractional delays: If the delay is not a whole number, the value is proportionally split between adjacent months. For example, delay(X, 2.3) puts 70% of each value at +2 months and 30% at +3 months. The split is calculated as: floor portion = 1 − fraction.
Concrete example: Input values M1=1, M2=2, M3=3, M4=4 with delay(INPUT, 2):
M1: null, M2: null, M3: 1, M4: 2, M5: 3, M6: 4
Examples:
delay(QUANTITY, 3)— shift values forward 3 monthsdelay(avg(REVENUE, 3), -1)— moving average aligned to current mont
spread
Distributes a lump-sum value evenly across the specified number of future months. The optional third argument adds an initial delay before spreading begins.
Syntax: spread(expression, number_of_months, delay_months)
Requires: System dimension Months.
Concrete example: spread(INPUT, 3, 1) with Input M3 = 300:
M3: null (delay of 1), M4: 100, M5: 100, M6: 100 (300 spread over 3 months)
If another input arrives at M4 = 30, its spread overlaps: M5 becomes 100 + 10 = 110.
Use case: Depreciation — spread a capital investment over its useful life:
spread(CAPEX, 24, 0) — spread investment over 24 months starting immediately
spread_days
Works like spread() but uses days instead of months for both the spread period and the delay. Provides finer granularity — the function accounts for actual days in each month when distributing values to monthly cells.
Syntax: spread_days(expression, number_of_days, delay_in_days)
Example: spread_days(INPUT, 365, 15) — spread over 365 days with a 15-day delay
Growth Functions
Growth functions apply compound percentage scaling to each successive period from a manually entered starting value. You enter a value in one period, and the function fills subsequent periods automatically.
mom (Month-over-Month)
Fills future months by applying the percentage as compound growth to each successive month.
Syntax: mom(percentage)
Requires: System dimension Months.
Concrete example: mom(0.1) with a starting value of 100 entered in February:
Jan: null, Feb: 100, Mar: 110, Apr: 121, May: 133.1, ...
Each month = previous month × (1 + 0.1)
qoq (Quarter-over-Quarter)
Fills future periods by applying the percentage increase at quarterly intervals. Within each quarter, values remain constant; the increase applies when crossing a quarter boundary.
Syntax: qoq(percentage)
Requires: System dimensions Months or Quarters.
Example: qoq(0.10) — 10% quarter-over-quarter growth
yoy (Year-over-Year)
Fills future periods by applying the percentage increase at yearly intervals.
Syntax: yoy(percentage)
Requires: System dimensions Months, Quarters, or Years.
Example: yoy(0.20) — 20% year-over-year growth
predictAverage
Fills future periods with the average of all manually entered values on the same variable. Only cells where a user has explicitly typed in a value are included in the average — calculated or formula-generated values are excluded.
Syntax: predictAverage() — takes no arguments
Requires: System dimension Months.
Concrete example: You enter 200 in Feb, 300 in Mar, and 600 in Jun:
Apr = 250 (average of 200, 300)
May = 250 (average of 200, 300)
Jul onward = 366.67 (average of 200, 300, 600)
The average updates as new values are entered — earlier predicted cells recalculate
Year boundary: When crossing into a new year, the function uses all calculated values from the previous year as the baseline average for the new year.
Logic & Comparison Functions
if
Returns the true or false expression based on the condition. The false result is optional — if omitted and the condition is false, the cell returns null (empty).
Syntax: if(condition, true_result, false_result)
Valid conditions: Any comparison function (eq, gt, gte, lt, lte), hasValue, hasDMember, or dimension selectors (and, or, not).
Examples:
if(gt(REVENUE, COSTS), REVENUE - COSTS, 0)— profit if positive, else 0if(lt(currentDate(), CTRL DATE), VALUES)— show values only before control dateif(hasDMember(Actual), IMPORT VAR, PLAN VAR)— use import for Actual version, plan input for others
Comparison Functions
All comparison functions take exactly two arguments and return true (1) or false (0). They are typically used inside if():
Function | Meaning | Example |
| a equals b |
|
| a greater than b |
|
| a greater than or equal to b |
|
| a less than b |
|
| a less than or equal to b |
|
Null handling: eq(null, null) returns true. Other comparisons with null return false.
hasValue
Returns true (1) if the expression evaluates to a non-null value, false (0) if the cell is empty.
Syntax: hasValue(expression)
Example: if(hasValue(OVERRIDE), OVERRIDE, CALCULATED) — use override when present, fallback to calculated
hasDMember / hasTags
Checks if specific dimension members exist in the current cell's dimensional context. When multiple members are provided, they are combined with AND logic — all must be present.
Syntax: hasDMember(member1, member2, ...)
hasTags() is the legacy name for the same function. Both work identically. hasDMember is the preferred name going forward.
Most common use: Combining Actual and Plan versions on the same variable:
if(hasDMember(Actual), GL IMPORT, GL PLAN INPUT)
Note: You reference dimension members by name directly — no need to specify the dimension table.
Date Functions
Important: Date functions work with Excel-style serial numbers — the number of days since January 1, 1900. This is the same date format used by Excel and Google Sheets, which means dates can be compared, added, and subtracted as numbers.
Cell-Context Date Functions
These functions return information about the current cell's position in the timeline. They take no arguments.
Function | Returns | Example Use |
| Excel serial number for the 1st of the cell's month (e.g. Jan 2024 = 45292) |
|
| Month number (1–12) |
|
| Four-digit year (e.g. 2025) |
|
| Number of days (28–31, leap-year aware) |
|
Date Manipulation Functions
These functions create, modify, and extract components from date serial numbers.
Function | Description | Example |
| Creates a date serial number from year, month, and day |
|
| Adds months to a date, returns new serial number |
|
| Extracts four-digit year from a date serial number |
|
| Extracts month (1–12) from a date serial number |
|
| Extracts day (1–31) from a date serial number |
|
Tip: Combine date functions to create dynamic cutoffs: if(lt(currentDate(), date(2026, 1, 1)), FORECAST, ACTUAL)
Dimension Selector Functions
These functions are used to create compound dimensions — custom dimension members that combine or exclude other members.
Function | Description | Example |
| Matches if any listed members are present |
|
| Matches only if all listed members are present |
|
| Matches if the member is not present |
|
How to use: Create a new dimension member, right-click it, select "Edit dimension member", and enter the formula (e.g., or(East, Adriatic)) in the formula bar. The compound member will then aggregate data from all matching members.
These functions can also be used within if() formulas for filtering: if(or(Region A, Region B), REVENUE)