1. Calculation functions
2. Functions
3. Excel comparison

This guide provides comparisons of all Microsoft Excel functions to those available in the Excel add-in.

Some Excel functions are directly comparable with Anaplan functions.

# A

 Excel function Excel description Anaplan solution ABS Returns the absolute value of a number. Use the ABS function. ACCRINT Returns the accrued interest for a security that pays interest on a periodic basis. See the Financial Functions. ACCRINTM Returns the accrued interest for a security that pays interest at maturity. See the Financial Functions. ACOS Returns the arccosine (in radians) of a number. There is no native Anaplan function. ACOSH Returns the inverse hyperbolic cosine of a number. There is no native Anaplan function. ADDRESS Returns a text representation of a cell address. Use the NAME function. AGGREGATE Apply functions such as AVERAGE, SUM, COUNT, MAX or MIN and ignores errors or hidden rows. Anaplan aggregates this 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 the Financial Functions. AMORLINC Returns the depreciation of an asset for each accounting period, on a prorated basis. See the Financial Functions. AND Returns TRUE if all of its arguments are TRUE. Use operators and constants with your data to create calculations and find the data you need. AREAS Returns the number of ranges in a reference. There is no native Anaplan function. ASIN Returns the arcsine (in radians) of a number. There is no native Anaplan function. ASINH Returns the inverse hyperbolic sine of a number. There is no native Anaplan function. ATAN Returns the arctangent (in radians) of a number. There is no native Anaplan function. ATAN2 Returns the arctangent (in radians) of (x,y) coordinates. There is no native Anaplan function. ATANH Returns the inverse hyperbolic tangent of a number. There is no native Anaplan function. AVEDEV Returns the average of the absolute deviations of the numbers provided. There is no native Anaplan function, but can be built using a calculation. AVERAGE Returns the average of the numbers provided. Use the x[AVERAGE:y] function. AVERAGEA Returns the average of the numbers provided and treats TRUE as 1 and FALSE as 0. There is no native Anaplan function. AVERAGEIF Returns the average of all numbers in a range of cells, based on a criterion. Use the x[AVERAGE:y] function to calculate the average for a specified range of values. AVERAGEIFS Returns the average of all numbers in a range of cells, based on multiple criteria Use the x[AVERAGE:y] function to calculate the average for a specified range of values.

# B

 Excel function Excel description Anaplan solution BETA.DIST Returns the beta distribution. There is no native Anaplan function. BETA.INV Returns the inverse of the cumulative beta probability density function. There is no native Anaplan function. BETADIST Returns the cumulative beta probability density function. There is no native Anaplan function. BETAINV Returns the inverse of the cumulative beta probability density function. There is no native Anaplan function, but can be built using a calculation. BIN2DEC Converts a binary number to a decimal number. There is no native Anaplan function, but can be built using a calculation. BIN2HEX Converts a binary number to a hexadecimal number. There is no native Anaplan function, but can be built using a calculation. BIN2OCT Converts a binary number to an octal number. There is no native Anaplan function, but can be built using a calculation. BINOM.DIST Returns the individual term binomial distribution probability. There is no native Anaplan function, but can be built using a calculation. BINOM.INV Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion. There is no native Anaplan function, but can be built using a calculation. BINOMDIST Returns the individual term binomial distribution probability. There is no native Anaplan function, but can be built using a calculation.

