Use functions to perform calculations with your data, change the format of your data, and more.

Some functions have differences in functionality, or are not available, in Polaris.

TitleSyntaxDescription
ABS
Numeric Functions
ABS(Number)

The ABS function returns the absolute value of a number. The absolute value of zero or a positive number remains the same. The absolute value of a negative number is the same number without the negative sign (the positive version).

ADDMONTHS
Time and Date Functions
ADDMONTHS(Date, number)

The ADDMONTHS function adds a number of months to a date.

ADDYEARS
Time and Date Functions
ADDYEARS(Date, number)

The ADDYEARS function adds a number of years to a date.

AGENTS
Call Center Planning Functions
AGENTS(SLA, Target response time, Arrival rate, Average duration)

The AGENTS function calculates the number of servers (or agents) needed to fulfil requests within a target time.

AGENTSB
Call Center Planning Functions
AGENTSB(SLA, Arrival rate, Average duration)

The AGENTSB function calculates the number of servers required to answer a specified percentage of calls (or SLA) within a busy period.

ALL
Aggregation Functions
Source[ALL: Mapping, ALL: Mapping 2, etc.]

The ALL aggregation function returns a TRUE result for all values that match specific Boolean criteria in a source module.

ANSWERTIME
Call Center Planning Functions
ANSWERTIME(Number of servers, SLA, Arrival rate, Average duration)

The ANSWERTIME function calculates the minimum hold time required to answer a certain percentage of calls, or service level agreement (SLA).

ANY
Aggregation Functions
Source[ANY: Mapping, ANY: Mapping 2, etc.]

The ANY aggregation function returns a TRUE result for any value that matches specific Boolean criteria in a source module.

ARRIVALRATE
Call Center Planning Functions
ARRIVALRATE(Number of servers, SLA, Target response time, Average duration)

The ARRIVALRATE function calculates the maximum interval between requests possible while processing a specified percentage of these requests.

AVERAGE
Aggregation Functions
Values to average[AVERAGE: Mapping, AVERAGE: Mapping 2, etc.]

The AVERAGE aggregation function takes a set of values from a source module and returns the mean average in a result module.

AVGDURATION
Call Center Planning Functions
AVGDURATION(Number of servers, SLA, Target response time, Arrival rate)

The AVGDURATION function calculates the required average duration of calls in order to answer a certain percentage of calls, or service level agreement (SLA).

AVGWAIT
Call Center Planning Functions
AVGWAIT(Number of servers, Arrival rate, Average duration)

The AVGWAIT function calculates the average waiting time for a request or call to be processed.

CODE
Miscellaneous Functions
CODE(Item)

The CODE function returns a list item's code.

COLLECT
Miscellaneous Functions
COLLECT()

Use the COLLECT function in a module that includes a line item subset to pull the source line item values into the module.

COMPARE
Logical Functions
COMPARE(Text to compare 1, Text to compare 2 [, Comparison mode] [, Locale])

The COMPARE function compares text values. If they're the same, it returns 0. If the first text value is greater, it returns 1, and if the first text value is less, it returns -1.

COUPDAYBS
Financial Functions
COUPDAYBS(Settlement, Maturity, Frequency[, Basis])

Use the COUPDAYBS (coupon days before settlement) function to calculate the number of days from the beginning of the coupon period until its settlement date. The number returned includes both the first day of the period and the settlement date.

COUPDAYS
Financial Functions
COUPDAYS(Settlement, Maturity, Frequency[, basis])

Use the COUPDAYS function to return the number of coupon days in the coupon period that contains the settlement date.

COUPDAYSNC
Financial Functions
COUPDAYBS(Settlement, Maturity, Frequency[, basis])

Use the COUPDAYSNC function to calculate the number of coupon days from the settlement date until the next coupon date. The number returned excludes the settlement date and includes the last day of the next coupon period.

COUPNCD
Financial Functions
COUPNCD(Settlement, Maturity, Frequency)

