1. Calculation functions
  2. All Functions
A
B
C
D
E
F
H
I
L
M
N
O
P
Q
R
S
T
U
V
W
Y

Function Description Type
ABS Returns the absolute value of a number. Numeric
ADDMONTHS Add a specified number of months to a date. Time and Date
ADDYEARS Add a specified number of years to a date. Time and Date
AGENTS Calculates the number of agents required to service a given number of calls and meet the service level agreement. Call Center Planning
AGENTSB Calculates the number of agents required to handle the busy-period call traffic, given a percentage of calls that might receive a busy tone. Call Center Planning
x[ALL:y] Flags where all items are populated with a check-mark (used with Boolean format). Aggregation
AND Enter several conditions and this function tests to see if all conditions are met. Logical
ANSWERTIME Calculates the minimum waiting time to maintain a service level agreement. Call Center Planning
x[ANY:y] Flags where any items are populated with a check-mark (used with Boolean format). Aggregation
ARRIVALRATE Calculates the arrival rate of calls that can be received while guaranteeing the service level agreement. Call Center Planning
x[AVERAGE:y] Calculates the average for a specified range of values. Numeric
AVGDURATION Calculates the average duration of calls that are answered while guaranteeing the service level agreement. Call Center Planning
AVGWAIT Calculates the average waiting time for incoming calls. Call Center Planning
BLANK When the result is text, list, time period, or date-formatted the result can be set to be BLANK. Logical
CODE Returns the code of a list item. Miscellaneous
COLLECT Collects the data from the original line items from one or more modules (module must have a line item subset as a dimension). Miscellaneous
COMPARE Compares text values; result is 0 if they match, 1 or -1 if not matched. Logical
COUPDAYS Returns the number of coupon days in the coupon period that contains the settlement date. Financial
COUPDAYSBS Calculates the number of coupon days before the settlement date. Financial
COUPDAYSNC Determines the number of coupon days from the settlement date until the next coupon date. Financial
COUPNCD Calculates the next coupon date after the settlement date. Financial
COUPNUM Returns the number of coupons payable between the settlement date of a bond and the bond's maturity. Financial
COUPPCD Identifies the previous coupon date before the settlement date. Financial
CUMIPMT Calculates the cumulative interest paid on a loan during a specified period. Financial
CUMPRINC Calculates the cumulative total of the principal amount paid during a given period for a loan. Financial
CUMULATE Gives the cumulative sum of values from the first period of the timescale or the cumulative sum across the items in a named non-time list. Time and Date
CURRENTPERIODEND The end date of the Current period. Time and Date
CURRENTPERIODSTART The start date of the Current period. Time and Date
CURRENTVERSION Returns the value of the current version as defined under Settings, Versions. Miscellaneous
DATE Converts numbers yyyy, mm, and dd to a date. Date format depends on your locale. Time and Date
DAY Converts a date to a day in number format. Date format depends on your locale. Time and Date
DAYS Number of days in a time period. Time and Date
DAYSINMONTH Number of days in a specified calendar month. Time and Date
DAYSINYEAR Number of days in a specified year where year is formatted as a four digit number, YYYY. Time and Date
DECUMULATE Calculates the difference in a value in the current period vs previous period. Time and Date
DIVIDE Same as normal division x/y except that 1/0=Infinity and 0/0=NaN (not a number). Numeric
DURATION Uses the Macaulay duration to indicate a bond price's response to changes in yield.  Financial
END Result is the last date in the period. Time and Date
ERLANG-B Assumes no queue and calculates the probability a call will be blocked entirely. Call Center Planning
ERLANG-C Assumes an unlimited queue and calculates the probability a call will be placed in the queue. Call Center Planning
EXP Returns e raised to the nth power, where e = 2.71828183. Numeric
FIND Look for a text string within a text-formatted item, starting at a character number. Returns a number that denotes the character position. Miscellaneous
FINDITEM Matches text to a list item or matches text to an item in a time period list (can match on item name or code). Miscellaneous
FIRSTNONBLANK Shows the first non-blank text, list or date cell and uses the mapping to determine where to post the result. Logical
FIRSTNONZERO This takes at least two numeric arguments, and returns the first that is non-zero. Numeric
FV Future value of an investment. Financial
HALFYEARTODATE Takes a single numeric parameter and returns a cumulative sums across a half-year time span and then resets. Time and Date
HALFYEARVALUE Returns the half-year value of the source line item, according to the Time Summary method set for the source. Time and Date
IF ISNOTBLANK Test to see if a date, text, time period, or list-formatted cell is not blank. Logical
IF ISBLANK Tests whether a date, text, time period, or list-formatted cell is blank. Logical
IF AND Tests whether two or more conditions are met. Logical
IF NOT Tests whether condition is not true. Logical
IF OR Tests if any of two or more conditions is met. Logical
IF THEN ELSE Conditional formula. Logical
INPERIOD Tests whether a date falls within the period specified on the time dimension or falls within a time period (result is Boolean-formatted). Time and Date
IPMT Calculates the amount allocated to loan interest in a period. Financial
IRR Calculates the internal rate of return of a series of future cashflows. Financial
ISACTUALVERSION Tests to see if the version you are on is the Actual version (result is Boolean-formatted). Logical
ISANCESTOR Tests whether a specified list item is an ancestor of the second specified list item or whether a specified time period is an ancestor of a second specified time period (result is Boolean-formatted). Logical
ISBLANK Tests whether an item is blank (the item must be date, text, list, or time period-formatted and the result is Boolean-formatted). Logical
ISCURRENTVERSION Tests to see if the version is current based on the flag that you have set in Settings > Versions. Logical
ISFIRSTOCCURRENCE Tests if a value in the source (first parameter) is the first occurrence of that value within the source (result is Boolean-formatted) Logical
ISNOTBLANK Tests whether an item is not blank (the item must be date, text, list, or time period-formatted and the result is Boolean-formatted) Logical
ITEM Returns the list item of a list-formatted or time period-formatted line item. Miscellaneous
LAG Take the value from n periods in the past. If this is before the start of the timescale, use the overflow value. Time and Date
LASTNONBLANK Shows the last non-blank text, list or date cell and uses the mapping to determine where to post the result. Logical
LEAD Take the value from n periods in the future. If this is beyond the end of the timescale, use the overflow value. Time and Date
LEFT Extracts a sub-string from a string starting at the leftmost character. Miscellaneous
LEN Variant of LENGTH. Returns the number of characters in a text string. Miscellaneous
LENGTH Returns the number of characters in a text string. Miscellaneous
LN Returns the natural logarithm of a number, based on the constant e. Numeric
LOG Returns the logarithm of a number to the specified base. Numeric
LOOKUP Looks up an amount from a source module using one or more mappings. Each mapping matches a list-formatted line item, a time period-formatted item, or property from the source with a dimension of the target line item. Logical
LOWER Converts text to lowercase. Miscellaneous
MAILTO Composes the To, cc, Bcc, Subject, and Body of an email. Miscellaneous
MAKELINK Composes a click-able URL. Miscellaneous
MAX Maximum value of selected line items. Numeric
x[MAX:y] The maximum value for a specified item in one of the target dimensions. The source will have a list-formatted item for this dimension. Aggregation
MDURATION Uses the modified Macaulay duration to tell you by what percentage the value of a bond will change for a 1% change in the yield. Financial
MID Extract a sub-string from a string starting at any character. Miscellaneous
MIN Minimum of selected line items. Numeric
x[MIN:y] The minimum value for a specified item in one of the target dimensions. The source will have a list-formatted item for this dimension. Aggregation
MOD Modulus: remainder after dividing a dividend by a divisor. Numeric
MONTH Converts a date or a time period to a calendar month as number. Time and Date
MONTHTODATE Takes a single numeric parameter and returns a cumulative sums across a month time span and then resets. Time and Date
MONTHVALUE Returns the month value of the source line item, according to the time summary method set for the source. Time and Date
MOVINGSUM Calculates moving values, such as a moving sum or moving average. Time and Date
MROUND Round to the nearest multiple. Numeric
NAME Converts a list item or time period to text. Miscellaneous
NEXT Returns a value from the next period. Time and Date
NEXTVERSION Returns a value from the next version. Miscellaneous
NOT Tests to see if a condition is not met (result is Boolean-formatted). Logical
NPER The length in periods of the investment term. Financial
NPV Calculates the net present value of a series of cash flows using a constant interest rate. Financial
OFFSET Returns a value from a specified number of periods in advance. Time and Date
OR Tests to see if any of two or more conditions is met. Logical
PARENT Can be used against list-formatted items to return the parent of a list item or against time period-formatted items to return the parent time period. Miscellaneous
PERIOD Take a date as parameter and returns a time period as result. Time and Date
PMT Calculates the payments for a loan or annuity with constant payments and a constant interest rate. Financial
POST Post a value to a specified number of periods in the future. Time and Date
POWER Raise a value to a power. Numeric
PPMT Calculates the amount of a payment allocated to the principal part of a loan. Financial
PREVIOUS Takes a value from the previous period. Time and Date
PREVIOUSVERSION Takes a value from the previous version. Miscellaneous
PRICE Returns the price per 100 monetary units of a bond that pays periodic interest. Financial
PROFILE Allocates a value over the next few periods based on a table. Time and Date
PV Calculates the present value of future cash flows. Financial
QUARTERTODATE Takes a single numeric parameter and returns a cumulative sums across a quarter time span and then resets. Time and Date
QUARTERVALUE Returns the quarter value of the source line item, according to the time summary method set for the source. Time and Date
RANK Orders a set of values and assigns rankings from 1 to a specified ending rank. Miscellaneous
RANKCUMULATE Allows you to cumulate line item values based on ranking criteria, that might contain groupings. Can be used against lists but not time. Aggregation
RATE Calculates a per period interest rate. Miscellaneous
RIGHT Extracts a sub-string from a string starting at the rightmost character. Miscellaneous
ROUND Rounds a value to a specified number of decimal places. Numeric
SELECT Selects a specific item from one or more of the hierarchy lists. Logical
SELECT & LOOKUP Selects a specific item from one or more of the hierarchy lists, then looks up a value based on matching that selection. Compound
SIGN Show the sign of a number (result = -1 if the value is negative, 1 if positive, 0 if zero).