# C

 Excel function Excel description Anaplan solution CEILING Returns a number rounded up based on a multiple of significance. Use the MROUND function to round up to the nearest desired multiple. CEILING.PRECISE Returns a number rounded up to the nearest integer or to the nearest multiple of significance. Use the MROUND function to round up to the nearest desired multiple. CELL Used to retrieve information about a cell (e.g. contents, formatting, size). There is no native Anaplan function. CHAR Returns the character based on the ASCII value. There is no native Anaplan function. CHIDIST Returns the one-tailed probability of the chi-squared distribution. There is no native Anaplan function, but can be built using a calculation. CHIINV Returns the inverse of the one-tailed probability of the chi-squared distribution. There is no native Anaplan function, but can be built using a calculation. CHITEST Returns the value from the chi-squared distribution. There is no native Anaplan function, but can be built using a calculation. CHOOSE Returns a value from a list of values based on a given position. Use the FINDITEM function to match text to an item in a list, or match text to an item in a time period list. This can match on an item name or code. CLEAN Removes all non-printable characters from a string. There is no native Anaplan function. CODE Returns the ASCII value of a character or the first character in a cell. Use the CODE function to enter several conditions, and the AND function to test if all conditions are met. COLUMN Returns the column number of a cell reference. Use the ITEM calculation function. COLUMNS Returns the number of columns in a cell reference. There is no native Anaplan function, but can be built using a calculation. COMBIN Returns the number of combinations for a specified number of items. There is no native Anaplan function, but can be built using a calculation. COMBINA Returns the number of combinations for a specified number of items and includes repetitions. There is no native Anaplan function, but can be built using a calculation. COMPLEX Converts coefficients (real and imaginary) into a complex number. There is no native Anaplan function, but can be built using a calculation. CONCAT Joins two or more strings together. Use the & operator to concatenates two or more text strings. CONCATENATE Joins two or more strings together. This has been replaced by CONCAT function. Use the & operator to concatenates two or more text strings. CONCATENATE with & Joins two or more strings together using the & operator. Use the & operator to concatenates two or more text strings. CONVERT Convert a number from one measurement unit to another measurement unit. Use the LOOKUP function to look 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. There is no native Anaplan function, but can be built using a calculation. COSH Returns the hyperbolic cosine of a number. There is no native Anaplan function, but can be built using a 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. Combine an IF THEN statement with a SUM function to accomplish the same goal. COUNTBLANK Counts the number of empty cells in a range. Combine an IF THEN statement with a SUM function to accomplish the same goal. COUNTIF Counts the number of cells in a range, that meets the specified criteria. Combine an IF THEN statement with a SUM function to accomplish the same goal. COUNTIFS Counts the number of cells in a range, that meets a single or multiple criteria. Combine an IF THEN statement with a SUM function to accomplish the same goal. COUPDAYBS Returns the number of days from the beginning of a coupon period until its settlement date. Use the COUPDAYBS function. COUPDAYS Returns the number of days in the coupon period that contains the settlement date. Use the COUPDAYS function. COUPDAYSNC Returns the number of days from the settlement date to the next coupon date. Use the COUPDAYSNC function. COUPNCD Returns a number that represents the next coupon date after the settlement date. Use the COUPNCD function. COUPNUM Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon. Use the COUPNUM function. COUPPCD Returns a number that represents the previous coupon date before the settlement date. Use the COUPPCD function. COVAR Returns the covariance, the average of the products of deviations for two data sets. There is no native Anaplan function, but can be built using a calculation.

# N

 Excel function Excel description Anaplan solution N Converts a value to a number. Use the VALUE function. NA Returns the #N/A error value. There is no native Anaplan function. NETWORKDAYS Returns the number of work days between two dates, excluding weekends and holidays. There is no native Anaplan function, but can be built using a calculation. NETWORKDAYS.INTL Returns the number of work days between two dates, excluding weekends and holidays. This is supported by the Anaplan settings. NOT Returns the reversed logical value. Use operators and constants to create calculations and find the data you need. NOW Returns the current system date and time. There is no native Anaplan function, but this can be built by creating a module called Today with one date formatted line item that is selected when required. NPER Returns the number of periods for an investment. Use the NPER function. NPV Returns the net present value of an investment. Use the NPV function.

# O

 Excel function Excel description Anaplan solution ODD Rounds a number up to the nearest odd integer. There is no native Anaplan function, this can be built using a ROUND calculation. OFFSET Returns a reference to a range that is offset a number of rows and columns. There is no native Anaplan function, but can be built using a calculation. OR Returns TRUE if any of the conditions are TRUE Use operators and constants with your data to create calculations and find the data you need.

