1. Calculation functions
  2. Functions
  3. Excel Comparison
EXCEL function Excel Description ANAPLAN Solution
ABS Returns the absolute value of a number ABS—Returns the absolute value of a number.
ACCRINT Returns the accrued interest for a security that pays interest on a periodic basis

See Financial Functions

ACCRINTM Returns the accrued interest for a security that pays interest at maturity

See Financial Functions

ACOS Returns the arccosine (in radians) of a number No native Anaplan function
ACOSH Returns the inverse hyperbolic cosine of a number No native Anaplan function
ADDRESS Returns a text representation of a cell address NAME
AGGREGATE Apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows Anaplan aggregates as part of the module functionality, set the aggregation level in the module
AMORDEGRC Returns the linear depreciation of an asset for each accounting period, on a prorated basis

See Financial Functions

AMORLINC Returns the depreciation of an asset for each accounting period, on a prorated basis

See Financial Functions

x[ALL:y] AND Returns TRUE if all conditions are TRUE
AREAS Returns the number of ranges in a reference No native Anaplan function
ASIN Returns the arcsine (in radians) of a number No native Anaplan function
ASINH Returns the inverse hyperbolic sine of a number No native Anaplan function
ATAN Returns the arctangent (in radians) of a number No native Anaplan function
ATAN2 Returns the arctangent (in radians) of (x,y) coordinates No native Anaplan function
ATANH Returns the inverse hyperbolic tangent of a number No native Anaplan function
AVEDEV Returns the average of the absolute deviations of the numbers provided No native Anaplan function: can be built using calculation
AVERAGE Returns the average of the numbers provided x[AVERAGE:y]
AVERAGEA Returns the average of the numbers provided and treats TRUE as 1 and FALSE as 0

No native Anaplan function

AVERAGEIF Returns the average of all numbers in a range of cells, based on a given criteria x[AVERAGE:y]—calculates the average for a specified range of values.
AVERAGEIFS Returns the average of all numbers in a range of cells, based on multiple criteria x[AVERAGE:y]—calculates the average for a specified range of values.
BETA.DIST Returns the beta distribution No native Anaplan function
BETA.INV Returns the inverse of the cumulative beta probability density function No native Anaplan function
BETADIST Returns the cumulative beta probability density function No native Anaplan function
BETAINV Returns the inverse of the cumulative beta probability density function No native Anaplan function: can be built using calculation
BIN2DEC Converts a binary number to a decimal number No native Anaplan function: can be built using calculation
BIN2HEX Converts a binary number to a hexadecimal number No native Anaplan function: can be built using calculation
BIN2OCT Converts a binary number to an octal number No native Anaplan function: can be built using calculation
BINOM.DIST Returns the individual term binomial distribution probability No native Anaplan function: can be built using calculation
BINOM.INV Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion No native Anaplan function: can be built using calculation
BINOMDIST Returns the individual term binomial distribution probability No native Anaplan function: can be built using calculation
CEILING Returns a number rounded up based on a multiple of significance MROUND—round to the nearest desired multiple.
CEILING.PRECISE Returns a number rounded up to the nearest integer or to the nearest multiple of significance MROUND—round to the nearest desired multiple.
CELL Used to retrieve information about a cell such as contents, formatting, size, etc.

No native Anaplan function

CHAR Returns the character based on the ASCII value No native Anaplan function.
CHIDIST Returns the one-tailed probability of the chi-squared distribution No native Anaplan function: can be built using calculation
CHIINV Returns the inverse of the one-tailed probability of the chi-squared distribution No native Anaplan function: can be built using calculation
CHITEST Returns the value from the chi-squared distribution No native Anaplan function: can be built using calculation
CHOOSE Returns a value from a list of values based on a given position FINDITEM—matches text to an item in a list or matches text to an item in a time period list (can match on item name or code).
CLEAN Removes all non-printable characters from a string

No native Anaplan function