The COUPNCD function calculates the next coupon date after a settlement date.

COUPNUM
Financial Functions
COUPNUM(Settlement, Maturity, Frequency)

The COUPNUM function returns the number of coupons payable between a settlement and maturity date.

COUPPCD
Financial Functions
COUPPCD(Settlement, Maturity, Frequency)

The COUPPCD function calculates the previous coupon date before a settlement date.

CUMIPMT
Financial Functions
CUMIPMT(Interest rate, Number of periods, Principal, Start period, End period [, Timing])

The CUMIPMT function calculates the cumulative interest paid on a loan over a period given equal payments made to the balance.

CUMPRINC
Financial Functions
CUMPRINC(Interest rate, Number of periods, Loan balance, Start period, End period [, Timing])

The CUMPRINC function calculates the amount of principal paid on a loan over a period, given consistent, equal payments.

CUMULATE
Time and Date Functions
CUMULATE (Values to add [, Boolean] [, List])

The function CUMULATE adds values and returns a number. By default, the values are added across a time period. You can also choose to add values across a list.

CURRENTPERIODEND
Time and Date Functions
CURRENTPERIODEND()

The CURRENTPERIODEND function returns the end date from a model's current period.

CURRENTPERIODSTART
Time and Date Functions
CURRENTPERIODSTART()

The CURRENTPERIODSTART function returns the start date from a model's current period.

CURRENTVERSION
Miscellaneous Functions
CURRENTVERSION(Line item)

The CURRENTVERSION function returns the value from another line item for the version that is set as Current in a model.

DATE
Time and Date Functions
DATE(Year, Month, Day)

The DATE function forms a date from values that represent the year, month, and day. 

DAY
Time and Date Functions
DAY(Date)

The DAY function returns the day from a date as a number between 1 and 31. If the day is blank, it returns 0.

DAYS
Time and Date Functions
DAYS([Period])

The DAYS function returns the number of days in a given time period.

DAYSINMONTH
Time and Date Functions
DAYSINMONTH(Year, Month)

The DAYSINMONTH function returns the number of days in a month you specify.

DAYSINYEAR
Time and Date Functions
DAYINYEAR(Year)

The DAYSINYEAR function returns the number of days in a year you specify.

DECUMULATE
Time and Date Functions
DECUMULATE(Value to subtract)

The DECUMULATE function calculates the difference between the values of the current and previous periods.

DIVIDE
Numeric Functions
DIVIDE(Dividend, Divisor)

Use DIVIDE to divide one number by another.

DURATION
Financial Functions
DURATION(Settlement, Maturity, Rate, Yield, Frequency [, Basis])

You can use the DURATION function to calculate the Macauley duration for an assumed parity value of 100 monetary units.

The Macauley duration is the weighted average maturity of cash flows. That is, the weighted average distance to payment. It's used to measure a bond price's response to changes in yield. A higher Macauley duration value indicates a riskier investment.

END
Time and Date Functions
END([Time period]) 

The END function returns the last date of a time period. 

ERLANGB
Call Center Planning Functions
ERLANGB(Number of servers, Arrival rate, Average duration)

The ERLANGB function determines the probability of a request being blocked given a specified number of servers, arrival rate of requests, and the average service duration.

ERLANGC
Call Center Planning Functions
ERLANGC(Number of servers, Arrival rate, Average duration)

The ERLANGC function determines the probability of a request being placed in a queue given a specified number of servers, arrival rate of requests, and the average duration to process requests.

EXP
Numeric Functions
EXP(Number)

The EXP function raises the mathematical constant e, or Euler's number, to the power you specify.

FIND
Text Functions
FIND(Text to find, Text to search [, Starting character])

The FIND function searches for the first occurrence of a text value within another one. If the text contains the specified characters, the function returns a number. This number indicates the position of the first occurrence of the text value searched for.

FINDITEM
Miscellaneous Functions
FINDITEM(List or Time, Item to find)

