Excel New Functions
Office 365
Excel 365 introduced 9 new worksheet functions.
| DETECTLANGUAGE | (2024) Preview. Returns the language code for the text string provided. Step by step guide |
| GROUPBY | (2023) Preview. Returns the grouping of your data along one axis and aggregates the associated values Step by step guide. |
| PIVOTBY | (2023) Preview. Returns the grouping of your data along two axis and aggregates the associated values. |
| PY | (2023) Returns the value or object after running code in a Python Editor. |
| REGEXEXTRACT | (2024) Preview. Returns the characters that match the pattern provided. |
| REGEXREPLACE | (2024) Preview. Returns the text string after replacing characters that match the pattern provided. |
| REGEXTEST | (2024) Preview. Returns the boolean True or False depending if the pattern matches any part of a text string. |
| TRANSLATE | (2024) Preview. Returns the text string translated into another language. |
| TRIMRANGE | (2024) Preview. Returns the array after excluding all empty rows and/or columns from the outer edges of a range. |
2024 New Functions
Excel 2024 introduced 27 new worksheet functions.
| ARRAYTOTEXT | Returns the contents of an array or cell range converted into text. |
| BYCOL | Returns the results from applying a LAMBDA function to each column in an array. |
| BYROW | Returns the results from applying a LAMBDA function to each row in an array. |
| CHOOSECOLS | Returns the array with just a specific number of columns. |
| CHOOSEROWS | Returns the array with just a specific number of rows. |
| DROP | Returns the array without certain rows or columns. |
| EXPAND | Returns the array expanded or padded to specific dimensions. |
| FIELDVALUE | Returns the field data from a Stocks or Geography linked data type. |
| HSTACK | Returns the array after combining two arrays horizontally in sequence. |
| IMAGE | Returns the image from a web URL. |
| ISOMITTED | Returns the value True or False depending whether the value in a LAMBDA is missing. |
| LAMBDA | Returns the results of a formula added as a named range. |
| MAKEARRAY | Returns the array calculated by applying a LAMBDA function. |
| MAP | Returns the array formed by applying a map using a LAMBDA function. |
| REDUCE | Returns the total value after reducing an array by applying a LAMBDA function. |
| SCAN | Returns the array after applying a LAMBDA function to each value and returns an array. |
| STOCKHISTORY | Returns the historical data about a financial instrument. |
| TAKE | Returns the intersection of specific rows and columns in an array. |
| TEXTAFTER | Returns the characters from the end of a text string after a delimiter. |
| TEXTBEFORE | Returns the characters from the start of a text string before a delimiter. |
| TEXTSPLIT | Returns the text string split into multiple columns using delimiters. |
| TOCOL | Returns the array transformed into a single column. |
| TOROW | Returns the array transformed into a single row. |
| VALUETOTEXT | Returns the text from any specified value. |
| VSTACK | Returns the array after combining two arrays vertically in sequence. |
| WRAPCOLS | Returns the array transformed into multiple columns. |
| WRAPROWS | Returns array transformed into multiple rows. |
2021 New Functions
Excel 2021 introduced 9 new worksheet functions.
| FILTER | Returns the array of data in a range that satisfies multiple conditions. |
| LET | Returns the result of a formula that can use variables. |
| RANDARRAY | Returns the array of random numbers between 0 and 1. |
| SEQUENCE | Returns the array of sequential numbers. |
| SORT | Returns the array of data in a range that has been sorted. |
| SORTBY | Returns the array of data in a range that has been sorted based on the values in a corresponding range. |
| UNIQUE | Returns the array of unique values in a list, table or cell range. |
| XLOOKUP | Returns the value in the same row after finding a matching value in any column. |
| XMATCH | Returns the position of a value in a list, table or cell range. |
2019 New Functions
Excel 2019 introduced 6 new worksheet functions.
| CONCAT | Returns the text string that is a concatenation of several strings. Replaces CONCATENATE |
| IFS | Returns the value based on multiple conditions you specify. |
| MAXIFS | Returns the largest value in a list or array of numbers that satisfies multiple conditions. |
| MINIFS | Returns the smallest value in a list or array of numbers that satisfies multiple conditions. |
| SWITCH | Returns the value based on a list of exact matches. |
| TEXTJOIN | Returns the text string that is a concatenation of several strings. |
2016 New Functions
Excel 2016 introduced 5 new worksheet functions.
| FORECAST.ETS | Returns a future value based on historical values. |
| FORECAST.ETS.CONFINT | Returns a confidence interval for a future value. |
| FORECAST.ETS.SEASONALITY | Returns the length of the repetitive seasonality pattern. |
| FORECAST.ETS.STAT | Returns a statistical value for a future value. |
| FORECAST.LINEAR | Returns the future y-value based on existing values. |
2013 New Functions
Excel 2013 introduced 50 new worksheet functions.
| ACOT | Returns the arc-cotangent of a number. |
| ACOTH | Returns the hyperbolic arc-cotangent of a number. |
| ARABIC | Returns the number converted from roman to arabic. |
| BASE | Returns the number converted into a text representation with the given base. |
| BINOM.DIST.RANGE | Returns the probablity of a trial result using a binomial distribution. |
| BITAND | Returns the bitwise and of two numbers. |
| BITLSHIFT | Returns a number shifted left by a number of bits. |
| BITOR | Returns the bitwise or of two numbers. |
| BITRSHIFT | Returns a number shifted right by a number of bits. |
| BITXOR | Returns a bitwise exclusive or of two numbers. |
| CEILING.MATH | Returns a number rounded up to the nearest integer or to the nearest significant figure. |
| COMBINA | Returns the number of combinations with repetitions for a given number of items. |
| COT | Returns the hyperbolic cosine of a number. |
| COTH | Returns the cotangent of an angle. |
| CSC | Returns the cosecant of an angle. |
| CSCH | Returns the hyperbolic cosecant of an angle. |
| DAYS | Returns the number of days between two dates. |
| DECIMAL | Returns the decimal number of a text representation of a number in a given base. |
| ENCODEURL | Returns a url encoded string. |
| FILTERXML | Returns specific data from the XML content by using Xpath. |
| FLOOR.MATH | Returns a number rounded to the nearest integer in a given base into a decimal number. |
| FORMULATEXT | Returns the formula from a particular cell. |
| GAMMA | Returns the gamma function value. |
| GAUSS | Returns 0.5 less than the standard normal cumulative distribution. |
| IFNA | Returns the boolean value depending if the value is #N/A. |
| IMCOSH | Returns the hyperbolic cosine of a complex number. |
| IMCOT | Returns the cotangent of a complex number. |
| IMCSC | Returns the cosecant of a complex number. |
| IMCSCH | Returns the hyperbolic cosecant of a complex number. |
| IMSEC | Returns the secant of a complex number. |
| IMSECH | Returns the hyperbolic secant of a complex number. |
| IMSINH | Returns the hyperbolic sine of a complex number. |
| IMTAN | Returns the tangent of a complex number. |
| ISOWEEKNUM | Returns the number of the ISO week of the year for a given date. |
| MUNIT | Returns the unit matrix or the specified dimension. |
| NUMBERVALUE | Returns the text to number in a locale independent manner. |
| PDURATION | Returns the number of periods required by an investment to reach a specified value. |
| PERMUTATIONA | Returns the number of permutations for a subset of objects or events (with repetition). |
| PHI | Returns the valueof the density function for a standard normal distribution. |
| RRI | Returns an equivalent interest rate for the growth of an investment. |
| SEC | Returns the secant of an angle. |
| SECH | Returns the hyperbolic secant of an angle. |
| SHEET | Returns the sheet number of the referenced sheet. |
| SHEETS | Returns the number of sheets in a reference. |
| SKEW.P | Returns the skewness of a distribution based on a population. |
| UNICHAR | Returns the unicode character that is references by the given numeric value. |
| UNICODE | Returns the number that corresponds to the first character of the text. |
| WEBSERVICE | Returns data from a webservice. |
| XOR | Returns the logical exclusive or for any number of arguments. |
2010 New Functions
Excel 2010 introduced 63 new worksheet functions.
| AGGREGATE | Returns an aggregate in a list or database. Replaces SUBTOTAL |
| ASC | Returns the text string converted from double byte to single byte characters. |
| BETA.DIST | Returns the cumulative beta probability density function. Replaces BETADIST |
| BETA.INV | Returns the inverse of the cumulative beta probability density function. Replaces BETAINV |
| BINOM.DIST | Returns the individual term binomial distribution probability. Replaces BINOMDIST |
| BINOM.INV | Returns the smallest number which is the cumulative binomial distribution that is greater than a criterion value. Replaces CRITBINOM |
| CEILING.PRECISE | Returns the number rounded up to the nearest integer or significant figure. Replaces CEILING |
| CHISQ.DIST | Returns the cumulative beta probability density function. |
| CHISQ.DIST.RT | Returns the one tailed probability of the chi-squared distribution. Replaces CHIDIST |
| CHISQ.INV | Returns the random variable where the probability of getting ‘less than’ it is P. |
| CHISQ.INV.RT | Returns the inverse of the one tailed probability of the chi-squared distribution. Replaces CHIINV |
| CHISQ.TEST | Returns the probability value from a chi-squared distribution. Replaces CHITEST |
| CONFIDENCE.NORM | Returns the confidence interval for a population mean. Replaces CONFIDENCE |
| CONFIDENCE.T | Returns the confidence interval for a population mean, using a Student’s t distribution. |
| COVARIANCE.P | Returns the covariance of two lists of numbers. Replaces COVAR |
| COVARIANCE.S | Returns the sample covariance, the average of the products deviations for each data point pair in two data sets. |
| DBCS | Returns the text string converted from single byte to double byte characters. |
| ERF.PRECISE | Returns the error function. |
| ERFC.PRECISE | Returns the complementary ERF function integrated between x and infinity. |
| EXPON.DIST | Returns the exponential distribution. Replaces EXPONDIST |
| F.DIST | Returns the F probability distribution. Replaces FDIST |
| F.DIST.RT | Returns the F probability distribution. Replaces FDIST |
| F.INV | Returns the inverse of the F probability distribution. Replaces FINV |
| F.INV.RT | Returns the inverse of the F probability distribution. Replaces FINV |
| F.TEST | Returns the probability value from an F distribution. Replaces FTEST |
| FLOOR.PRECISE | Returns the number rounded down to the nearest integer or significant figure. Replaces FLOOR |
| GAMMA.DIST | Returns the gamma distribution. Replaces GAMMADIST |
| GAMMA.INV | Returns the inverse of the gamma distribution. Replaces GAMMAINV |
| GAMMALN.PRECISE | Returns the natural logarithm of the gamma function, G(x). Replaces GAMMALN |
| HYPGEOM.DIST | Returns the hyper geometric distribution for a finite population. Replaces HYPGEOMDIST |
| ISO.CEILING | Rounds a number up to the nearest integer or to the nearest multiple of significance. |
| LOGNORM.DIST | Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard_dev. Replaces LOGNORMDIST |
| LOGNORM.INV | Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard_dev. Replaces LOGINV |
| MODE.MULT | Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data. |
| MODE.SNGL | Returns the number that occurs most frequently in a range. Replaces MODE |
| NEGBINOM.DIST | Returns the negative binomial distribution. Replaces NEGBINOMDIST |
| NETWORKDAYS.INTL | Returns the total number of working days between two dates excluding weekends and holidays. Replaces NETWORKDAYS |
| NORM.DIST | Returns the normal cumulative distribution. Replaces NORMDIST |
| NORM.INV | Returns the inverse of the normal cumulative distribution. Replaces NORMINV |
| NORM.S.DIST | Returns the standard normal cumulative distribution. Replaces NORMSDIST |
| NORM.S.INV | Returns the inverse of the standard normal cumulative distribution. Replaces NORMSINV |
| PERCENTILE.EXC | Returns the Kth percentile of values in a range, where k is in the range 0..1, exclusive. |
| PERCENTILE.INC | The Kth percentile of values in an array of numbers. Replaces PERCENTILE |
| PERCENTRANK.EXC | Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set. |
| PERCENTRANK.INC | Returns the rank of a value in a data set as a percentage of the data set. Replaces PERCENTRANK |
| POISSON.DIST | Returns the Poisson distribution. Replaces POISSON |
| QUARTILE.EXC | Returns the quartile of the data set, based on percentile values from 0..1, exclusive. |
| QUARTILE.INC | Returns the quartile of a data set. Replaces QUARTILE |
| RANK.AVG | Returns the rank of a value in a list, table or cell range (in descending order) (average ranking). |
| RANK.EQ | Returns the rank of a number in a list of numbers. Replaces RANK |
| STDEV.P | Returns the standard deviation based on the entire population. Replaces STDEVP |
| STDEV.S | Returns the standard deviation based on a sample. Replaces STDEV |
| T.DIST | Returns the Percentage Points (probability) for the Student’s t-distribution. |
| T.DIST.2T | Returns the two-tailed Student’s t-distribution. Replaces TDIST |
| T.DIST.RT | Returns the right-tailed Student’s t-distribution. Replaces TDIST |
| T.INV | Returns the T-value of the Student’s t-distribution as a function of the probability and the degrees of freedom. |
| T.INV.2T | Returns the t-value of the Student’s t-distribution as a function of the probability and the degrees of freedom. Replaces TINV |
| T.TEST | Returns the probability value from a T distribution. Replaces TTEST |
| VAR.P | Returns the variance based on the entire population. Replaces VARP |
| VAR.S | Returns the variance based on a sample. Replaces VAR |
| WEIBULL.DIST | Returns the Weibull distribution. Replaces WEIBULL |
| WORKDAY.INTL | Returns the serial number that is a given number of days before or after a date. Replaces WORKDAY |
| Z.TEST | Returns the probability value from a Normal distribution. Replaces ZTEST |
2007 New Functions
Excel 2007 introduced 12 new worksheet functions.
| AVERAGEIF | Returns the average of all the numbers in a range of cells that satisfy a condition. |
| AVERAGEIFS | Returns the average of all the numbers in a range of cells that satisfy multiple conditions. |
| COUNTIFS | Returns the number of cells with a value that satisfy multiple conditions. |
| CUBEKPIMEMBER | Returns a key performance indicator (KPI) property and displays the KPI name. |
| CUBEMEMBER | Returns a member or tuple from the cube. |
| CUBEMEMBERPROPERTY | Returns the value of a member property from the cube. |
| CUBERANKEDMEMBER | Returns the nth or ranked member in a set. |
| CUBESET | Returns the calculated members returned from a cube. |
| CUBESETCOUNT | Returns the number of items in a set. |
| CUBEVALUE | Returns an aggregated value from the cube. |
| IFERROR | Returns a value or if that value generates an error it returns a different value. |
| SUMIFS | Returns the total of all the numbers in a range of cells that satisfy multiple conditions. |
2016 New Functions
333 built-in worksheet functions (238 + 95).
The Analysis ToolPak add-in contained 95 functions.
| ABS | The absolute value or a number without its sign. |
| ACCRINT | (Analysis ToolPak – Financial) The accrued interest for a security that pays interest periodically. |
| ACCRINTM | (Analysis ToolPak – Financial) The accrued interest for a security that pays interest at maturity. |
| ACOS | The arc-cosine of a number. |
| ACOSH | The hyperbolic arc-cosine of a number. |
| ADDRESS | The cell reference given a row and column number. |
| AMORDEGRC | (Analysis ToolPak – Financial) The depreciation of an asset in a single period (straight-line, implicit coefficient). |
| AMORLINC | (Analysis ToolPak – Financial) The depreciation of an asset in a single period (straight-line). |
| AND | The logical AND for any number of arguments. |
| AREAS | The number of areas in a cell range or reference. |
| ASC | (Analysis ToolPak – Text) The text string converted from double byte to single byte characters. |
| ASIN | The arc-sine of a number. |
| ASINH | The hyperbolic arc-sine of a number. |
| ATAN | The arc-tangent of a number. |
| ATAN2 | The arc-tangent of the specified x and y co-ordinates. |
| ATANH | The hyperbolic arc-tangent of a number. |
| AVEDEV | The average deviation of the numbers in a list, table or cell range. |
| AVERAGE | The arithmetic mean of non blank cells in a list, table or cell range. |
| AVERAGEA | The arithmetic mean of non blank cells in a list, table or cell range (including logical values and text). |
| BAHTTEXT | (New) The number converted to a Thai text string and appends ‘Baht’. |
| BESSELI | (Analysis ToolPak – Engineering) The Bessel function evaluated for purely imaginary arguments. |
| BESSELJ | (Analysis ToolPak – Engineering) The modified Bessel function represented by J(x). |
| BESSELK | (Analysis ToolPak – Engineering) The modified Bessel function represented by K(x). |
| BESSELY | (Analysis ToolPak – Engineering) The modified Bessel function represented by Y(x). |
| BETADIST | (BETA.DIST) The cumulative probability function for a beta distribution (only cumulative). |
| BETAINV | (BETA.INV) The inverse of the probability distribution function for a beta distribution. |
| BIN2DEC | (Analysis ToolPak – Engineering) The number converted from binary to decimal. |
| BIN2HEX | (Analysis ToolPak – Engineering) The number converted from binary to hexadecimal. |
| BIN2OCT | (Analysis ToolPak – Engineering) The number converted from binary to octal. |
| BINOMDIST | (BINOM.DIST) The probability of getting a specific number of successes using a binomial distribution. |
| CEILING | (CEILING.MATH) The number rounded up to the nearest integer or significant figure. |
| CELL | The text string indicating information about a cell. |
| CHAR | The character with the corresponding ANSI/ASCII number. |
| CHIDIST | (CHISQ.DIST.RT) The probability distribution function for a chi-squared distribution (right tailed) (no cumulative). |
| CHIINV | (CHISQ.INV.RT) The inverse of the probability distribution function for a chi-squared distribution (right tailed). |
| CHITEST | (CHISQ.TEST) The probability value from a chi-squared distribution. |
| CHOOSE | The value in a row (or column) based on an index number. |
| CLEAN | The text string with all the non-printable characters removed. |
| CODE | The ANSI/ASCII number for the first character in a text string. |
| COLUMN | The column number of a cell reference. |
| COLUMNS | The number of columns in a cell range or reference. |
| COMBIN | The number of combinations for a given number of items (without repetition). |
| COMPLEX | (Analysis ToolPak – Engineering) The complex number given real and imaginary coefficients. |
| CONCATENATE | (CONCAT) The text string that is a concatenation of several strings. |
| CONFIDENCE | (CONFIDENCE.NORM) The confidence interval for a population mean. |
| CONVERT | (Analysis ToolPak – Engineering) The number in one measurement system converted to another. |
| CORREL | The correlation coefficient between two data sets. |
| COS | The cosine of a number. |
| COSH | The hyperbolic cosine of a number. |
| COUNT | The number of numerical values in a list, table or cell range. |
| COUNTA | The number of numerical values in a list, table or cell range (including logical values and text). |
| COUNTBLANK | The number of blank cells in a list, table or cell range. |
| COUNTIF | (COUNTIFS) The number of numerical values in a range that satisfies one condition. |
| COUPDAYBS | (Analysis ToolPak – Financial) The number of days between the previous coupon date and the settlement date. |
| COUPDAYS | (Analysis ToolPak – Financial) The number of days between the coupon dates on either side of the settlement date. |
| COUPDAYSNC | (Analysis ToolPak – Financial) The number of days between the settlement date and the next coupon date. |
| COUPNCD | (Analysis ToolPak – Financial) The next coupon date after the settlement date. |
| COUPNUM | (Analysis ToolPak – Financial) The number of coupons between the settlement date and the maturity date. |
| COUPPCD | (Analysis ToolPak – Financial) The previous coupon date before the settlement date. |
| COVAR | (COVARIANCE.P) The average of the products of deviations for each data point pair. |
| CRITBINOM | (BINOM.INV) The inverse of the probability distribution function for a binomial distribution (renamed). |
| CUMIPMT | (Analysis ToolPak – Financial) The cumulative interest paid on a loan between two dates. |
| CUMPRINC | (Analysis ToolPak – Financial) The cumulative principal paid on a loan between two dates. |
| DATE | The date as a date serial number given a year, month, day. |
| DATEDIF | (Compatibility) The number of days, months or years between two dates. |
| DATEVALUE | The date serial number given a date in text format. |
| DAVERAGE | The arithmetic mean of non blank cells in a database column satisfying certain conditions. |
| DAY | The day as an integer given a date serial number. |
| DAYS360 | The number of days between two dates, based on 30 day months. |
| DB | The depreciation of an asset in a single period (declining balance method). |
| DCOUNT | The total number of non blank cells in a database column satisfying certain conditions. |
| DCOUNTA | The total number of non blank cells in a database column satisfying certain conditions (including logical values and text). |
| DDB | The depreciation of an asset in a single period (double or triple declining balance method). |
| DEC2BIN | (Analysis ToolPak – Engineering) The number converted from decimal to binary. |
| DEC2HEX | (Analysis ToolPak – Engineering) The number converted from decimal to hexadecimal. |
| DEC2OCT | (Analysis ToolPak – Engineering) The number converted from decimal to octal. |
| DEGREES | The number of degrees given a number of radians. |
| DELTA | (Analysis ToolPak – Engineering) The numerical value indicating if two numbers are equal. |
| DEVSQ | The sum of the squared deviations from the mean. |
| DGET | The single value in a database column satisfying certain conditions. |
| DISC | (Analysis ToolPak – Financial) The interest rate (or discount rate) for a security held to maturity. |
| DMAX | The maximum value in a database column satisfying certain conditions. |
| DMIN | The minimum value in a database column satisfying certain conditions. |
| DOLLAR | The text string of a number with the dollar formatting $0,000.00. |
| DOLLARDE | (Analysis ToolPak – Financial) The dollar fraction expressed as a decimal. |
| DOLLARFR | (Analysis ToolPak – Financial) The dollar decimal expressed as a fraction. |
| DPRODUCT | The product of values in a database column satisfying certain conditions. |
| DSTDEV | The standard deviation of a database column satisfying certain conditions based on a sample. |
| DSTDEVP | The standard deviation of a database column satisfying certain conditions based on an entire population. |
| DSUM | The sum of values in a database column satisfying certain conditions. |
| DURATION | (Analysis ToolPak – Financial) The annual duration of a security that pays interest periodically. |
| DVAR | The variance of a database column satisfying certain conditions based on a sample. |
| DVARP | The variance of a database column satisfying certain conditions based on an entire population. |
| EDATE | (Analysis ToolPak – Date & Time) The date serial number that is a certain number of months before or after a date. |
| EFFECT | (Analysis ToolPak – Financial) The effective interest rate given a nominal interest rate and compounding frequency. |
| EOMONTH | (Analysis ToolPak – Date & Time) The date serial number of the last day of a month before or after a date. |
| ERF | (Analysis ToolPak – Engineering) The value of the error function integrated between two limits. |
| ERFC | (Analysis ToolPak – Engineering) The complementary error function integrated between a limit and infinity. |
| ERROR.TYPE | The number corresponding to a particular error value in a cell. |
| EUROCONVERT | (Euro Currency Add-in) The number converted to euros. |
| EVEN | The number rounded up to the nearest even integer. |
| EXACT | The value True or False based on whether two strings match exactly. |
| EXP | The exponential number raised to a particular power. |
| EXPONDIST | (EXPON.DIST) The probability distribution function OR the cumulative probability function for an exponential distribution. |
| FACT | The factorial of a positive whole number. |
| FACTDOUBLE | (Analysis ToolPak – Maths & Trigonometry) The double factorial of a positive whole number. |
| FALSE | The logical value False. |
| FDIST | (F.DIST) The probability distribution function for an f distribution (left tailed) (no cumulative). |
| FIND | The starting position of a substring within a larger text string. |
| FINV | (F.INV) The inverse of the probability distribution function for an f distribution (left tailed). |
| FISHER | The fisher transformation. |
| FISHERINV | The inverse of the fisher transformation. |
| FIXED | The text string of a number rounded to a fixed number of decimal places. |
| FLOOR | (FLOOR.MATH) The number rounded down to the nearest integer or significant figure. |
| FORECAST | (FORECAST.LINEAR) The future y-value along a linear trend using existing values. |
| FREQUENCY | The number of times a particular value occurs in a list, table or cell range. |
| FTEST | (F.TEST) The probability value from an f distribution. |
| FV | The future value of a series of equal cash flows at regular intervals. |
| FVSCHEDULE | (Analysis ToolPak – Financial) The future value of an initial principal after applying compound interest rates. |
| GAMMADIST | (GAMMA.DIST) The probability distribution function OR the cumulative probability function for a gamma distribution. |
| GAMMAINV | (GAMMA.INV) The inverse of the probability distribution for a gamma distribution. |
| GAMMALN | (GAMMALN.PRECISE) The natural logarithm of the gamma function. |
| GCD | (Analysis ToolPak – Maths & Trigonometry) The greatest common divisor of two or more numbers. |
| GEOMEAN | The geometric mean of an array of numbers. |
| GESTEP | (Analysis ToolPak – Engineering) The value True or False depending if the number is greater than a threshold value. |
| GETPIVOTDATA | The data obtained from a pivot table. |
| GROWTH | The predicted exponential growth using existing data. |
| HARMEAN | The harmonic mean of a data set. |
| HEX2BIN | (Analysis ToolPak – Engineering) The number converted from hexadecimal to binary. |
| HEX2DEC | (Analysis ToolPak – Engineering) The number converted from hexadecimal to decimal. |
| HEX2OCT | (Analysis ToolPak – Engineering) The number converted from hexadecimal to octal. |
| HLOOKUP | The value in the same column after finding a matching value in the first row. |
| HOUR | The hour as an integer given a date serial number. |
| HYPERLINK | The hyperlink linked to a cell, document or webpage. |
| HYPGEOMDIST | (HYPGEOM.DIST) The cumulative probability function for a hypergeometric distribution (only cumulative). |
| IF | The value based on whether a condition is True or False. |
| IMABS | (Analysis ToolPak – Engineering) The absolute value of a complex number. |
| IMAGINARY | (Analysis ToolPak – Engineering) The imaginary coefficient of a complex number. |
| IMARGUMENT | (Analysis ToolPak – Engineering) The theta argument in radians. |
| IMCONJUGATE | (Analysis ToolPak – Engineering) The complex conjugate of a complex number. |
| IMCOS | (Analysis ToolPak – Engineering) The cosine of a complex number. |
| IMDIV | (Analysis ToolPak – Engineering) The quotient of two complex numbers. |
| IMEXP | (Analysis ToolPak – Engineering) The exponential of a complex number. |
| IMLN | (Analysis ToolPak – Engineering) The natural logarithm of a complex number. |
| IMLOG10 | (Analysis ToolPak – Engineering) The base-10 logarithm of a complex number. |
| IMLOG2 | (Analysis ToolPak – Engineering) The base-2 logarithm of a complex number. |
| IMPOWER | (Analysis ToolPak – Engineering) The complex number raised to an integer number. |
| IMPRODUCT | (Analysis ToolPak – Engineering) The product of two or more complex numbers. |
| IMREAL | (Analysis ToolPak – Engineering) The real coefficient of a complex number. |
| IMSIN | (Analysis ToolPak – Engineering) The sine of a complex number. |
| IMSQRT | (Analysis ToolPak – Engineering) The square root of a complex number. |
| IMSUB | (Analysis ToolPak – Engineering) The difference of two complex numbers. |
| IMSUM | (Analysis ToolPak – Engineering) The sum of two or more complex numbers. |
| INDEX | The value from a cell range which is the intersection of a row AND a column. |
| INDIRECT | The text string of the contents of a given cell reference. |
| INFO | The text string returning useful information about the environment. |
| INT | The number rounded down to the nearest integer. |
| INTERCEPT | The intersection with the y-axis using a linear regression plotted through known values. |
| INTRATE | (Analysis ToolPak – Financial) The interest rate for a security held to maturity. |
| IPMT | The interest amount paid on a given period on a loan with fixed interest. |
| IRR | The interest rate for a series of unequal cash flows at regular intervals (implicit reinvestment rate). |
| ISBLANK | The boolean True or False depending if the value is blank. |
| ISERR | The boolean True or False depending if the value is an error (not #N/A). |
| ISERROR | The boolean True or False depending if the value is an error. |
| ISEVEN | (Analysis ToolPak – Information) The boolean True or False depending if the value is an even number. |
| ISLOGICAL | The boolean True or False depending if the value is True or False. |
| ISNA | The boolean True or False depending if the value is #N/A. |
| ISNONTEXT | The boolean True or False depending if the value is non text. |
| ISNUMBER | The boolean True or False depending if the value is a number. |
| ISODD | (Analysis ToolPak – Information) The boolean True or False depending if the value is an odd number. |
| ISPMT | (IPMT) The interest paid for a given period in a series of equal cash flows at regular intervals (incorrectly). |
| ISREF | The boolean True or False depending if the value is a cell reference. |
| ISTEXT | The boolean True or False depending if the value is text. |
| JIS | (New) The text string converted from single byte to double byte characters (renamed). |
| KURT | The kurtosis for a list or array of numbers. |
| LARGE | The kth largest value in a list or array of numbers. |
| LCM | (Analysis ToolPak – Maths & Trigonometry) The least common multiple of two or more numbers. |
| LEFT | The first or left most characters in a text string. |
| LEN | The number of characters in a text string. |
| LINEST | The array of values for a straight line that best fits your data. |
| LN | The natural logarithm of a number. |
| LOG | The logarithm of a number to any base. |
| LOG10 | The logarithm of a number to the base 10. |
| LOGEST | The array of values for an exponential curve that best fits your data. |
| LOGINV | (LOGNORM.INV) The inverse of the probability distribution function for a lognormal distribution. |
| LOGNORMDIST | (LOGNORM.DIST) The cumulative probability function for a lognormal distribution (only cumulative). |
| LOOKUP | The value in a row (or column) that matches a value in a column (or row). |
| LOWER | The text string with all the characters converted to lowercase. |
| MATCH | The position of a value in a list, table or cell range. |
| MAX | The largest value in a list or array of numbers. |
| MAXA | The largest value in a list or array of numbers (including logical values and text). |
| MDETERM | The matrix determinant of an array. |
| MDURATION | (Analysis ToolPak – Financial) The modified duration for a security that pays interest periodically. |
| MEDIAN | The median value in a list or array of numbers. |
| MID | The characters from the middle of a text string. |
| MIN | The smallest value in a list or array of numbers. |
| MINA | The smallest value in a list or array of numbers (including logical values and text). |
| MINUTE | The minute as an integer given a date serial number. |
| MINVERSE | The inverse matrix of an array. |
| MIRR | The interest rate for a series of unequal cash flows at regular intervals (explicit reinvestment rate). |
| MMULT | The matrix product of two arrays. |
| MOD | The remainder after division. |
| MODE | (MODE.SNGL) The value that occurs most frequently in a list or array of numbers. |
| MONTH | The month as an integer given a date serial number. |
| MROUND | (Analysis ToolPak – Maths & Trigonometry) The number rounded to the desired multiple. |
| MULTINOMIAL | (Analysis ToolPak – Maths & Trigonometry) The multinomial of a set of numbers in a list or cell range. |
| N | The value converted to a number. |
| NA | The error value #N/A. |
| NEGBINOMDIST | (NEGBINOM.DIST) The probability distribution function for a negative binomial distribution (no cumulative). |
| NETWORKDAYS | (Analysis ToolPak – Date & Time) The total number of working days between two dates excluding weekends and holidays. |
| NOMINAL | (Analysis ToolPak – Financial) The nominal interest rate over a period given an annual interest rate. |
| NORMDIST | (NORM.DIST) The probability distribution function OR the cumulative probability function for a normal distribution. |
| NORMINV | (NORM.INV) The inverse of the probability distribution function for a normal distribution. |
| NORMSDIST | (NORM.S.DIST) The probability distribution function for a standard normal distribution (no cumulative). |
| NORMSINV | (NORM.S.INV) The inverse of the probability distribution function for a standard normal distribution. |
| NOT | The opposite of a True or False value. |
| NOW | The date serial number of the current system date and time. |
| NPER | The number of periods for an investment. |
| NPV | The net present value of a series of unequal cash flows at regular intervals. |
| OCT2BIN | (Analysis ToolPak – Engineering) The number converted from octal to binary. |
| OCT2DEC | (Analysis ToolPak – Engineering) The number converted from octal to decimal. |
| OCT2HEX | (Analysis ToolPak – Engineering) The number converted from octal to hexadecimal. |
| ODD | The number rounded up to the nearest odd integer. |
| ODDFPRICE | (Analysis ToolPak – Financial) The price per $100 face value of a security with an odd first period. |
| ODDFYIELD | (Analysis ToolPak – Financial) The yield of a security with an odd first period. |
| ODDLPRICE | (Analysis ToolPak – Financial) The price per $100 face value of a security with an odd last period. |
| ODDLYIELD | (Analysis ToolPak – Financial) The yield of a security with an odd last period. |
| OFFSET | The value in a cell which is an offset from another cell. |
| OR | The logical OR for any number of arguments. |
| PEARSON | The pearson product moment correlation coefficient. |
| PERCENTILE | (PERCENTILE.INC) The number corresponding to a particular percentage from an array of numbers (inclusive). |
| PERCENTRANK | (PERCENTRANK.INC) The percentage rank of a value in an array of numbers (inclusive). |
| PERMUT | The number of permutations for a subset of objects or events. |
| PHONETIC | (New) The phonetic characters from a text string. |
| PI | The number PI (3.141592). |
| PMT | (Analysis ToolPak – Financial) The full amount (principal + interest) paid every period on a loan with fixed interest. |
| POISSON | (POISSON.DIST) The probability distribution function OR the cumulative probability function for a poisson distribution. |
| POWER | The number raised to a given power. |
| PPMT | The principal amount paid on a given period on a loan with fixed interest. |
| PRICE | (Analysis ToolPak – Financial) The price of a security that pays periodic interest. |
| PRICEDISC | (Analysis ToolPak – Financial) The price of a discounted security (no interest payments). |
| PRICEMAT | (Analysis ToolPak – Financial) The price of a security that pays interest at maturity. |
| PROB | The probability that values in a range are between two limits. |
| PRODUCT | The product of all the numbers in a list or cell range. |
| PROPER | The text string with the first letter of every word as a capital letter. |
| PV | The present value of a series of equal cash flows at regular intervals. |
| QUARTILE | (QUARTILE.INC) The quartile of a data set (inclusive). |
| QUOTIENT | (Analysis ToolPak – Maths & Trigonometry) The integer portion after division. |
| RADIANS | The number of radians given the number of degrees. |
| RAND | The random number >=0 and <1. |
| RANDBETWEEN | (Analysis ToolPak – Maths & Trigonometry) The random number between two specified numbers (inclusive). |
| RANK | (RANK.EQ) The rank of a value in a list, table or cell range (in descending order) (equal ranking). |
| RATE | The interest rate for a series of equal cash flows at regular intervals. |
| RECEIVED | (Analysis ToolPak – Financial) The amount received at the end when a security is held to maturity. |
| REPLACE | The text string after replacing characters in a specific location. |
| REPT | The text string repeated a number of times. |
| RIGHT | The last or right most characters in a text string. |
| ROMAN | The text string converting a binary number to a roman numeral. |
| ROUND | The number rounded to a specified number of digits. |
| ROUNDDOWN | The number rounded down to the nearest integer. |
| ROUNDUP | The number rounded up to the nearest integer. |
| ROW | The row number of a cell reference. |
| ROWS | The number of rows in a cell range or reference. |
| RSQ | The square of pearson product moment correlation coefficient through data points in known y’s and known x’s. |
| RTD | (New) The real time data from a program that supports COM. |
| SEARCH | The starting position of a substring within a larger text string. |
| SECOND | The number of seconds as an integer given a date serial number. |
| SERIESSUM | (Analysis ToolPak – Maths & Trigonometry) The sum of a power series based on a formula. |
| SIGN | The numerical value indicating if a number is positive or negative. |
| SIN | The sine of a number. |
| SINH | The hyperbolic sine of a number. |
| SKEW | The number representing the skewness of a distribution based on a sample. |
| SLN | The depreciation of an asset in a single period (straight-line method). |
| SLOPE | The slope of a linear regression line through the given data points. |
| SMALL | The kth smallest value in a list or array of numbers. |
| SQRT | The positive square root of a number. |
| SQRTPI | (Analysis ToolPak – Maths & Trigonometry) The square root of a number multiplied by PI. |
| STANDARDIZE | The normalized value from a distributed characterised by a mean and a standard deviation. |
| STDEV | (STDEV.S) The standard deviation based on a sample. |
| STDEVA | The standard deviation based on a sample (including logical values and text). |
| STDEVP | (STDEV.P) The standard deviation based on an entire population. |
| STDEVPA | The standard deviation based on an entire population (including logical values and text). |
| STEYX | The standard error of a regression. |
| SUBSTITUTE | The text string after replacing instances of a substring. |
| SUBTOTAL | (AGGREGATE) The subtotal of values in a list, table or cell range (renamed). |
| SUM | The total value of the numbers in a list, table or cell range. |
| SUMIF | (SUMIFS) The total value of the numbers that satisfies one condition. |
| SUMPRODUCT | The sum of the product of one or more arrays of values. |
| SUMSQ | The sum of the squares of all the values in a list, table or cell range. |
| SUMX2MY2 | The sum of the difference of squares of corresponding values in two arrays. |
| SUMX2PY2 | The sum of the sum of squares of corresponding values in two arrays. |
| SUMXMY2 | The sum of squares of differences of corresponding values in two arrays. |
| SYD | The depreciation of an asset in a single period (sum-of-years digits method). |
| T | The text string of the value given. |
| TAN | The tangent of a number. |
| TANH | The hyperbolic tangent of a number. |
| TBILLEQ | (Analysis ToolPak – Financial) The yield (bond-equivalent) for a treasury bill. |
| TBILLPRICE | (Analysis ToolPak – Financial) The price per $100 face value for a treasury bill. |
| TBILLYIELD | (Analysis ToolPak – Financial) The yield for a treasury bill. |
| TDIST | (T.DIST.2T) The percentage points probability for the t distribution. |
| TEXT | The number as a formatted text string. |
| TIME | The time as a decimal given an hour, minute, second. |
| TIMEVALUE | The time as a decimal given a time in text format. |
| TINV | (T.INV.2T) The t-value of the distribution as a function of the probability and the degrees of freedom. |
| TODAY | The date serial number representing today’s date. |
| TRANSPOSE | The array with its orientation changed. |
| TREND | The y-values along a linear trend given a set of x-values. |
| TRIM | The text string with all extra spaces removed from the beginning, middle and end. |
| TRIMMEAN | The mean of the interior of a data set. |
| TRUE | The logical value True. |
| TRUNC | The number with any decimal places removed. |
| TTEST | (T.TEST) The probability value from a t distribution. |
| TYPE | The number indicating the data type of the value. |
| UPPER | The text string with all the characters converted to uppercase. |
| VALUE | (NUMBERVALUE) The number that a text string represents (renamed). |
| VAR | (VAR.S) The variance based on a sample. |
| VARA | The variance based on a sample (including logical values and text). |
| VARP | (VAR.P) The variance based on an entire population. |
| VARPA | The variance based on an entire population (including logical values and text). |
| VDB | The depreciation of an asset in a single period (variable declining balance method). |
| VLOOKUP | The value in the same row after finding a matching value in the first column. |
| WEEKDAY | The day of the week for a given date. |
| WEEKNUM | (Analysis ToolPak – Date & Time) The week number in the year for a given date. |
| WEIBULL | (WEIBULL.DIST) The probability distribution function OR the cumulative probability function for a weibull distribution. |
| WORKDAY | (Analysis ToolPak – Date & Time) The date serial number that is a given number of working days before or after a date. |
| XIRR | (Analysis ToolPak – Financial) The interest rate for a series of unequal cash flows at irregular intervals (implicit reinvestment rate). |
| XNPV | (Analysis ToolPak – Financial) The net present value of a series of unequal cash flows at irregular intervals. |
| YEAR | The year as an integer given a date serial number. |
| YEARFRAC | (Analysis ToolPak – Date & Time) The number of years as a decimal between two dates. |
| YIELD | (Analysis ToolPak – Financial) The interest rate (annual) for a series of equal cash flows at regular intervals. |
| YIELDDISC | (Analysis ToolPak – Financial) The interest rate (annual) for a discounted security (no interest payments). |
| YIELDMAT | (Analysis ToolPak – Financial) The interest rate (annual) for a security that pays interest at maturity. |
| ZTEST | (Z.TEST) The probability that the supplied hypothesized sample mean is greater than the mean of the supplied data values. |
Backwards Compatibility
The functions listed below should not be used.
The functions listed below are only included to provide backwards compatibility.
Some of these functions were for compatibility with other spreadsheet programs.
The SINGLE function was removed in Excel 365.
The JIS function was removed in Excel 2013.
The functions in the Compatibility Category should only be used if you need compatibility with Excel 2007.
| AVERAGEIF | (AVERAGEIFS) The arithmetic mean of the numerical values that satisfies one condition. |
| CEILING | (CEILING.MATH) Returns the number rounded up to the nearest integer or significant figure. |
| CEILING.PRECISE | (CEILING.MATH) Returns the number rounded up to the nearest integer or significant figure. |
| CELL | Returns the text string indicating information about a cell. |
| COUNTIF | (COUNTIFS) The number of numerical values that satisfies one condition. |
| DATEDIF | Returns the number of days, months or years between two dates. |
| DATESTRING | Meant to return the system date in the MM-dd-yyyy format, using abbreviated month name. |
| ECMA.CEILING | (CEILING.MATH) Returns the number rounded up to the nearest multiple of significance. |
| FALSE | Returns the logical value False. |
| FLOOR | (FLOOR.MATH) Returns the number rounded down to the nearest integer or significant figure. |
| FLOOR.PRECISE | (FLOOR.MATH) Returns the number rounded down to the nearest integer or significant figure. |
| ISO.CEILING | (CEILING.MATH) Returns the number rounded up to the nearest integer or significant figure (renamed). |
| ISERR | (ISERROR) Returns the value True or False depending if the value is an error. |
| ISPMT | (IPMT) Returns the interest paid for a given period in a series of equal cash flows at regular intervals (incorrectly). |
| JIS | (Removed in 2013) (DBCS) Returns the text string converted from single byte to double byte characters (renamed). |
| N | Returns the value converted to a number. |
| NA | Returns the error value #N/A. |
| SINGLE | (Removed in 365) Returns the value from a cell range which is the intersection of a row OR a column. |
| SUBTOTAL | (AGGREGATE) The subtotal of values in a list, table or cell range (renamed). |
| SUMIF | (SUMIFS) The total of the numerical values that satisfies one condition. |
| T | Returns the text string of the value given. |
| TRUE | Returns the logical value True. |
| VALUE | (NUMBERVALUE) Returns the number that a text string represents (renamed). |