# P

 Excel function Excel description Anaplan solution PERCENTILE Returns the nth percentile from a set of values. There is no native Anaplan function, but can be built by using a calculation. PERCENTRANK Returns the rank of a value in a set of values as a percentage of the set. There is no native Anaplan function, but can be built by using a 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. Use the POWER function for permutations where repetition is allowed, where: x = the number of items n = the number of permutations PI Returns the mathematical constant called pi. There is no native Anaplan function, but can be built by using a calculation. PMT Returns the payment amount for a loan. Use the PMT formula to take a date as a parameter and return a time period as a result. POWER Returns the result of a number raised to a given power. Use the POWER function. PPMT Returns the payment on the principal for a particular payment. See the Financial Functions. PRICE Returns the price per \$100 face value of a security that pays periodic interest. Use the PRICE function. PRODUCT Multiplies the numbers and returns the product. Use an intermediate line item with the summary as the product. PROPER Sets the first character in each word to uppercase and the rest to lowercase. There is no native Anaplan function, but this can be achieved through a formula. PV Returns the present value of an investment. Use the PV function to  calculate the present value of future cash flows.

# Q

 Excel function Excel description Anaplan solution QUARTILE Returns the quartile from a set of values. There is no native Anaplan function, but can be built using a calculation.

# R

 Excel function Excel description Anaplan solution RADIANS Converts degrees into radians. There is no native Anaplan function, but can be built using a calculation. RAND Returns a random number that is greater than or equal to 0 and less than 1. There is no native Anaplan function, but can be built using a calculation. RANDBETWEEN Returns a random number that is between a bottom and top range. There is no native Anaplan function, but can be built using a calculation. RANK Returns the rank of a number within a set of numbers Use the RANK function to  order a set of values, and assign rankings from 1 to a specified ending rank. RATE Returns the interest rate for an annuity. Use the RATE function to calculate a per period interest rate. REPLACE Replaces a sequence of characters in a string with another set of characters. Use the FIND, LEN, LEFT, RIGHT, or & function. REPT Returns a repeated text value a specified number of times. There is no native Anaplan function, but can be built using a calculation. RIGHT Extracts a substring from a string starting from the right-most character. Use the RIGHT function. ROMAN Converts a number to a roman numeral. There is no native Anaplan function, but can be built using a calculation. ROUND Returns a number rounded to a specified number of digits. Use the ROUND function. ROUNDDOWN Returns a number rounded down to a specified number of digits. Use the ROUND function. ROUNDUP Returns a number rounded up to a specified number of digits Use the ROUND function. ROW Returns the row number of a cell reference. Use the ITEM function. ROWS Returns the number of rows in a cell reference. There is no native Anaplan function, but can be built using a calculation.

# S

 Excel function Excel description Anaplan solution SEARCH Returns the location of a substring in a string. Use the FIND formula to look for a text string in a text formatted item, starting at a specified character number. The result is a number that denotes the character position. SECOND Returns the seconds (a number from 0 to 59) from a time value. There is no native Anaplan function, but can be built using a calculation. SIGN Returns the sign of a number. Use the SIGN function. The result is -1 if the value is negative, 1 if positive, or 0 if zero. SIN Returns the sine of an angle. There is no native Anaplan function, but can be built using a calculation. SINH Returns the hyperbolic sine of a number. There is no native Anaplan function, but can be built using a calculation. SLN Returns the depreciation of an asset based on the straight line depreciation method. Use the SPREAD function. SLOPE Returns the slope of a regression line based on the data points identified by known_y_values and known_x_values. There is no native Anaplan function, but can be built using a calculation. SMALL Returns the nth smallest value from a set of values. There is no native Anaplan function, but can be built using a calculation. SQRT Returns the square root of a number. Use the SQRT function. STDEV Returns the standard deviation of a population based on a sample of numbers. There is no native Anaplan function, but can be built using a calculation. STDEVA Returns the standard deviation of a population based on a sample of numbers, text, and logical values. There is no native Anaplan function, but can be built using a calculation. STDEVP Returns the standard deviation of a population based on an entire population of numbers. There is no native Anaplan function, but can be built using a calculation. STDEVPA Returns the standard deviation of a population based on an entire population of numbers, text, and logical values. There is no native Anaplan function, but can be built using a calculation. SUBSTITUTE Replaces a set of characters with another. Use the FIND, LEN, or & function. SUBTOTAL Returns the subtotal of the numbers in a column in a list or database. Use the SUM & SELECT function to select an item from one or more of the hierarchy lists, and sum the 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. Use the SUM function to sum 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 an intermediate module. Configure the rows as criteria, and columns as line items that SUM criteria. SUMIFS Adds all numbers in a range of cells, based on a single or multiple criteria. Create an intermediate module. Configure the rows as criteria, and columns as line items that SUM criteria. SUMPRODUCT Multiplies the corresponding items in the arrays and returns the sum of the results. There is no native Anaplan function, but can be built using a calculation. SUMSQ Returns the sum of the squares of a series of values. Use the POWER function with SUM. SUMX2MY2 Returns the sum of the difference of squares between two arrays. There is no native Anaplan function, but can be built using a calculation. SUMX2PY2 Returns the sum of the squares of corresponding items in the arrays. There is no native Anaplan function, but can be built using a calculation. SUMXMY2 Returns the sum of the squares of the differences between corresponding items in the arrays. There is no native Anaplan function, but can be built using a calculation. SYD Returns the depreciation of an asset based on the sum-of-years digits depreciation method. There is no native Anaplan function, but can be built using a calculation.