The FINDITEM function searches for a text value within the names and codes of the items or time periods in a given list or Time respectively. If the FINDITEM function finds a match, it returns the corresponding list item or time period.

FIRSTNONBLANK
Aggregation Functions
Line item to search [FIRSTNONBLANK: Mapping, FIRSTNONBLANK: Mapping 2, etc.]

The aggregation function FIRSTNONBLANK returns the first value of a line item found for a given list item or time period. 

FIRSTNONZERO
Numeric Functions
FIRSTNONZERO(Value 1, Value 2, [etc.])

The FIRSTNONZERO function searches through two or more numeric arguments and returns the first value that is not zero.

FV
Financial Functions
FV(Interest rate, Number of periods, Payments [, Present value] [, Payment timing])

The FV function calculates the future value of an investment. The future value is the lump sum or closing balance received at the end of an investment.

HALFYEARTODATE
Time and Date Functions
HALFYEARTODATE(Line item to cumulate)

The HALFYEARTODATE function cumulates values from a single numeric parameter, over a half-year period. The HALFYEARTODATE cumulation starts at the Fiscal Year Start Month as selected in the Model Calendar, and resets every half-year.

HALFYEARVALUE
Time and Date Functions
HALFYEARVALUE(Line item)

The HALFYEARVALUE function references another line item and returns the half-yearly time summary in place of the detail value.

IF THEN ELSE
Logical Functions
IF Boolean argument THEN Result 1 ELSE Result 2

Tests a Boolean argument and returns one of two results based on whether it is true or false.

INPERIOD
Time and Date Functions
INPERIOD(Date to test, Time period)

The INPERIOD function returns a TRUE result for a date that falls under a time period or a module's Time dimension. It returns FALSE for all other dates.

IPMT
Financial Functions
IPMT(Interest rate, Period to examine, Number of periods, Present value [, Future value] [, Payment timing])

The IPMT function calculates the amount of interest to be paid on a loan in a given payment period. The function assumes a consistent interest rate and payment timings in each period.

IRR
Financial Functions
IRR(Cash flow [, Estimate])

The IRR function calculates the internal rate of return for a series of positive and negative transactions. It can be used either with all transactions over a timescale, or with specified transactions on certain dates.

ISACTUALVERSION
Logical Functions
ISACTUALVERSION()

The ISACTUALVERSION function returns a TRUE result for the version that is set as Actual in a model. It returns FALSE for all other versions.

ISANCESTOR
Logical Functions
ISANCESTOR(Ancestor, Descendant)

The ISANCESTOR function takes two list or time period values. It returns a Boolean value of TRUE if the first is an ancestor of the second. Ancestors of an item are its parent, its parent's parent, and so on.

ISBLANK
Logical Functions
ISBLANK(Value to test)

The ISBLANK function returns true for values that are blank.

ISCURRENTVERSION
Logical Functions
ISCURRENTVERSION()

The ISCURRENTVERSION function returns a TRUE result for the version that is set as Current in a model. It returns FALSE for all other versions.

ISFIRSTOCCURRENCE
Logical Functions
ISFIRSTOCCURRENCE(Values to compare, List dimension to search)

The ISFIRSTOCCURRENCE function returns a Boolean value of TRUE for the first occurrence of a value in a list dimension.

ISNOTBLANK
Logical Functions
ISNOTBLANK(Value to test)

The ISNOTBLANK function returns a Boolean result for values that are not blank.

ITEM
Miscellaneous Functions
ITEM(List or Time)

When used with a list, the ITEM function returns the list item that applies to each cell. When used with Time, it returns the time period that applies to each cell.

LAG
Time and Date Functions
LAG(Value to offset, Offset amount, Substitute value [, Non-positive behavior])

LAG returns a value from a period in the past. 

LASTNONBLANK
Aggregation Functions
Line item to search[LASTNONBLANK: Mapping, LASTNONBLANK: Mapping 2, etc.]

The aggregation function LASTNONBLANK returns the last value of a line item found for a given list item or time period. 

