Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2023-06-23
Reference: Formula Functions

Reference: Formula Functions

Formula functions are grouped into four categories: mathematical, logical, string and date. Each function and a brief explanation are defined in this topic.
For simplicity, the descriptions in the table show formulas using comma separators. Some browser settings require you to use semicolon separators. The syntax columns shows both versions.

Mathematical Functions

Basic mathematical functions are available as buttons. For example plus (=), minus (-), and multiply (*). Additional selections are available from
Function > Mathematical
:
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
.
Abs (N)
Returns the absolute value of the number (
N
).
Examples
:
Abs (2)
 is
2
,
Abs (-2)
 is
2
.
Ceil (N)
Returns the integer (whole number) that is closest to, but greater than or equal to
N
.
Example:
Ceil(1.897)
 is
2
.
ColumnMax (N)
Returns the maximum value of a numeric modeled column N, for the current level. Only available in the formula assistant when editing and creating modeled sheet calculated accounts.
Example:
ColumnMax (ROW.PayRate)
ColumnMin (N)
Returns the minimum value of a numeric modeled column N, for the current level. Only available in the formula assistant when editing and creating modeled sheet calculated accounts.
Example:
ColumnMin (ROW.PayRate)
Div (N,D)
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)
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.
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
.
Greatest (N1, N2, ...)
Greatest (N1; N2; ...)
Returns the largest number in the list (
N1,N2
...).
Example:
Greatest (75, 32, 18, 24)
 is
75
.
Irr (value1, value2, ..., valueN, estimated_irr)
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 600 iterations to get close to the true value of the internal rate of return.
Least (N1, N2, ...)
Least (N1; N2; ...)
Returns the smallest number in the list (
N1,N2
...).
Example:
Least (75, 32, 18, 24)
 is
18
.
Ln(N)
Returns the natural logarithm (
Ln
) of the number (
N
).
Example:
 Ln (2) is
0.69
 (rounded to two decimal places)
Npv (discount_rate, value1, value2, ..., valueN)
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])
.
Power (N,E)
Power (N;E)
Returns the number (
N
) raised to the power of the exponent (
E
).
Example:
Power (2,4)
 is
16
.
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
.
Spread445 (N, M)
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)
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)
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
    (1300/52)*4
Sqrt (N)
Returns the square root of the number (
N
).
Example:
Sqrt (25)
 is
5
.
Trunc (N)
Truncates the number at the decimal point.
Example:
Trunc (3.14)
 is
3
.

Math Functions For Trigonometry

Syntax
Description
Acos (N)
Returns the arccosine of a number N, where N is the cosine of an angle.
Example:
Acos (-0.5)
 is
2.094395102
Asin (N)
Returns the arcsine of a number N, where N is the sine of an angle.
Example:
Asin (-0.5)
 is
-0.523598776
Cos (N)
Returns the cosine of an angle N, where N is in radians.
Examples:
Cos (Radians(60))
 is
.5
Radians (N)
Returns the radians of a number N, where N is an angle in degrees.
Example:
Radians (270)
 is
4.712389
Sin (N)
Returns the sine of an angle N, where N is in radians.
Example:
Sin (Radians(30))
 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
Blank()
Returns the
Blank
 value. This value displays as blank (rather than a zero), but evaluates identically to zero for formula purposes. Unlike a zero, it causes the
IsBlank()
 function to return
TRUE
, but similar to a zero it is able to be suppressed when "suppress empty rows" is enabled on sheets or reports.
Error ()
Causes a run-time evaluation error.
Example:
IF(ACCT.EffectiveHeadcount < 0, Error(), ACCT.EffectiveHeadcount+ACCT.AddlHeadcount)
If the effective headcount is less than 0, return an error to help track down the underlying problem.
If (EXPR, T, F)
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)
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.Qtr.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)
Switch (Orig_EXPR, Case1, Case2, ..., Default)
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 returns0, 1, or4 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
D.Month.NumberOfDays
Returns the number of days in the month in which date
D
 occurs
.
D.Year.PositionOf(D.Month).
Returns the month of the fiscal year in which date
D
 occurs.
D.Year.PositionOf(D.Qtr)
Returns the quarter of the fiscal year in which date
D
 occurs.
(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
).
DaysInMonth (D)
Returns the number of days in a month in which date D occurs. May return an error or an incorrect value for custom calendars. Use
D.Month.NumberOfDays
 instead.
FiscalMonth (D)
Returns the month of the fiscal year in which date (
D
) occurs. May return an error for custom calendars. Use
D.Year.PositionOf(D.Month).
FiscalQuarter (D)
Returns the quarter of the fiscal year in which date (
D
) occurs. May return an error for custom calendars. Use
D.Year.PositionOf(D.Qtr).
FiscalYear (D)
Returns the fiscal year in which date (
D
) occurs. May return an error for custom calendars. Try coding your years with numbers that exactly match the year and use
ToNumber
(D.Year.code).
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).
this.Version.PositionOf(D.Month)
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.
this.Version.PositionOf(D.Year)
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.
TimeFraction (D1, D2, D, Dx)
TimeFraction (D1; D2; D; Dx)
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. If the optional fixed number of days parameter (
Dx
) is specified, override the time period in
D
 with the numeric value.
Instances configured without a custom calendar can use
MonthFraction
 instead of
TimeFraction
.
ToDate (Y, M, D)
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.
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.
May return an error for custom calendars. Use
this.Version.PositionOf(D.Month).
Version.PositionOf (timeperiod)
Returns the relative order of the provided time period within the entire span of the version. The first time period in a version starts with 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.
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.
May return an error for custom calendars. Use
this.Version.PositionOf(D.Year)
.
Year (D)
Returns the year in which date (
D
) occurs regardless of whether a custom calendar is used or not.

String Functions

Syntax
Description
Concat (text1, text2, ...)
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)
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)
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 0.

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