Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Reference: Formula Functions

Provides detailed syntax on the functions you can use to create formulas including Mathematical, Logical, Date and Time, and String.

Formula functions are grouped into four categories: mathematical, logical, string and date. Each function and a brief explanation are defined in this topic.

Click Help  in the formula assistant to view available functions.

Mathematical Functions

Basic mathematical functions are available as buttons. For example plus (=), minus (-), and multiply (*). Additional selections are available from Function > Mathematical:

For simplicity, the Examples in this section show formulas with actual numbers, but you can create mathematical functions with any Account Reference or Modifier that has a numeric value. For example, Div(ACCT.CostOfGoodsSold,ACCT.Inventory).

Syntax Description
%

The modulo operation (%) calculates the whole number remainder that occurs when one number is divided by another.
Examples: 7%2 is 1, 8%3 is 2, and 9%3 is 0

Div (N,D)

Divides the numerator (N) by the denominator (D) and returns the result. If the denominator is zero, returns zero (instead of undefined). 
Examples: Div (6,2) is 3, Div (4,0) is 0.

You can use the divide symbol (/) instead of the Div operator, but it's not recommended. The Div  operator prevents formula errors that can occur when the denominator is zero.

Divf (N,D)

The "fast" or short-circuiting form of the Div function. Returns the same value as Div. However, if the denominator is zero, the numerator is not evaluated, making this function perform faster than the standard Div. However, the numerator could have a circular loop or other internal error but the result will still be valid (and zero) if the denominator is zero.

Round (N)

Rounds the number (N) up or down to the nearest whole number.
Examples: Round (7.8) is 8, Round (2.1) is 2.

Trunc (N)

Truncates the number at the decimal point.
Example: Trunc (3.14) is 3.

Floor (N)

Returns the integer (whole number) that is closest to, but less than or equal to N. This is equivalent to Trunc () for positive numbers.  
Example:  Floor(1.897) is 1.

Ceil (N)

Returns the integer (whole number) that is closest to, but greater than or equal to N

Example: Ceil(1.897) is 2.

Power (N,E) Returns the number (N) raised to the power of the exponent (E).
Example: Power (2,4) is 16.
Ln(N)

Returns the natural logarithm (Ln) of the number (N).
Example: Ln (2) is 0.69 (rounded to two decimal places)

Greatest (N1, N2, ...)

Returns the largest number in the list (N1,N2...).
Example: Greatest (75, 32, 18, 24) is 75.

Least (N1, N2, ...)

Returns the smallest number in the list (N1,N2...).
Example: Least (75, 32, 18, 24) is 18.

Spread445 (N, M)

Returns the portion of the number (N) which, when spread over a number of months (M) according to the
445 rule, belongs to the current month. If M is omitted, then 12 is used.

Examples:

  • Spread445 (1300,12) in January returns 100, the result of (1300/52)*4
  • Spread445 (1300,12) in March returns 125, the result of (1300/52)*5
Spread454 (N, M)

Returns the portion of the number (N) which, when spread over a number of months (M) according to the
454 rule, belongs to the current month. If M is omitted, then 12 is used.

Examples:

  • Spread454 (1300,12) in January returns 100, the result of (1300/52)*4
  • Spread454 (1300,12) in February returns 125, the result of (1300/52)*5
  • Spread454 (1300,12) in March returns 100, the result of (1300/52)*4
Spread544 (N, M)

Returns the portion of the number (N) which, when spread over a number of months (M) according to the
544 rule, belongs to the current month. If M is omitted, then 12 is used.

Examples:

  • Spread544 (1300,12) in January returns 125, the result of (1300/52)*5
  • Spread544 (1300,12) in March returns 100, the result of (13000/52)*4
Npv (discount_rate, value1, value2, ..., valueN)

Returns the net present value of a series of cash flows at the specified discount rate.
Time ranges of accounts may be used as sets of values. 

Example: 
If the formula is: NPV(0.1,ACCT.CF[time=this:this+11]

The ACCT.CF formula reference will be interpreted as being 12 distinct values instead of a single rolled-up value. This is equivalent to entering: NPV(0.1, ACCT.CF[time=this], ACCT.CF[time=this+1], ..., ACCT.CF[time=this+12]).

Irr (value1, value2, ..., valueN, estimated_irr)

Estimates the internal rate of return for a series of cash flows. Time ranges of accounts
may be used as sets of values. If the function is given only a single argument, that argument is assumed to be a time range and an assumed estimated Irr of 0.10 is used.

This function uses a trial and error algorithm which is accurate provided the outflow takes place in one time period (usually the first period). This function will try up to 20 iterations to get close to the true value of the internal rate of return.

Abs (N)

Returns the absolute value of the number (N).
Examples: Abs (2) is 2, Abs (-2) is 2.

Sqrt (N)

Returns the square root of the number (N).
Example: Sqrt (25) is 5.

Logical Functions

To include as part of an If statement, basic logic functions and comparisons are available as buttons. For example, logical and (AND), logical or (OR), and logical negation (NOT). Comparisons include equal to (=), greater than (>), less than (<), or not equal to (!=). You can also use (<>) for not equal. 

Additional selections are available from Function > Logical:

Syntax Description
If (EXPR, T, F) Returns a numeric value (T) if Boolean expression (EXPR) is true, otherwise returns a numeric value (F). You can use Boolean and comparison operators to construct the Boolean expression.  
Iff (EXPR, T, F)

The "fast" or short-circuiting form of the If function. Returns the same value as If. However, if the condition is true, the third argument to the function is not evaluated. Similarly, if the condition is false, the second argument is not evaluated. This means that either the second or third argument could potentially contain errors but the results of Iff will still be valid because the argument that includes the error is not evaluated. 

Examples: 

  • IFF (this.Version.isActuals, ACCT.TravelExp, ACCT.Personnel.EstimatedTravel)
  • IFF (this.Quarter.PositionOf (this.Month) = 2, 100000, 0)
IsBlank (N) Returns true if the argument is blank or empty, false otherwise. Can only be used in an expression argument (EXPR) to the If function.
Example: If(IsBlank(Row.StartDate),0,1)
Error () Causes a run-time evaluation error. 
Switch (Orig_EXPR, Case1, Case2, ..., Default)

Provides a method for writing compact expressions that can be used instead of complex, nested If statements. 

  • The first argument (Orig_EXPR) is the original expression (sometimes called the control expression) that is evaluated first.
  • The subsequent arguments are Cases(values and corresponding expressions) that can be activated depending on which case matches the value of the original expression.
  • You can define one or more cases. Each case consists of a value (N) and an expression (EXPR) separated by a comma. The expanded syntax looks like this: Switch (Orig_EXPR, N1, EXPR1, N2, EXPR2, ..., Default)
  • For example, if the value of the original expression matches the value defined in Case1, the expression associated with Case1 is returned.  But, if the value of the original expression matches Case2, the expression associated with Case2 is returned, and so on. 
  • If there is no match, Switch returns the Default expression. 
  • All expressions (EXPR) in the Switch statement must be the same data type (string, date, number, etc.). If the expressions vary in data type, this error appears: Inconsistent Switch return types
  • Instead of a single value (N) for each case, you can specify a Range(L,U) of values where L is the lower bound of the range and U is the upper bound of the range. For example, a range might include a number of years Range(2014,2017) and when the value of the original expression falls within that range, inclusive, the expression associated with that range is activated. 
  • Each argument to a range must be the same data type (for example, a number like 7, a string constant like Engineering or a date constant built using the existing Date() function or an expression which resolves to the proper data type (e.g. this.Version.Name or ACCT.Rent).

Examples: 

  • Switch (ROW.DAY,1,"Sunday",2,"Monday",3,"Tuesday","No match")
    If ROW.DAY is 2, then Monday is the expression that corresponds to the value 2, so the Switch result is Monday. 
  • Switch (ToNumber (ROW.CaseQty),
    Range (0,11),  0,
    Range (12,24), 1,
    Range (24,48), 4,
                   0)

    This second example returns 0, 1, 2, or 4 depending on the numeric value found in the CaseQty selector. 

Date and Time Functions

Date functions are often used in combination with modeled sheet Date columns. Date functions only display in modeled sheet calculated accounts.

Syntax Description
ToDate (Y, M, D) Returns the date specified by the given year (Y), month (M), and day (D). If D is omitted, 15 is used. The value returned may be used as an argument in other functions which take a date.
(Date).stratum_code

Returns the Timeperiod of the requested strata type which contains the (single, Gregorian) Date. Any date can be used as the base of the stratum code. Example: ToDate(2016,8,5).Qtr returns the fiscal calendar quarter that contains Aug 5 2016.

Day (D) Returns the day of the month for date (D).
Month (D) Returns the month in which date (D) occurs.
Quarter (D) Returns the calendar quarter in which date (D) occurs. This may not be the same as your company's fiscal quarter (see below).
Year (D)  Returns the year in which date (D) occurs regardless of whether a custom calendar is used or not.
FiscalMonth (D)

Returns the month of the fiscal year in which date (D) occurs.

For instances configured with a custom calendar, FiscalMonth (D) may return an error. Use D.Year.PositionOf(D.Month). instead.

FiscalQuarter (D)

Returns the quarter of the fiscal year in which date (D) occurs.

For instances configured with a custom calendar, FiscalQuarter (D) may return an error. Use D.Year.PositionOf(D.Qtr). instead.

FiscalYear (D)

Returns the fiscal year in which date (D) occurs.

For instances configured with a custom calendar, FiscalYear may return an error. Use ToNumber(D.Year.code). instead.

VersionMonth (D)

Returns a number that represents the month of the version in which the date (D) occurs (first month = 0).  This number can be negative if the date (D) is earlier than the start of the version.

For instances configured with a custom calendar, versionMonth may return an error. Use this.Version.PositionOf(D.Month) instead.

VersionYear (D)

Returns a number that represents the year of the version in which the date (D) occurs (first year = 0).  This number can be negative if the date (D) is earlier than the start of the version.

For instances configured with a custom calendar versionYear may return an error. Use this.Version.PositionOf(D.Year) instead.

Version.PositionOf (timeperiod) Returns the relative order of the provided time period within the entire span of the version. The initial sequence starts with 0 (not 1) and the order number can be negative if the time period occurs before the start of the version. This function also works with a time period that is beyond the end of the version.
DaysInMonth (D) Returns the number of days in the month in which date (D) occurs. For instances configured with a custom calendar, use D.Month.NumberOfDays.
MonthFraction (D1, D2, D) Returns the portion of the current month which occurs between the start date (D1) and the end date (D2). If an optional date (D) is specified, uses the month in which D occurs, instead of the current month. For instances configured with a custom calendar, use TimeFraction (D1, D2, D).

String Functions

Syntax Description
Concat (text1, text2, ...)

Returns a concatenated string by joining two or more text strings. 
Example: Concat ("hello", "world") returns "helloworld".

Length (text)

Returns the number of characters in a text string.
Example: Length ("Now, is the winter of our discontent. Made glorious summer by this son or York.") returns 79.

Search (source_text, search_text)

Returns the starting position of search_text in source_text. Returns 0 if search_text is not found. Search is not case-sensitive. 
Examples: 
Search ("Now is the winter of our discontent.", "Winter") returns 12.
Search ("Now is the winter of our discontent.", "Summer") returns 0.

Substring (source_text, start_position, length)

Returns a text string containing the characters found in source_text starting at start_position (where position 1 is the first character of the text string) and running for length (or up to the end of the text string, whichever is encountered first).

Selecting a start_position of 0 or less is treated as though it were position 1. Selecting a length of 0 or less is treated as a length of 0 and returns an empty string. Selecting a start_position which is after the end of source_text returns an empty string. 

ToNumber (text)

Converts a text string representing a number to its numeric value. Will convert the leading part of the string until a non-numeric character is found.
Examples:
ToNumber ("7") returns 7.
ToNumber ("2017-12-31") returns 2017 (a number).
ToNumber ("Sample) returns O.

Examples

The following is a Headcount formula from a Personnel modeled sheet. It is a good example of how functions can be combined. This formula returns a binary result: 1 or 0.

If(this.Version.PositionOf(this.Month) >= this.Version.PositionOf(ROW.StartDate.Month)   # if this month is after the person's start date
   and (isblank(ROW.EndDate)                                                             # and either they have no termination date
      or this.Version.PositionOf(this.Month) <= Version.PositionOf(ROW.EndDate.Month)),  # or the current month is before termination date
   1, # return 1 for their headcount in this month
   0) # otherwise, return 0