# 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`.

`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: `ColumnMin (ROW.PayRate)`

`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: `ColumnMax (ROW.PayRate)`

### 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 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)`
`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.
`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 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.
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```