CODE Returns the ASCII value of a character or the first character in a cell CODE—enter several conditions and the AND function tests to see if all conditions are met.
COLUMN Returns the column number of a cell reference ITEM.
COLUMNS Returns the number of columns in a cell reference No native Anaplan function: can be built using calculation
COMBIN Returns the number of combinations for a specified number of items No native Anaplan function: can be built using calculation
COMBINA Returns the number of combinations for a specified number of items and includes repetitions No native Anaplan function: can be built using calculation
COMPLEX Converts coefficients (real and imaginary) into a complex number No native Anaplan function: can be built using calculation
CONCAT Used to join two or more strings together &—concatenates two or more text strings.
CONCATENATE Used to join two or more strings together (replaced by CONCAT Function) &—concatenates two or more text strings.
CONCATENATE with & Used to join two or more strings together using the & operator &—concatenates two or more text strings.
CONVERT Convert a number from one measurement unit to another measurement unit 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.
COS Returns the cosine of an angle No native Anaplan function: can be built using calculation
COSH Returns the hyperbolic cosine of a number No native Anaplan function: can be built using calculation
COUNT Counts the number of cells that contain numbers as well as the number of arguments that contain numbers Use an IF THEN statement with a SUM function to accomplish the same goals. Use IF THEN ELSE to create a count, then use SUM to get the total for that line item.
COUNTA Counts the number of cells that are not empty as well as the number of value arguments provided You can combine an IF THEN statement with a SUM function to accomplish the same goals.
COUNTBLANK Counts the number of empty cells in a range You can combine an IF THEN statement with a SUM function to accomplish the same goals.
COUNTIF Counts the number of cells in a range, that meets a given criteria You can combine an IF THEN statement with a SUM function to accomplish the same goals.
COUNTIFS Counts the number of cells in a range, that meets a single or multiple criteria You can combine an IF THEN statement with a SUM function to accomplish the same goals.
COUPDAYBS Returns the number of days from the beginning of a coupon period until its settlement date COUPDAYBS
COUPDAYS Returns the number of days in the coupon period that contains the settlement date COUPDAYS
COUPDAYSNC Returns the number of days from the settlement date to the next coupon date COUPDAYSNC
COUPNCD Returns a number that represents the next coupon date after the settlement date COUPNCD
COUPNUM Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon COUPNUM
COUPPCD Returns a number that represents the previous coupon date before the settlement date COUPPCD
COVAR Returns the covariance, the average of the products of deviations for two data sets No native Anaplan function: can be built using calculation
DATE Returns the serial date value for a date DATE—converts numbers yyyy, mm and dd to a date. Date format depends on your locale.
DATEDIF Returns the difference between two date values, based on the interval specified DECUMULATE—calculates the difference in a value in the current period vs previous period.
DATEVALUE Returns the serial number of a date

Function not required by Anaplan—use Date Settings.

DAVERAGE Averages all numbers in a column in a list or database, based on a given criteria

Function not required by Anaplan—use Summary method AVERAGE.

DAY Returns the day of the month (a number from 1 to 31) given a date value DAY—converts a date to a day in number format. Date format depends on your locale.
DAYS Returns the number of days between two dates DAYS—number of days in a time period. Takes no parameter or a single time period parameter. Returns the number of days in a time period:
DAYS360 Returns the number of days between two dates based on a 360-day year DAYS—number of days in a time period. Takes no parameter or a single time period parameter. Returns the number of days in a time period:
DB Returns the depreciation of an asset based on the fixed-declining balance method See Financial Functions
DCOUNT Returns the number of cells in a column or database that contains numeric values and meets a given criteria Function not required by Anaplan—use SUM
DCOUNTA Returns the number of cells in a column or database that contains non-blank values and meets a given criteria Function not required by Anaplan—use SUM
DDB Returns the depreciation of an asset based on the double-declining balance method

See Financial Functions

DEGREES Converts radians into degrees No native Anaplan function: can be built using calculation
DGET Retrieves from a database a single record that matches a given criteria ITEM
DMAX Returns the largest number in a column in a list or database, based on a given criteria x[MAX:y]
DMIN Returns the smallest number in a column in a list or database, based on a given criteria x[MIN:y]
DOLLAR Converts a number to text, using a currency format No native Anaplan function: can be built using calculation
DPRODUCT Returns the product of the numbers in a column in a list or database, based on a given criteria

