What's new with functions in Excel 2011

To provide improved function accuracy, consistent functionality that meets expectations, and function names that more accurately describe their functionality, several functions have been updated, renamed, or added to the function library in Microsoft Excel for Mac 2011.

For backward compatibility, renamed functions remain available with their old name.

Algorithm and accuracy improvements

For the following functions, algorithm changes have been implemented to improve function accuracy and performance. For example, a new algorithm has been implemented for the BETADIST function to improve the accuracy of this function. The MOD function now uses new algorithms to achieve both accuracy and speed, and the RAND function now uses a new random number algorithm.

Improved function

Function category

Purpose

ASINH function

Math and trigonometry functions

Returns the inverse hyperbolic sine of a number

BETA.DIST function

Statistical functions

Returns the beta cumulative distribution function

BETADIST function

Compatibility functions

Returns the beta cumulative distribution function

BETA.INV function

Statistical functions

Returns the inverse of the cumulative distribution function for a specified beta distribution

BETAINV function

Compatibility functions

Returns the inverse of the cumulative distribution function for a specified beta distribution

BINOM.DIST function

Statistical functions

Returns the individual term binomial distribution probability

BINOMDIST function

Compatibility functions

Returns the individual term binomial distribution probability

BINOM.INV function

Statistical functions

Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value

CEILING function

Math and trigonometry functions

Rounds a number to the nearest integer or to the nearest multiple of significance

CRITBINOM function

Compatibility functions

Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value

CHISQ.INV.RT function

Statistical functions

Returns the inverse of the one-tailed probability of the chi-squared distribution

CHIINV function

Compatibility functions

Returns the inverse of the one-tailed probability of the chi-squared distribution

CHISQ.TEST function

Statistical functions

Returns the test for independence

CHITEST function

Compatibility functions

Returns the test for independence

CONVERT function

Engineering functions

Converts a number from one measurement system to another

CUMIPMT function

Financial functions

Returns the cumulative interest paid between two periods

CUMPRINC function

Financial functions

Returns the cumulative principal paid on a loan between two periods

ERF function

Engineering functions

Returns the error function

ERFC function

Engineering functions

Returns the complementary error function

F.DIST.RT function

Statistical functions

Returns the F probability distribution

FDIST function

Compatibility functions

Returns the F probability distribution

F.INV.RT function

Compatibility functions

Returns the inverse of the F probability distribution

FINV function

Statistical functions

Returns the inverse of the F probability distribution

FACTDOUBLE function

Math and trigonometry functions

Returns the double factorial of a number

FLOOR function

Math and trigonometry functions

Rounds a number down, toward zero

GAMMA.DIST function

Statistical functions

Returns the gamma distribution

GAMMADIST function

Compatibility functions

Returns the gamma distribution

GAMMA.INV function

Statistical functions

Returns the inverse of the gamma cumulative distribution

GAMMAINV function

Compatibility functions

Returns the inverse of the gamma cumulative distribution

GAMMALN function

Statistical functions

Returns the natural logarithm of the gamma function, Γ(x)

GEOMEAN function

Statistical functions

Returns the geometric mean

HYPGEOM.DIST function

Statistical functions

Returns the hypergeometric distribution

HYPGEOMDIST function

Compatibility functions

Returns the hypergeometric distribution

IMLOG2 function

Engineering functions

Returns the base-2 logarithm of a complex number

IMPOWER function

Engineering functions

Returns a complex number raised to an integer power

IPMT function

Financial functions

Returns the interest payment for an investment for a given period

IRR function

Financial functions

Returns the internal rate of return for a series of cash flows

LINEST function

Statistical functions

Returns the parameters of a linear trend

LOGNORM.DIST function

Compatibility functions

Returns the cumulative lognormal distribution

LOGNORMDIST function

Statistical functions

Returns the cumulative lognormal distribution

LOGNORM.INV function

Statistical functions

Returns the inverse of the lognormal cumulative distribution

LOGINV function

Compatibility functions

Returns the inverse of the lognormal cumulative distribution

MOD function

Math and trigonometry functions

Returns the remainder from division

NEGBINOM.DIST function

Statistical functions

Returns the negative binomial distribution

NEGBINOMDIST function

Compatibility functions

Returns the negative binomial distribution

NORM.DIST function

Statistical functions

Returns the normal cumulative distribution

NORMDIST function

Compatibility functions

Returns the normal cumulative distribution

NORM.INV function

Statistical functions

Returns the inverse of the normal cumulative distribution

NORMINV function

Compatibility functions

Returns the inverse of the normal cumulative distribution

NORM.S.DIST function

Statistical functions

Returns the standard normal cumulative distribution

NORMSDIST function

Compatibility functions

Returns the standard normal cumulative distribution

NORM.S.INV function

Statistical functions

Returns the inverse of the standard normal cumulative distribution

NORMSINV function

Compatibility functions

Returns the inverse of the standard normal cumulative distribution

PMT function

Financial functions

Returns the periodic payment for an annuity

PPMT function

Financial functions

Returns the payment on the principal for an investment for a given period

