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 ACCRINTM Returns the accrued interest for a security that pays interest at maturity 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 AMORLINC Returns the depreciation of an asset for each accounting period, on a prorated basis 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 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 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 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 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 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.