# T

 Excel function Excel description Anaplan solution T Returns the text referred to by a value. Use the TEXT function to convert a numeric value to text. TAN Returns the tangent of an angle. There is no native Anaplan function, but can be built using a calculation. TANH Returns the hyperbolic tangent of a number. There is no native Anaplan function, but can be built using a calculation. TEXT Returns a value converted to text with a specified format. Use the TEXT function to  convert a numeric value to text. TEXTJOIN Used to join two or more strings together separated by a delimiter. Use the & function to concatenate two or more text strings. TIME Returns a decimal number given an hour, minute, and second value. There is no native Anaplan function, but can be built using a calculation. TIMEVALUE Returns the serial number of a time. There is no native Anaplan function, but can be built using a calculation. TODAY Returns the current system date. There is no native Anaplan function, but the date can be imported. TRANSPOSE Returns a transposed range of cells. Create a module with a pivoted view. TRIM Returns a text value with the leading and trailing spaces removed. Use the LEN, LEFT, or RIGHT formulas. TRUE Returns a logical value of TRUE. Use a boolean argument. TRUNC Returns a number truncated to a specified number of digits. Use the module formatting functionality. TYPE Returns the type of a value. There is no native Anaplan function.

# U

 Excel function Excel description Anaplan solution UPPER Convert text to all uppercase. Use the UPPER function.

# V

 Excel function Excel description Anaplan solution VALUE Converts a text value that represents a number to a number. Use the VALUE formula to convert a numeric value in a text field to a number format. VAR Returns the variance of a population based on a sample of numbers. Use the SELECT and COLLECT formulas with line items to calculate the variance. VARA Returns the variance of a population based on a sample of numbers, text, and logical values. Use the SELECT and COLLECT formulas with line items to calculate the variance. VARP Returns the variance of a population based on an entire population of numbers. Use the SELECT and COLLECT formulas with line items to calculate the variance. VARPA Returns the variance of a population based on an entire population of numbers, text, and logical values. Use the SELECT and COLLECT formulas with line items to calculate the variance. VDB Returns the depreciation of an asset based on a variable declining balance depreciation method. See the 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. Use the LOOKUP function to look 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.

# W

 Excel function Excel description Anaplan solution WEEKDAY Returns a number representing the day of the week, given a date value. Use the WEEKDAY formula to take a date and return a number from 1 to 7 for the day of the week. An optional Type parameter can be used to set the start day for the day of the week count. WEEKNUM Returns the week number for a date. There is no native Anaplan function, but can be built using a calculation. WORKDAY Adds a specified number of work days to a date, and returns the result as a serial date. There is no native Anaplan function, but can be built using a calculation. WORKDAY.INTL Adds a specified number of work days to a date, and returns the result as a serial date (customizable weekends). There is no native Anaplan function, but can be built using a calculation.

# X

 Excel function Excel description Anaplan solution XIRR Returns the internal rate of return for a series of cash flows that may not be periodic. See the Financial Functions.

# Y

 Excel function Excel description Anaplan solution YEAR Returns a four-digit year (a number from 1900 to 9999) given a date value. Use the YEAR function to convert a date or a time period to a year in a 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). Use the YEARFRAC function. YIELD Returns the yield on a security that pays periodic interest. Use the YIELD function.