When you use a specific variable, the formula affects all related cells in the spreadsheet. Unlike regular spreadsheet tools, you can't change the formula for just one cell in Farseer. If you edit it in one cell, those changes automatically apply to all other cells connected to the same variable.

Variables can hold formulas, which consist of other variables. For example, you can have three variables: QUANTITY,PRICE and REVENUE .

Here's an example for calculating revenue, on the REVENUE variable:

REVENUE = QUANTITY * PRICE

You can also use this, at the same time, on the QUANTITY variable:

QUANTITY=REVENUE / PRICE

While writing a formula, you can use integrated functions in Farseer:

REVENUE = round(QUANTITY, 0) * PRICE

Below you'll find all integrated functions available for use in formulas.

Syntax: delay (variable or formula, number of months)

Number of months can be a positive or negative number. It can also be another variable or formula.

Examples:

Here the quantity is delayed by 0, -1, and 2 months.

Be careful:

The „Month“ dimension must be present in the variable or formula.

You can also use this formula for calculating the moving average:

Syntax: spread (variable or formula, number of months, delay)

The delay argument is optional and defaults to zero if not used.

Example:

Spread the CAPEX value over 4 and 12 months.

Be careful:

The „Month“ dimension must be present in the variable or formula.

Spread the CAPEX value over 4 months with 3-month delay.

Syntax: floor (variable or formula)

The function behaves like ROUNDDOWN in Excel, rounding the decimals to closest lower number.

Example:

Syntax: sum (variable or formula, dimensions)

This behaves like SUMIF / SUMIFS functions in Excel. You can define which variable will be summed up and define the criteria (dimensions) by which this sum will be performed / sliced.

*note that Farseer uses „>“ instead of „=“ in formulas (as the case is with Excel or Sheets). This can be interpreted as an arrow which points from a certain dimension to dimension member by which we want to aggregate the variable.

Example:

Sum with single condition (SUMIF).

Sum with multiple conditions (SUMIFS).

Syntax: avg (variable or formula, number of months, skip_nulls)

The number of months can be expressed as a constant (number) or formula defining number of months. Argument skip_nulls is either 0 or 1, which defines whether the function takes empty cells (nulls) into account or not.

By default, Farseer shows the result in the Month +1 cell:

You can offset this using the delay() function:

Syntax:

max(variable/formula/number,variable/formula/number),

min(variable/formula/number, variable/formula/number)

These functions return the greater or smaller value between two compared numbers (variables or entries). They behave like MAX() and MIN() in Excel.

Example:

Syntax: abs (variable / formula / number)

The formula behaves like the ABS () function in Excel, returning the positive (absolute) value of the expression.

Example:

Syntax: round (variable or formula, number)

This function behaves like ROUND () in Excel, where the number defines the decimal places. If a negative number is used, the formula rounds the number to the nearest one.

Example:

Syntax:

eq (variable / formula / number, variable / formula / number)

gt (variable / formula / number, variable / formula / number)

gte (variable / formula / number, variable / formula / number)

lt (variable / formula / number, variable / formula / number)

lte (variable / formula / number, variable / formula / number)

Equal eq() behaves like EXACT() in Excel, comparing if two values are the same. It is useful when used with the if() function.

The same applies to other functions, where gt() translates to “greater than” and gte() to “greater than or equal”. The same logic of translation applies to lt() and lte() as shown in the figure below.

Examples:

You can combine these functions with if() to get more meaningful results:

In the above example, the formula translates to: if Random variable 1 is greater than Random variable 2, show Random variable 1.

Syntax: cumsum (variable / formula, factor, “reset” variable)

Be careful:

This function does not work on measures (variables without dimensions).

Cumsum() is mostly used for calculating interest, YTD calculations, or cumulative variations. If the argument reset variable is not used, the function continues indefinitely.

Examples:

In this example, cumsum() will continue to increase the result by 2,5% indefinitely. To prevent continuous growth, we can use another variable as a third argument in the expression.

The result of using this variable (Result reset) in cumsum() formula function as third argument:

Syntax:

hasTags (dimension)

hasTags (or(dimension1,dimension2))

hasValue (variable1)

Both functions are commonly used with the if() function. They can be compared to defining criteria in SUMIF(S) functions in Excel.

Example – hasTags

Most common use of hasTags() function is using it to bring Actuals and Plan versions on the same variable. Actuals are in most cases imported from ERP or similar, and that variable is read-only. To be able to enter the plan on the same level, we need to create another variable which takes import for Actual version. You don’t have to specify the dimension table.

Example – hasValue:

hasValue() uses Variable instead of Dimension in if() formulas.

Be careful: all date functions need to have system dimensions of Years and Months to work properly.

currentMonth()

Gets current month timestamp, usually is used in combination with if() and comparison functions.

currentDate()

Gets current date timestamp, usually is used in combination with if() and comparison functions.

daysInCurrentMonth()

This function counts the number of days in each month. It can be used with if() and comparison functions, as in this example:

If (number of days in month) is (less than) 31, multiply Quantity by 2, else Quantity

Note: If not used for comparison but for calculating the number of days in each month, it must be used with a “dummy” variable to which we declared a 1 or 0. Then we get the desired number in the “Days in current month” variable by adding or multiplying the dummy with the daysInCurrentMonth() function (depending on whether we used 1 or 0 for the dummy variable).

date(year,month,day)

Creates a date formatted cell from inputs, Syntax: date(2014,3,15)

edate(date,number of months)

Returns a date, based on some entered or referenced date and number of months you want to add or subtract from that date.

day(date), month(date), year(date)

Extracts day, month or year from a date, correspond to same Excel functions

Be careful: all growth functions need to have system dimensions of Years, Quarters, or Months to work properly.

Syntax:

mom(percentage) – month on month

qoq(percentage) – quarter on quarter

yoy(percentage) – year on year

All three functions use an input for starting point, and percentage as scaling factor. Percentage can be another variable, or number input. Both positive and negative values can be used.

Example:

For “New Revenue” variable, we have entered 500k as a starting point, and % for 2025-2027 as a separate variable. (Hardcoded number can also be used)

Syntax:

power(x,z) – where “x” is a base for calculation, and “z” is power

Corresponds / behaves like POWER() in Excel.

Example:

“Power base” and “Z for Power” are inputs, “Power result” is calculated variable

If() function behaves and corresponds to Excel IF() function.

Additional functions are or(), and(), and not(), which can also be used for filtering dimensions in combination with the if() function.

Syntax:

if(logical test, true result, false result) - false result is not required in function

or(dimension 1, dimension 2)

and(dimension 1, dimension 2)

not(dimension)

if() function has already been used throughout the document:

Another example:

If Variable “COGS minus Revenue” is negative (less than 0), multiply it with -1

Examples of or() and not() functions

You can also use or(), and(), and not() functions for compound dimensions.

Create new dimension called “East or Adriatic”, right click, and click on “Edit dimension member”

In formula bar, enter or() function, same as in example:

Result: