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.