No native Anaplan function: can be built using calculation

DSTDEV Returns the standard deviation of a population based on a sample of numbers

No native Anaplan function: can be built using calculation

DSTDEVP Returns the standard deviation of a population based on the entire population of numbers

No native Anaplan function: can be built using calculation

DSUM Sums the numbers in a column or database that meets a given criteria

SUM

DURATION Returns the Macauley duration for an assumed par value of $100. Duration is defined as the weighted average of the present value of cash flows, and is used as a measure of a bond price's response to changes in yield DURATION
DVAR Returns the variance of a population based on a sample of numbers

Using SELECT and COLLECT with line items to calculate Variance

DVARP Returns the variance of a population based on the entire population of numbers

Using SELECT and COLLECT with line items to calculate Variance

EDATE Adds a specified number of months to a date and returns the result as a serial date OFFSET—returns a value from a specified number of periods in advance
EOMONTH Calculates the last day of the month after adding a specified number of months to a date END
ERROR.TYPE Returns the numeric representation of an Excel error

No native Anaplan function: can be built using calculation.

EVEN Rounds a number up to the nearest even integer No native Anaplan function: can be built using calculation
EXACT Compares two strings and returns TRUE if both values are the same COMPARE—compares text values; result is 0 if they match, 1 or -1 if not matched.
EXP Returns e raised to the nth power EXP—returns e raised to the nth power.
FACT Returns the factorial of a number No native Anaplan function: can be built using calculation.
FALSE Returns a logical value of FALSE

Anaplan Functionality: Boolean argument

FIND Returns the location of a substring in a string (case-sensitive) FIND—look for a text string within a text-formatted item, starting at a specified character number (result is a number that denotes the character position).
FIXED Returns a text representation of a number rounded to a specified number of decimal places VALUE—converts a numeric value in a text field to a number format.
FLOOR Returns a number rounded down based on a multiple of significance No native Anaplan function: can be built using calculation
FORECAST Returns a prediction of a future value based on existing values provided FV
FREQUENCY Returns how often values occur within a set of data. It returns a vertical array of numbers

No native Anaplan function: can be built using calculation

FV Returns the future value of an investment FV
GROWTH Returns the predicted exponential growth based on existing values provided EXP distributed along timeline
HLOOKUP Performs a horizontal lookup by searching for a value in the top row of the table and returning the value in the same column based on the index_number 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.
HOUR Returns the hours (a number from 0 to 23) from a time value No native Anaplan function: can be built using calculation
HYPERLINK Creates a shortcut to a file or Internet address MAKELINK—composes a click-able URL
IF Returns one value if the condition is TRUE or another value if the condition is FALSE IF THEN ELSE—conditional formula
IF (more than 7) Nest more than seven IF functions IF THEN ELSE—conditional formula
IF (up to 7) Nest up to seven IF functions IF THEN ELSE—conditional formula
IFERROR Used to return an alternate value if a formula results in an error IF THEN ELSE
IFNA Used to return an alternate value if a formula results in #N/A error IF THEN ELSE
IFS Specify multiple IF conditions within a function IF THEN ELSE
INDEX Returns either the value or the reference to a value from a table or range FINDITEM—matches text to an item in a list or matches text to an item in a time period list (can match on item name or code)
INDIRECT Returns the reference to a cell based on its string representation Use LOOKUP and TEXT
INFO Returns information about the operating environment

No native Anaplan function: can be built using calculation

INT Returns the integer portion of a number Handled by module formatting.
INTERCEPT Returns the y-axis intersection point of a line using x-axis values and y-axis values

No native Anaplan function: can be built using calculation

