Friday, May 26, 2017

What's New: Changes made to Excel functions

What's New: Changes made to Excel functions

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

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

In this article

Functions with accuracy improvements

Renamed functions

New functions

Backward Compatibility

Functions with accuracy improvements

For the following functions, algorithm changes have been implemented to improve function accuracy and performance. For example, because the BETADIST function was inaccurate, a new algorithm has been implemented 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

ASINH function

Math and trigonometry functions (reference)

BETA.DIST function, BETADIST function

Statistical functions (reference) , Compatibility functions (reference)

BETA.INV function, BETAINV function

Statistical functions (reference) , Compatibility functions (reference)

BINOM.DIST function, BINOMDIST function

Statistical functions (reference), Compatibility functions (reference)

BINOM.INV function, CRITBINOM function

Statistical functions (reference), Compatibility functions (reference)

CHISQ.INV.RT function, CHIINV function

Statistical functions (reference), Compatibility functions (reference)

CHISQ.TEST function, CHITEST function

Statistical functions (reference), Compatibility functions (reference)

CONVERT function

Engineering functions (reference)

CUMIPMT function

Financial functions (reference)

CUMPRINC function

Financial functions (reference)

ERF function

Engineering functions (reference)

ERFC function

Engineering functions (reference)

F.DIST.RT function, FDIST function

Statistical functions (reference), Compatibility functions (reference)

F.INV.RT function, FINV function

Statistical functions (reference), Compatibility functions (reference)

FACTDOUBLE function

Math and trigonometry functions (reference)

GAMMA.DIST function, GAMMADIST function

Statistical functions (reference), Compatibility functions (reference)

GAMMA.INV function, GAMMAINV function

Statistical functions (reference)

Compatibility functions

GAMMALN function

Statistical functions (reference)

GEOMEAN function

Statistical functions (reference)

HYPGEOM.DIST function, HYPGEOMDIST function

Statistical functions (reference), Compatibility functions (reference)

IMLOG2 function

Engineering functions (reference)

IMPOWER function

Engineering functions (reference)

IPMT function

Financial functions (reference)

IRR function

Financial functions (reference)

LINEST function

Statistical functions (reference)

LOGNORM.DIST function, LOGNORMDIST function

Statistical functions (reference), Compatibility functions (reference)

LOGNORM.INV function, LOGINV function

Statistical functions (reference), Compatibility functions (reference)

MOD function

Math and trigonometry functions (reference)

NEGBINOM.DIST function, NEGBINOMDIST function

Statistical functions (reference), Compatibility functions (reference)

NORM.DIST function, NORMDIST function

Statistical functions (reference), Compatibility functions

NORM.INV function, NORMINV function

Statistical functions (reference), Compatibility functions

NORM.S.DIST function, NORMSDIST function

Statistical functions (reference), Compatibility functions (reference)

NORM.S.INV function, NORMSINV function

Statistical functions (reference), Compatibility functions (reference)

PMT function

Financial functions (reference)

PPMT function

Financial functions (reference)

POISSON.DIST function, POISSON function

Statistical functions (reference), Compatibility functions (reference)

RAND function

Math and trigonometry functions (reference)

STDEV.S function, STDEV function

Statistical functions (reference), Compatibility functions (reference)

T.DIST.RT function, TDIST function

Statistical functions (reference), Compatibility functions (reference)

T.DIST.2T function

Statistical functions (reference)

T.INV.2T function, TINV function

Statistical functions (reference), Compatibility functions (reference)

VAR.S function, VAR function

Statistical functions (reference), Compatibility functions (reference)

XIRR function

Financial functions (reference)

Top of Page

Renamed functions

The following Statistical functions (reference) have been renamed so that they 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 CRITBINOM returns the inverse of the binomial distribution, BINOM.INV is a more appropriate name.

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 (reference) category. However, if backward compatibility is not required, you should start using the renamed functions instead.

Renamed statistical functions

Renamed function

Compatibility function

BETA.DIST function

BETADIST function

BETA.INV function

BETAINV function

BINOM.DIST function

BINOMDIST function

BINOM.INV function

CRITBINOM function

CHISQ.DIST.RT function

CHIDIST function

CHISQ.INV.RT function

CHIINV function

CHISQ.TEST function

CHITEST function

CONFIDENCE.NORM function

CONFIDENCE function

COVARIANCE.P function

COVAR function