LEAD
Time and Date Functions
LEAD(Value to offset, Offset amount, Substitute value [, Non-positive behavior])

LEAD returns a value from a period in the future. 

LEFT
Text Functions
LEFT(Text [, Number of characters]) 

Extracts a string of characters from text, starting from the left.

LENGTH
Text Functions
LENGTH(Text to evaluate)

The LENGTH (or LEN) function returns the number of characters in a text string.

LN
Numeric Functions
LN(Number)

Use the natural logarithm (LN) to work out the length of time it takes to achieve a unit of growth.

LN returns the natural logarithm of a number, based on the constant e. This function is the inverse of the EXP function, which raises e to the nth power.

LOG
Numeric Functions
LOG(Number, Base)

The LOG function returns the logarithm of a number to the base you specify.

LOOKUP
Logical Functions
Values to lookup[LOOKUP: Mapping, LOOKUP: Mapping 2]

Use the LOOKUP function to look up values in a source module or list and display the values in a target module. 

LOWER
Text Functions
LOWER(Text [, Locale])

The LOWER function converts text values to lowercase.

MAILTO
Text Functions
MAILTO(Display text, To [, CC] [, BCC] [, Subject] [, Body text])

Use the MAILTO function to generate clickable links that send an email. You can specify recipients, subjects, and body text.

MAKELINK
Text Functions
MAKELINK(Display text, URL)

The MAKELINK function generates clickable links in a module.

MAX
Aggregation Functions
Source[MAX: Mapping, MAX: Mapping 2, etc.]

The MAX aggregation function returns the maximum value from a line item in a source module.

MAX
Numeric Functions
MAX(Value to compare, Value to compare 2, [etc.])

The MAX function returns the maximum from a set of values. For a number, it returns the maximum value. For a date, it returns the latest date.

MDURATION
Financial Functions
MDURATION(Settlement, Maturity, Rate, Yield, frequency [, basis])

You can use the MDURATION function to calculate the modified Macauley duration for an assumed parity value of 100 monetary units.

The modified Macauley duration expresses the measurable change in the value of a bond in response to a change in interest rates. The result represents the effect that a 1% change in interest rates will have on the price of a bond.

MID
Text Functions
MID(Text, Start position [, Number of characters])

Extracts a number of characters from a text string, starting from a character you select.

MIN
Aggregation Functions
Source[MIN: Mapping, MIN: Mapping 2, etc.]

The MIN aggregation function returns the minimum value from a line item in a source module.

MIN
Numeric Functions
MIN(Value to compare, Value to compare 2, [etc.])

The MIN function returns the minimum from a set of values. For a number, it returns the minimum value. For a date, it returns the earliest date.

MOD
Numeric Functions
MOD(Dividend, Divisor)

The MOD function returns the remainder when one number is divided by another, or modulo.

MONTH
Time and Date Functions
MONTH(Value to convert, [Time period method]) 

The function MONTH converts a date or time period to a month in number format.

MONTHTODATE
Time and Date Functions
MONTHTODATE(Line item to aggregate)

MONTHTODATE cumulates values from a single numeric parameter, within a monthly time range.

MONTHVALUE
Time and Date Functions
MONTHVALUE(Line item)

The MONTHVALUE function references another line item and returns the monthly time summary in place of the detail value.

MOVINGSUM
Time and Date Functions
MOVINGSUM(Line item to aggregate [, Start period] [, End period] [, Aggregation method])

MOVINGSUM returns values over a changing time range. For each time range included, it aggregates the sum of values found.

MROUND
Numeric Functions
MROUND(Number to round [, Multiple to round to] [, Rounding direction])

The MROUND function rounds a value to the nearest multiple of a number.

NAME
Text Functions
NAME(List item)

Use the NAME function to convert data from a list item to text.

NEXT
Time and Date Functions
NEXT(Expression)

The NEXT function evaluates an expression based on the next period in the Time dimension.

NEXTVERSION
Miscellaneous Functions
NEXTVERSION(Expression)