IPMT Returns the interest payment for an investment RATE—calculates a per period interest rate
IRR Returns the internal rate of return for a series of cash flows IRR
ISBLANK Used to check for blank or null values ISBLANK—tests whether an item is blank
ISERR Used to check for error values except #N/A No native Anaplan function: can be built using calculation
ISERROR Used to check for error values No native Anaplan function: can be built using calculation
ISLOGICAL Used to check for a logical value (TRUE or FALSE) No native Anaplan function: can be built using calculation
ISNA Used to check for #N/A error No native Anaplan function: can be built using calculation
ISNONTEXT Used to check for a value that is not text No native Anaplan function: can be built using calculation
ISNUMBER Used to check for a numeric value No native Anaplan function: can be built using calculation
ISOWEEKNUM Returns the ISO week number for a date

No native Anaplan function—Use the internal time structure within Anaplan

ISPMT Returns the interest payment for an investment RATE—calculates a per period interest rate.
ISREF Used to check for a reference No native Anaplan function: can be built using calculation
ISTEXT Used to check for a text value No native Anaplan function: can be built using calculation
LARGE Returns the nth largest value from a set of values

No native Anaplan function: can be built using calculation

LEFT Extract a substring from a string, starting from the left-most character LEFT—extracts a substring from a string starting at the leftmost character
LEN Returns the length of the specified string LEN—variant of LENGTH. Returns the number of characters in a text string
LINEST Uses the least squares method to calculate the statistics for a straight line and returns an array describing that line

No native Anaplan function: can be built using calculation

LN Returns the natural logarithm of a number LN—returns the natural logarithm of a number, based on the constant e
LOG Returns the logarithm of a number to a specified base LOG—returns the logarithm of a number to the specified base
LOG10 Returns the base-10 logarithm of a number LOG—returns the logarithm of a number to the specified base
LOOKUP Returns a value from a range (one row or one column) or from an array 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.
LOWER Converts all letters in the specified string to lowercase LOWER—converts text to lowercase
MATCH Searches for a value in an array and returns the relative position of that item 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.
MAX Returns the largest value from the numbers provided MAX
MAXA Returns the largest value from the values provided (numbers, text and logical values) x[MAX_y]
MDETERM Returns the matrix determinant of an array No native Anaplan function: can be built using calculation
MDURATION Returns the modified Macauley duration for a security with an assumed par value of $100 MDURATION
MEDIAN Returns the median of the numbers provided

No native Anaplan function: can be built using calculation

MID Extracts a substring from a string (starting at any position) MID—extract a substring from a string starting at any character
MIN Returns the smallest value from the numbers provided MIN—minimum of selected line items
MINA Returns the smallest value from the values provided (numbers, text and logical values) MIN—minimum of selected line items
MINUTE Returns the minutes (a number from 0 to 59) from a time value

No native Anaplan function: can be built using calculation

MINVERSE Returns the inverse matrix for a given matrix No native Anaplan function: can be built using calculation
MIRR Returns the modified internal rate of return for a series of cash flows IRR
MMULT Returns the matrix product of two arrays No native Anaplan function: can be built using calculation
MOD Returns the remainder after a number is divided by a divisor Modulus: remainder after dividing a dividend by a divisor
MONTH Returns the month (a number from 1 to 12) given a date value MONTH—converts a date or a time period to a calendar month as number
N Converts a value to a number VALUE
NA Returns the #N/A error value

No native Anaplan function

NETWORKDAYS Returns the number of work days between two dates, excluding weekends and holidays

Function not supported by Anaplan: can be built using calculation

NETWORKDAYS.INTL Returns the number of work days between two dates, excluding weekends and holidays

Function supported by Anaplan in Settings

NOT Returns the reversed logical value NOT
NOW Returns the current system date and time No native Anaplan function: can be built by creating a module called Today with one date-formatted line item which is selected when required
NPER Returns the number of periods for an investment NPER
NPV Returns the net present value of an investment NPV
ODD Rounds a number up to the nearest odd integer No native Anaplan function: can be built using ROUND calculation
OFFSET Returns a reference to a range that is offset a number of rows and columns No native Anaplan function: can be built using calculation
OR Returns TRUE if any of the conditions are TRUE x[ANY_y]
PERCENTILE Returns the nth percentile from a set of values