EXPON.DIST function

EXPONDIST function

F.DIST.RT function

FDIST function

F.INV.RT function

FINV function

F.TEST function

FTEST function

GAMMA.DIST function

GAMMADIST function

GAMMA.INV function

GAMMAINV function

HYPGEOM.DIST function

HYPGEOMDIST function

LOGNORM.DIST function

LOGNORMDIST function

LOGNORM.INV function

LOGINV function

MODE.SNGL function

MODE function

NEGBINOM.DIST function

NEGBINOMDIST function

NORM.DIST function

NORMDIST function

NORM.INV function

NORMINV function

NORM.S.DIST function

NORMSDIST function

NORM.S.INV function

NORMSINV function

PERCENTILE.INC function

PERCENTILE function

PERCENTRANK.INC function

PERCENTRANK function

POISSON.DIST function

POISSON function

QUARTILE.INC function

QUARTILE function

RANK.EQ function

RANK function

STDEV.P function

STDEVP function

STDEV.S function

STDEV function

T.DIST.2T function

TDIST function

T.DIST.RT function

TDIST function

T.INV.2T function

TINV function

T.TEST function

TTEST function

VAR.P function

VARP function

VAR.S function

VAR function

WEIBULL.DIST function

WEIBULL function

Z.TEST function

ZTEST function

Top of Page

New functions

The following functions have been added to the Excel function library. These functions can be used in the current version of Excel but are incompatible with earlier versions of Excel. If backward compatibility is required, you can run the Compatibility Checker so that you can make the necessary changes in your worksheet to avoid errors.

New function

Function category

Purpose

NETWORKDAYS.INTL function

Date and time functions (reference)

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

WORKDAY.INTL function

Date and time functions (reference)

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

AGGREGATE function

Math and trigonometry functions (reference)

Returns an aggregate in a list or database

CEILING.PRECISE function

Math and trigonometry functions (reference)

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.

ISO.CEILING function

Math and trigonometry functions (reference)

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

CHISQ.DIST function

Statistical functions (reference)

Returns the cumulative beta probability density function

CHISQ.INV function

Statistical functions (reference)

Returns the cumulative beta probability density function

CONFIDENCE.T function

Statistical functions (reference)

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

COVARIANCE.S function

Statistical functions (reference)

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

ERF.PRECISE function

Engineering functions (reference)

Returns the error function

ERFC.PRECISE function

Engineering functions (reference)

Returns the complementary ERF function integrated between x and infinity

F.DIST function

Statistical functions (reference)

Returns the F probability distribution

F.INV function

Statistical functions (reference)

Returns the inverse of the F probability distribution

FLOOR.PRECISE function

Math and trigonometry functions (reference)

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.

GAMMALN.PRECISE function

Statistical functions (reference)

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

MODE.MULT function

Statistical functions (reference)

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

PERCENTILE.EXC function

Statistical functions (reference)

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

PERCENTRANK.EXC function

Statistical functions (reference)

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

QUARTILE.EXC function

Statistical functions (reference)

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

RANK.AVG function

Statistical functions (reference)

Returns the rank of a number in a list of numbers

T.DIST function

Statistical functions (reference)

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

T.INV function

Statistical functions (reference)

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

Top of Page

Backward Compatibility

If you have to share your worksheet with other people who do not have Excel 2010 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.

Finding compatibility functions

To find all compatibility functions, do the following:

  1. On the Formulas tab, in the Function Library group, click Insert Function.

  2. In the Or select a category box, click Compatibility.

    Insert Function dialog box

Selecting the right function in the worksheet

When you start entering a function in the worksheet, Formula AutoComplete lists both renamed and compatibility functions. Distinguishing icons make it easier for you to click the one that you want to use.

Function AutoComplete example

Checking for compatibility issues

As mentioned earlier in this article, new functions are incompatible with earlier versions of Excel. Earlier versions of Excel cannot recognize the new functions, and #NAME? errors will be displayed instead of the expected results. Before you save your workbook to a file format of an earlier version of Excel, you can run the Compatibility Checker to determine whether new functions were used. This way, you can make the necessary changes to avoid errors.

To run the Compatibility Checker, do the following:

  1. Click the Office Button, and then click Info.

  2. Under Prepare for Distribution, click Check for Issues, and then click Check Compatibility.

    Compatibility Checker dialog box

Top of Page

No comments:

Post a Comment