POISSON.DIST function

Statistical functions

Returns the Poisson distribution

POISSON function

Compatibility functions

Returns the Poisson distribution

RAND function

Math and trigonometry functions

Returns a random number between 0 and 1

STDEV.S function

Statistical functions

Estimates standard deviation based on a sample

DSTDEV function

Compatibility functions

Estimates standard deviation based on a sample

T.DIST.RT function

Statistical functions

Returns the Student's t-distribution

TDIST function

Compatibility functions

Returns the Student's t-distribution

T.DIST.2T function

Statistical functions

Returns the Percentage Points (probability) for the Student t-distribution

T.INV.2T function

Statistical functions

Returns the inverse of the Student's t-distribution

TINV function

Compatibility functions

Returns the inverse of the Student's t-distribution

VAR.S function

Statistical functions

Estimates variance based on a sample

VAR function

Compatibility functions

Estimates variance based on a sample

XIRR function

Financial functions

Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic

Accurate and consistent function names

The following functions have new names that are more consistent with the function definitions of the scientific community and with other function names in Excel. The new function names also more accurately describe their functionality. For example, because the CRITBINOM function returns the inverse of the binomial distribution, BINOM.INV is a more appropriate name for this function.

Some renamed functions, such as BETA.DIST, have an additional parameter so you can specify the type of distribution (left-tail cumulative or probability density).

For backward compatibility with earlier versions of Excel, the functions are still available with their old name in the compatibility functions category. However, if backward compatibility is not required, you should start using the renamed functions instead.

Renamed function

Function category

Purpose

BETA.DIST function

Statistical functions

Returns the beta cumulative distribution function

BETA.INV function

Statistical functions

Returns the inverse of the cumulative distribution function for a specified beta distribution

BINOM.DIST function

Statistical functions

Returns the individual term binomial distribution probability

BINOM.INV function

Statistical functions

Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value

CEILING.PRECISE function

Math and trigonometry functions

Rounds a number to the nearest integer or to the nearest multiple of significance

CHISQ.DIST function

Statistical functions

Returns the cumulative beta probability density function

CHISQ.DIST.RT function

Statistical functions

Returns the one-tailed probability of the chi-squared distribution

CHISQ.INV function

Statistical functions

Returns the cumulative beta probability density function

CHISQ.INV.RT function

Statistical functions

Returns the inverse of the one-tailed probability of the chi-squared distribution

CHISQ.TEST function

Statistical functions

Returns the test for independence

CONFIDENCE.NORM function

Statistical functions

Returns the confidence interval for a population mean

CONFIDENCE.T function

Statistical functions

Returns the confidence interval for a population mean, using a Student's t distribution

COVARIANCE.P function

Statistical functions

Returns covariance, the average of the products of paired deviations

COVARIANCE.S function

Statistical functions

Returns the sample covariance, the average of the products deviations for each data point pair in two data sets

ERF.PRECISE function

Engineering functions

Returns the error function

ERFC.PRECISE function

Engineering functions

Returns the complementary ERF function integrated between x and infinity

EXPON.DIST function

Statistical functions

Returns the exponential distribution

F.DIST function

Statistical functions

Returns the F probability distribution

F.DIST.RT function

Statistical functions

Returns the F probability distribution

F.INV function

Statistical functions

Returns the inverse of the F probability distribution

F.INV.RT function

Statistical functions

Returns the inverse of the F probability distribution

F.TEST function

Statistical functions

Returns the result of an F-test

FLOOR.PRECISE function

Math and trigonometry functions

Rounds a number the nearest integer or to the nearest multiple of significance; regardless of the sign of the number, the number is rounded up

GAMMA.DIST function

Statistical functions

Returns the gamma distribution

GAMMA.INV function

Statistical functions

Returns the inverse of the gamma cumulative distribution

GAMMALN.PRECISE function

Statistical functions

Returns the natural logarithm of the gamma function, Γ(x)

HYPGEOM.DIST function

Statistical functions

Returns the hypergeometric distribution

ISO.CEILING function

Math and trigonometry functions

Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance

LOGNORM.DIST function

Statistical functions

Returns the cumulative lognormal distribution

LOGNORM.INV function

Statistical functions

Returns the inverse of the lognormal cumulative distribution

MODE.MULT function

Statistical functions

Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data

MODE.SNGL function

Statistical functions

Returns the most common value in a data set

NEGBINOM.DIST function

Statistical functions

Returns the negative binomial distribution

NETWORKDAYS.INTL function

Date and time functions

Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days

NORM.DIST function

Statistical functions

Returns the normal cumulative distribution

NORM.INV function

Statistical functions

Returns the inverse of the normal cumulative distribution

NORM.S.DIST function

Statistical functions

Returns the standard normal cumulative distribution

NORM.S.INV function

Statistical functions

Returns the inverse of the standard normal cumulative distribution

PERCENTILE.EXC function

Statistical functions

Returns the k-th percentile of values in a range, where k is in the range 0 to 1, exclusive

PERCENTILE.INC function

Statistical functions