Numeric

SLA Calculates the percentage of incoming calls to be answered within a target answer time to meet a Service Level Agreement (SLA). Call Center Planning
SPREAD Spreads a value evenly over a number of periods. Time and Date
SQRT Square root of a value. Numeric
START Result is the first date in the period. Time and Date
SUBSTITUTE Replaces a single text character or a string of text characters with another. Miscellaneous
SUM Sums values based on a list-formatted or time period-formatted line item or property in the source that matches a dimension in the target. Logical
SUM & LOOKUP Sums based on a list-formatted line item or property in the source, then looks up a value from a source module using one or more mappings. Compound
SUM & SELECT Selects a specific item from one or more of the hierarchy lists, then sums values based on a list-formatted line item or property in the source that matches a dimension in the target. Compound
TEXT Converts a numeric value to text. Miscellaneous
TEXTLIST Lists a series of text items in a single result cell. Aggregation
TIMESUM Aggregates between the from and to time periods. These can be absolute time periods or relative to the latest actual time period. Aggregation
TRIM Replaces multiple text spaces between words in text, and at the beginning or end of the text, with a single space. Miscellaneous
UPPER Converts text to uppercase. Miscellaneous
VALUE Converts a numeric value in a text field to a number format. Miscellaneous
WEEKDAY Takes a date and returns a number 1 to 7 for the day-of-week number. An optional Type parameter can be used to set the start day for the day-of-week count. Time and Date
WEEKTODATE Takes a single numeric parameter and returns a cumulative sums across a week time span and then resets. Time and Date
WEEKVALUE Returns the week value of the source line item, according to the time summary method set for the source. Time and Date
YEAR Converts a date or a time period to a year in number format. Time and Date
YEARFRAC Calculates the fraction of a year between two dates. Financial
YEARTODATE Takes a single numeric parameter and returns a cumulative sums across a year time span and then resets. Time and Date
YEARVALUE Returns the year value of the source line item, according to the time summary method set for the source. Time and Date
YIELD Determines the yield to maturity of a bond — the interest rate that, when used to discount the bond's future cashflows, produces the given price. Financial