The NEXTVERSION function evaluates the given expression using the next version.

NPER
Financial Functions
NPER(Interest rate, Payments, Present value [, Residual value] [, Timing])

The NPER function calculates the required number of periods to achieve a certain value for a loan or investment. This is based on a given interest rate, consistent payments, and opening and closing balance.

NPV
Financial Functions
NPV(Discount rate, Cash flow, Dates, Transactions)

The NPV function calculates the net present value for a series of positive and negative transactions with a constant interest rate.

OFFSET
Time and Date Functions
OFFSET(Value to offset, Offset amount, Substitute value)

OFFSET returns a value from a period before or after the current period. 

PARENT
Miscellaneous Functions
PARENT(Child value)

The PARENT function returns the parent item of list items and time periods.

PERIOD
Time and Date Functions
PERIOD(Date)

The PERIOD function converts a date to a time period. 

PMT
Financial Functions
PMT(Interest rate, Number of periods, Present value [, Future value] [, Timing])

The PMT function calculates the payments due for a loan or annuity over a specified number of periods, given a consistent interest rate and payment amount.

POST
Time and Date Functions
POST(Value to post, Offset amount) 

The POST function offsets, or posts, a numeric value into the past or the future by a given number of periods. If multiple values are offset to the same period, the POST function adds them together.

POWER
Numeric Functions
POWER(Number, Power)

The POWER function raises a number to the power you specify.

PPMT
Financial Functions
PPMT(Interest rate, Period to examine, Number of periods, Present value [, Future value] [, Timing])

The PPMT function calculates how much of a payment is allocated to its principal part rather than interest. The function assumes a consistent interest rate and payment timings in each period.

PREVIOUS
Time and Date Functions
PREVIOUS(Expression)

The PREVIOUS function evaluates an expression based on the previous period of the Time dimension.

PREVIOUSVERSION
Miscellaneous Functions
PREVIOUSVERSION(Expression)

The PREVIOUSVERSION function evaluates the given expression using the previous version.

PRICE
Financial Functions
PRICE(Settlement, Maturity, Rate, Yield, Redemption, Frequency[, Basis])

The PRICE function calculates the price per 100 monetary units invested for a bond that pays periodic interest.

PROFILE
Time and Date Functions
PROFILE(Number to change, Profile)

Use the PROFILE function to multiply values over time based on a series of numbers, or profile.

PV
Financial Functions
PV(Interest rate, Number of periods, Payments, Future value, Payment timing)

The PV function calculates the present value of an investment or the principal value of a loan.

QUARTERTODATE
Time and Date Functions
QUARTERTODATE(Line item to aggregate)

QUARTERTODATE accumulates values from a single numeric parameter, within a quarterly time range.

QUARTERVALUE
Time and Date Functions
QUARTERVALUE(Line item)

The QUARTERVALUE function references another line item and returns the quarterly time summary in place of the detail value.

RANK
Miscellaneous Functions
RANK(Source values [, Direction] [, Equal value behavior] [, Include value] [, Ranking groups])

The RANK function evaluates a set of values and assigns sequential rankings starting at 1.

RANKCUMULATE
Miscellaneous Functions
RANKCUMULATE(Cumulation values, Ranking values [, Direction] [, Include value] [, Ranking groups])

The RANKCUMULATE function ranks values and then cumulates values in order of the ranking. It can perform ranking separately across different groups.

RATE
Financial Functions
RATE(Number of periods, Payments, Present value[, Future value] [, Payment timing] [ ,Rate estimate])

The RATE function calculates the interest rate for a loan or investment based on length, payments, and present and future value.

RIGHT
Text Functions
RIGHT(Text [, Number of characters])

Extracts a string of characters from text, starting from the right. 

ROUND
Numeric Functions
ROUND(Number to round [, Number of decimal places] [, Rounding direction] [, Rounding method])

The ROUND function rounds a value to a specified number of decimal places, an integer, or a power of 10.

