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.
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 ( 
Div (N,D) 
Divides the numerator ( You can use the divide symbol ( 
Divf (N,D) 
The "fast" or shortcircuiting form of the 
Round (N) 
Rounds the number ( 
Trunc (N) 
Truncates the number at the decimal point. 
Floor (N) 
Returns the integer (whole number) that is closest to, but less than or equal to 
Ceil (N) 
Returns the integer (whole number) that is closest to, but greater than or equal to Example: 
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 ( 
Greatest (N1, N2, ...) 
Returns the largest number in the list ( 
Least (N1, N2, ...) 
Returns the smallest number in the list ( 
Spread445 (N, M) 
Returns the portion of the number ( Examples:

Spread454 (N, M) 
Returns the portion of the number ( Examples:

Spread544 (N, M) 
Returns the portion of the number ( Examples:

Npv (discount_rate, value1, value2, ..., valueN) 
Returns the net present value of a series of cash flows at the specified discount rate. Example: The 
Irr (value1, value2, ..., valueN, estimated_irr) 
Estimates the internal rate of return for a series of cash flows. Time ranges of accounts 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 ( 
Sqrt (N) 
Returns the square root of the number ( 
ColumnMin (N) 
Only available with Adaptive Insights for Sales 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: 
ColumnMax (N) 
Only available with Adaptive Insights for Sales 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: 
Math Functions For Trigonometry
Only available with Adaptive Insights for Sales
Syntax  Description 

Sin (N) 
Returns the sine of an angle N, where N is in radians. Example: Sin (Radians(30)) is .5 
Cos (N) 
Returns the cosine of an angle N, where N is in radians. Examples: Cos (Radians(60)) is .5 
Asin (N) 
Returns the arcsine of a number N, where N is the sine of an angle. Example: Asin (0.5) is 0.523598776 
Acos (N) 
Returns the arccosine of a number N, where N is the cosine of an angle. Example: Acos (0.5) is 2.094395102 
Radians (N) 
Returns the radians of a number N, where N is an angle in degrees. Example: Radians (270) is 4.712389 
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 shortcircuiting form of the Examples:

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) 
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 runtime evaluation error. 
Switch (Orig_EXPR, Case1, Case2, ..., Default) 
Provides a method for writing compact expressions that can be used instead of complex, nested
Examples:

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 
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 ( For instances configured with a custom calendar, 
FiscalQuarter (D) 
Returns the quarter of the fiscal year in which date ( For instances configured with a custom calendar, 
FiscalYear (D) 
Returns the fiscal year in which date ( For instances configured with a custom calendar, 
VersionMonth (D) 
Returns a number that represents the month of the version in which the date ( For instances configured with a custom calendar, 
VersionYear (D) 
Returns a number that represents the year of the version in which the date ( For instances configured with a custom calendar 
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. 
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. 
Length (text) 
Returns the number of characters in a text string. 
Search (source_text, search_text) 
Returns the starting position of 
Substring (source_text, start_position, length) 
Returns a text string containing the characters found in Selecting a 
ToNumber (text) 
Converts a text string representing a number to its numeric value. Will convert the leading part of the string until a nonnumeric character is found. 
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