No native Anaplan function: can be built using calculation

PERCENTRANK returns the rank of a value in a set of values as a percentage of the set

No native Anaplan function: can be built using calculation:

Percentile = (number of values below X) + 0.5)/total number of values*100
PERMUT Returns the number of permutations for a specified number of items

For permutations where repetition is allowed, use POWER, with the number of items as x and the number of permutations as n.

PI Returns the mathematical constant called pi No native Anaplan function: can be built using calculation
PMT Returns the payment amount for a loan PMT—Take a date as parameter and returns a time period as result
POWER Returns the result of a number raised to a given power POWER—Raise a value to a power
PPMT Returns the payment on the principal for a particular payment

See Financial Functions

PRICE Returns the price per $100 face value of a security that pays periodic interest PRICE
PRODUCT Multiplies the numbers and returns the product Using a intermediate line item with the Summary as PRODUCT
PROPER Sets the first character in each word to uppercase and the rest to lowercase None: can be achieved through Formula
PV Returns the present value of an investment PV—calculates the present value of future cash-flows
QUARTILE Returns the quartile from a set of values

No native Anaplan function: can be built using calculation

RADIANS Converts degrees into radians No native Anaplan function: can be built using calculation
RAND Returns a random number that is greater than or equal to 0 and less than 1 No native Anaplan function: can be built using calculation
RANDBETWEEN Returns a random number that is between a bottom and top range No native Anaplan function: can be built using calculation
RANK Returns the rank of a number within a set of numbers RANK—orders a set of values and assigns rankings from 1 to a specified ending rank
RATE Returns the interest rate for an annuity RATE—calculates a per period interest rate
REPLACE Replaces a sequence of characters in a string with another set of characters Use FIND, LEN, LEFT, RIGHT,&
REPT Returns a repeated text value a specified number of times No native Anaplan function: can be built using calculation
RIGHT Extracts a substring from a string starting from the right-most character RIGHT—extracts a substring from a string starting at the rightmost character
ROMAN Converts a number to roman numeral No native Anaplan function: can be built using calculation
ROUND Returns a number rounded to a specified number of digits ROUND
ROUNDDOWN Returns a number rounded down to a specified number of digits ROUND
ROUNDUP Returns a number rounded up to a specified number of digits ROUND
ROW Returns the row number of a cell reference ITEM
ROWS Returns the number of rows in a cell reference No native Anaplan function: can be built using calculation
SEARCH Returns the location of a substring in a string FIND—look for a text string within a text-formatted item, starting at a specified character number (result is a number that denotes the character position).
SECOND Returns the seconds (a number from 0 to 59) from a time value No native Anaplan function: can be built using calculation
SIGN Returns the sign of a number SIGN—show the sign of a number (result = -1 if the value is negative, 1 if positive, 0 if zero)
SIN Returns the sine of an angle No native Anaplan function: can be built using calculation
SINH Returns the hyperbolic sine of a number No native Anaplan function: can be built using calculation
SLN Returns the depreciation of an asset based on the straight-line depreciation method SPREAD
SLOPE Returns the slope of a regression line based on the data points identified by known_y_values and known_x_values

No native Anaplan function: can be built using calculation

SMALL Returns the nth smallest value from a set of values

No native Anaplan function: can be built using calculation

SQRT Returns the square root of a number SQRT—square root of a value
STDEV Returns the standard deviation of a population based on a sample of numbers

No native Anaplan function: can be built using calculation

STDEVA Returns the standard deviation of a population based on a sample of numbers, text, and logical values

No native Anaplan function: can be built using calculation

STDEVP Returns the standard deviation of a population based on an entire population of numbers

No native Anaplan function: can be built using calculation

STDEVPA Returns the standard deviation of a population based on an entire population of numbers, text, and logical values

No native Anaplan function: can be built using calculation

