Function Consistency Improvements in Excel 2010

The second part of the function improvements work we are making for Excel 2010 is to create a more consistent function library. We have implemented a new naming scheme to allow consistent and accurate naming convention for functions. Also, we have added new functions to the function library to round out our function set. New functions will take advantage of the new and more accurate algorithms.

Consistent Function Names

To be explicitly clear about what our functions do, we have implemented a new naming scheme. New versions of existing functions have been created to follow the new naming scheme so that there is consistency within our function library. The goal here is to have functions with names that accurately describe their functionality so that there is no misunderstanding as to what the function calculates.

Excel will adopt the following consistent naming convention:

 

Naming Scheme

Description

General

<function>.<descriptor>

The descriptor differentiates one version of a function from another.

Examples

Statistical distribution functions: <distrib>.DIST

Functions with this syntax are the left-tailed cumulative distribution function when the cumulative parameter is TRUE, and they are the probability density function when the cumulative parameter is FALSE. Variations of this syntax are <distrib>.DIST.RT and <distrib>.DIST.2T which are the right-tailed cumulative distribution function and the two-tailed cumulative distribution function, respectively.

 

Statistical distribution inverses: <dsitrib>.INV

Functions with this syntax are the inverse of the left-tailed cumulative distribution function. Variations of this syntax are <distrib>.INV.RT and <distrib>.INV.2T which are the inverse of the right-tailed cumulative distribution function and the inverse of the two-tailed cumulative distribution function, respectively.

 

Functions based on a sample: <function>.S

Functions with this syntax are based on a sample.

 

Functions based on a population: <function>.P

Functions with this syntax are based on a population.

You will see examples of the new naming convention in the next part of this post.

Consistent Function Set

In Excel 2010, we have added new functions to the existing function library so that we have a set of functions that are internally consistent and consistent with best practices. We have added new statistical distribution functions so that we can provide a consistent set of functions across all statistical distributions. We have also added additional versions of existing math functions.

We have added functions so that for all statistical distribution function, we will provide the PDF and left-tailed CDF. In the case where an inverse CDF exists already, we ensure that there always exists the inverse of the left-tailed CDF.

The table below shows our new set of statistical distribution functions:

Distribution

PDF/PMF

Left-tailed CDF

Right-tailed/ 2-tailed CDF

Inverse left-tail CDF

Inverse right-tailed/2-tailed CDF

Beta

BETA.DIST

BETA.DIST

 

BETA.INV

 

Binomial

BINOM.DIST

BINOM.DIST

 

BINOM.INV

 

Chi squared

CHISQ.DIST

CHISQ.DIST

CHISQ.DIST.RT

CHISQ.INV

CHISQ.INV.RT

Exponential

EXPON.DIST

EXPON.DIST

 
 
 

F

F.DIST

F.DIST

F.DIST.RT

F.INV

F.INV.RT

Gamma

GAMMA.DIST

GAMMA.DIST

 

GAMMA.INV

 

Hypergeometric

HYPGEOM.DIST

HYPGEOM.DIST

 
 
 

Lognormal

LOGNORM.DIST

LOGNORM.DIST

 

LOGNORM.INV

 

Negative Binomial

NEGBINOM.DIST

NEGBINOM.DIST

 
 
 

Normal

NORM.DIST

NORM.DIST

 

NORM.INV

 

Standard Normal

NORM.S.DIST

NORM.S.DIST

 

NORMS.INV

 

Poisson

POISSON.DIST

POISSON.DIST

 
 
 

Student's t

T.DIST

T.DIST

T.DIST.RT

T.DIST.2T

T.INV

T.INV.2T

Weibull

WEIBULL.DIST

WEIBULL.DIST

 
 
 

Excel 2010 will also feature new versions of existing functions. These will be a different variation of an existing function. The table below lists new functions in our consistent function library:

Function name

Description

CEILING.PRECISE

Consistent with mathematical definition. Rounds up towards positive infinity regardless of the sign of the number being rounded.

FLOOR.PRECISE

Consistent with mathematical definition. Rounds down towards negative infinity regardless of the sign of the number being rounded.

CONFIDENCE.NORM

Name for existing CONFIDENCE function that is internally consistent with naming of other confidence function.

CONFIDENCE.T

Consistent definition with industry best practices. Confidence function assuming a Student’s t distribution.

COVARIANCE.P

Name for existing COVAR function that is internally consistent with naming of other covariance function.

COVARIANCE.S

Internally consistent name with other functions that act on a population or a sample.

MODE.MULT

Consistent with user expectations. Returns multiple modes for a range.

MODE.SNGL

Name for existing MODE function that is internally consistent with naming of other mode function.

PERCENTILE.EXC

Consistent with industry best practices, assuming percentile is a value between 0 and 1, exclusive.

PERCENTILE.INC

Name for existing PERCENTILE function that is internally consistent with naming of other percentile function.

PERCENTRANK.EXC

Consistent with industry best practices, assuming percentile is a value between 0 and 1, exclusive.

PERCENTRANK.INC

Name for existing PERCENTRANK function that is internally consistent with naming of other PERCENTRANK function.

QUARTILE.EXC

Consistent with industry best practices, assuming percentile is a value between 0 and 1, exclusive.

QUARTILE.INC

Name for existing QUARTILE function that is internally consistent with naming of other quartile function.

RANK.AVG

Consistent with industry best practices, returning the average rank when there is a tie.

RANK.EQ

Name for existing RANK function that is internally consistent with naming of other rank function.

STDEV.P

Name for existing STDEVP function that is internally consistent with naming of other standard deviation function.

STDEV.S

Name for existing STDEV function that is internally consistent with naming of other standard deviation function.

VAR.P

Name for existing VARP function that is internally consistent with naming of other variance function.

VAR.S

Name for existing VAR function that is internally consistent with naming of other variance function.

CHISQ.TEST

Name for existing CHITEST function that is internally consistent with naming of other hypothesis test functions.

F.TEST

Name for existing FTEST function that is internally consistent with naming of other hypothesis functions.

T.TEST

Name for existing TTEST function that is internally consistent with naming of other hypothesis functions.

Z.TEST

Name for existing ZTEST function that is internally consistent with naming of other hypothesis functions.

Stay tuned for my next post where I will talk about the UI changes we have made to help users select and use the new consistent functions while still making the old functions available. I will also talk about our backward compatibility story for functions in Excel 2010.