SELECT
Logical Functions
Source[SELECT: Target item]

Use the SELECT function to return values from a given list item or time period.

SIGN
Numeric Functions
SIGN(Number)

The SIGN function returns the sign of a number (whether it's positive, negative, or zero). The SIGN function returns 1 for positive numbers, 0 for zero, and -1 for negative numbers.

SLA
Call Center Planning Functions
SLA(Number of servers, Target response time, Arrival rate, Average duration)

The SLA function calculates what percentage of calls must be answered within a target answer time, or service level agreement (SLA).

SPREAD
Time and Date Functions
SPREAD(Value to divide, Number of time periods)

The SPREAD function divides a value evenly over a number of time periods. 

SQRT
Numeric Functions
SQRT(Number)

The SQRT function calculates the square root of a number.

START
Time and Date Functions
START(Time period)

The START function returns the first date of a time period. 

SUBSTITUTE
Text Functions
SUBSTITUTE(Text to search in, Text to find, Replacement text)

The SUBSTITUTE function finds all occurrences of a text value within another one, and replaces them with a given value.

SUM
Aggregation Functions
Values to sum[SUM: Mapping, SUM: Mapping 2, etc.]

The SUM aggregation function sums values in a result module based on mapping from a source module.

TEXT
Text Functions
TEXT(Number to convert)

The TEXT function converts numeric values to text.

TEXTLIST
Aggregation Functions
Values to list[TEXTLIST: Mapping, TEXTLIST: Mapping 2, etc.]

The TEXTLIST aggregation function returns a collection of text values as a comma-separated value. The values returned are based on mapping from a source module.

TEXTLIST
Text Functions
TEXTLIST(Text to concatenate, Separator, List to reference [, Duplicate behavior])

The TEXTLIST function concatenates a series of text values into a single text value.

TIMESUM
Time and Date Functions
TIMESUM(Line item to aggregate [, Start period] [, End period] [, Aggregation method])

The TIMESUM function aggregates values between two time periods and returns a single value.

TRIM
Text Functions
TRIM(Text)

The TRIM function removes all leading and trailing spaces, and extra spaces between words in a text string.

UPPER
Text Functions
UPPER(Text [, Locale])

The UPPER function converts text values to uppercase.

VALUE
Miscellaneous Functions
VALUE(Value to convert)

The VALUE function converts text values that represent numbers to numbers.

WEEKDAY
Time and Date Functions
WEEKDAY(Date [, First day of the week])

The WEEKDAY function converts a date to a number between one and seven, representing the day of the week.

WEEKTODATE
Time and Date Functions
WEEKTODATE(Line item to aggregate)

WEEKTODATE aggregates the daily values within a week from a single numeric parameter. WEEKTODATE resets after the last day of the week.

WEEKVALUE
Time and Date Functions
WEEKVALUE(Line item)

The WEEKVALUE function references another line item and returns the weekly time summary in place of the detail value.

YEAR
Time and Date Functions
YEAR(Value to convert, [Time period method]) 

The function YEAR converts a date or time period to a year in number format. 

YEARFRAC
Financial Functions
YEARFRAC(Start, End[, Basis])

Use the YEARFRAC function to calculate the fraction of a year between two dates (inclusive of the start date, exclusive of the end date).

The function uses a basis (day-count convention) to count the number of days between these dates, and then divide that number by the basis.

YEARTODATE
Time and Date Functions
YEARTODATE(Line item)

YEARTODATE cumulates values from a single numeric parameter, within a yearly time range. YEARTODATE resets at each yearly start date, based on Calendar Type.

YEARVALUE
Time and Date Functions
YEARVALUE(Line item)

The YEARVALUE function references another line item and returns the yearly time summary in place of the detail value.

YIELD
Financial Functions
YIELD(Settlement, Maturity, Rate, Price, Redemption, Frequency [, Basis])

Use this function to calculate the yield to maturity (YTM) of a bond.

Disclaimer

We update Anapedia regularly to provide the most up-to-date instructions.