SUBSTITUTE Replaces a set of characters with another Use FIND, LEN, &
SUBTOTAL Returns the subtotal of the numbers in a column in a list or database SUM & SELECT—first it selects a specific item from one or more of the hierarchy lists, then it sums values based on a list-formatted line item or property in the source that matches a dimension in the target.
SUM Adds all numbers in a range of cells 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.
SUMIF Adds all numbers in a range of cells based on one criteria Create intermediate module—configure Rows as criteria, column as line item which SUM criteria
SUMIFS Adds all numbers in a range of cells, based on a single or multiple criteria Create intermediate module—configure Rows as criteria, column as line item which SUM criteria
SUMPRODUCT Multiplies the corresponding items in the arrays and returns the sum of the results

No native Anaplan function: can be built using calculation

SUMSQ Returns the sum of the squares of a series of values Use POWER with SUM.
SUMX2MY2 Returns the sum of the difference of squares between two arrays No native Anaplan function: can be built using calculation
SUMX2PY2 Returns the sum of the squares of corresponding items in the arrays No native Anaplan function: can be built using calculation
SUMXMY2 Returns the sum of the squares of the differences between corresponding items in the arrays No native Anaplan function: can be built using calculation
SYD Returns the depreciation of an asset based on the sum-of-years' digits depreciation method

No native Anaplan function: can be built using calculation

T Returns the text referred to by a value TEXT—converts a numeric value to text.
TAN Returns the tangent of an angle No native Anaplan function: can be built using calculation
TANH Returns the hyperbolic tangent of a number No native Anaplan function: can be built using calculation
TEXT Returns a value converted to text with a specified format TEXT—converts a numeric value to text
TEXTJOIN Used to join two or more strings together separated by a delimiter

&—concatenates two or more text strings

TIME Returns a decimal number given an hour, minute and second value No native Anaplan function: can be built using calculation
TIMEVALUE Returns the serial number of a time No native Anaplan function: can be built using calculation
TODAY Returns the current system date No native Anaplan function: can be built using calculation
TRANSPOSE Returns a transposed range of cells Create a new module with pivoted view
TRIM Returns a text value with the leading and trailing spaces removed Use LEN, LEFT, RIGHT
TRUE Returns a logical value of TRUE

Anaplan functionality: Boolean argument

TRUNC Returns a number truncated to a specified number of digits Handled by module formatting
TYPE Returns the type of a value

No native Anaplan function

UPPER Convert text to all uppercase UPPER—converts text to uppercase
VALUE Converts a text value that represents a number to a number VALUE—converts a numeric value in a text field to a number format
VAR Returns the variance of a population based on a sample of numbers Using SELECT and COLLECT with line items to calculate Variance
VARA Returns the variance of a population based on a sample of numbers, text, and logical values Using SELECT and COLLECT with line items to calculate Variance
VARP Returns the variance of a population based on an entire population of numbers Using SELECT and COLLECT with line items to calculate Variance
VARPA Returns the variance of a population based on an entire population of numbers, text, and logical values Using SELECT and COLLECT with line items to calculate Variance
VDB Returns the depreciation of an asset based on a variable declining balance depreciation method

See Financial Functions

VLOOKUP Performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position 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.
WEEKDAY Returns a number representing the day of the week, given a date value 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.
WEEKNUM Returns the week number for a date

No native Anaplan function. Use the internal time structure within Anaplan: can be built using calculation.

WORKDAY Adds a specified number of work days to a date and returns the result as a serial date

No native Anaplan function. Use the internal time structure within Anaplan: can be built using calculation.

WORKDAY.INTL Adds a specified number of work days to a date and returns the result as a serial date (customizable weekends)

No native Anaplan function. Use the internal time structure within Anaplan—can be built using calculation.

XIRR Returns the internal rate of return for a series of cash flows that may not be periodic

See Financial Functions

YEAR Returns a four-digit year (a number from 1900 to 9999) given a date value YEAR—converts a date or a time period to a year in number format.
YEARFRAC Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date) YEARFRAC
YIELD Returns the yield on a security that pays periodic interest YIELD

Some of the functions available in Excel are directly comparable with Anaplan Functions. This is not an absolute list, please email Anapedia team if you need help translating an Excel function into Anaplan.