Returns the k-th percentile of values in a range

PERCENTRANK.EXC function

Statistical functions

Returns the rank of a value in a data set as a percentage (0 to 1, exclusive) of the data set

PERCENTRANK.INC function

Statistical functions

Returns the percentage rank of a value in a data set

POISSON function

Statistical functions

Returns the Poisson distribution

QUARTILE.EXC function

Statistical functions

Returns the quartile of the data set, based on percentile values from 0 to 1, exclusive

QUARTILE.INC function

Statistical functions

Returns the quartile of a data set

RANK.AVG function

Statistical functions

Returns the rank of a number in a list of numbers

RANK.EQ function

Statistical functions

Returns the rank of a number in a list of numbers

STDEV.P function

Statistical functions

Calculates standard deviation based on the entire population

STDEV.S function

Statistical functions

Estimates standard deviation based on a sample

T.DIST function

Statistical functions

Returns the Percentage Points (probability) for the Student t-distribution

T.DIST.2T function

Statistical functions

Returns the Percentage Points (probability) for the Student t-distribution

T.DIST.RT function

Statistical functions

Returns the Student's t-distribution

T.INV function

Statistical functions

Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom

T.INV.2T function

Statistical functions

Returns the Student's t-distribution

T.TEST function

Statistical functions

Returns the probability associated with a Student's t-test

VAR.P function

Statistical functions

Calculates variance based on the entire population

VAR.S function

Statistical functions

Estimates variance based on a sample

WEIBULL function

Statistical functions

Returns the Weibull distribution

WORKDAY.INTL function

Date and time functions

Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days

Z.TEST function

Statistical functions

Returns the one-tailed probability-value of a z-test

Consistency with best practices

The following functions were added because they are more consistent with best practices than existing functions. For example, the RANK.AVG function is more in line with how statisticians expect the function to work than the existing RANK function.

Renamed function

Function category

Purpose

CHISQ.DIST function

Statistical functions

Returns the cumulative beta probability density function

CHISQ.INV function

Statistical functions

Returns the cumulative beta probability density function

CONFIDENCE.T function

Statistical functions

Returns the confidence interval for a population mean, using a Student's t distribution

COVARIANCE.S function

Statistical functions

Returns the sample covariance, the average of the products deviations for each data point pair in two data sets

EXPON.DIST function

Statistical functions

Returns the exponential distribution

F.DIST function

Statistical functions

Returns the F probability distribution

F.INV function

Statistical functions

Returns the inverse of the F probability distribution

MODE.MULT function

Statistical functions

Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data

NETWORKDAYS.INTL function

Date and time functions

Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days

PERCENTILE.EXC function

Statistical functions

Returns the k-th percentile of values in a range, where k is in the range 0 to 1, exclusive

PERCENTRANK.EXC function

Statistical functions

Returns the rank of a value in a data set as a percentage (0 to 1, exclusive) of the data set

QUARTILE.EXC function

Statistical functions

Returns the quartile of the data set, based on percentile values from 0 to 1, exclusive

RANK.AVG function

Statistical functions

Returns the rank of a number in a list of numbers

T.DIST function

Statistical functions

Returns the Percentage Points (probability) for the Student t-distribution

T.INV function

Statistical functions

Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom

WORKDAY.INTL function

Date and time functions

Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days

New functions

The following function has been added to the Excel function library. This function can be used in the current version of Excel but is incompatible with Excel 2008 and Excel 97-2004. If backward compatibility is required, you can run the Compatibility Checker so that you can make the necessary changes in your sheet to avoid errors.

New function

Function category

Purpose

AGGREGATE function

Math and trigonometry functions

Returns an aggregate in a list or database

Backward compatibility

To share your sheet with other people who do not have Excel 2011 installed, you can use the compatibility functions instead of the renamed functions. Earlier versions of Excel recognize the old names and provide the expected results.

Selecting the right function in the sheet

When you start entering a function in the sheet, Formula AutoComplete lists both renamed and compatibility functions. The functions are organized by category to make it easier for you to click the one that you want to use. For example, if you type in =beta, Formula AutoComplete displays possible choices under the categories Functions and Compatibility Functions, and also lists any recently used functions that match what you are typing.

Formula AutoComplete

Formula AutoComplete

Checking for compatibility issues

New functions (such as AGGREGATE) or functions with algorithm and accuracy improvements (such as BETA.DIST) are incompatible with earlier versions of Excel. Earlier versions of Excel can't recognize the new functions, and #NAME? errors will be displayed instead of the expected results. Before you save your workbook, you can run the Compatibility Report to determine whether new functions were used. Then you can make the necessary changes to avoid errors when the workbook is opened in an earlier version of Excel (Excel 97-2004 or Excel 2008).

To run the Compatibility Report, do the following:

  1. On the View menu, under Toolbox, click Compatibility Report.

  2. On the Check compatibility with pop-up menu, click the version of Excel that you want to check for compatibility issues.

    The Results window displays a message that explains whether your workbook contains functions that are not available in the version of Excel that you selected.

    Compatibility Report

    Compatibility Report

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×