Built-in Statistical Functions

Excel provides a variety of statistical functions, which we list below. Since these have been covered in the rest of the website, we won’t go into any detail here.

Basic statistical functions

Figure 1 – Basic Excel statistics functions

Click below for more information about each of these functions:

AVERAGE, MEDIAN, MODE, GEOMEAN, HARMEAN, AVEDEV, DEVSQ, STDEV, STDEVP, VAR, VARP, KURT, SKEW, LARGE, MAX, MIN, PERCENTRANK, PERCENTILE, QUARTILE, RANK, SMALL, AVERAGEIF, AVERAGEIFS, COUNT, STANDARDIZE, TRIMMEAN

Correlation and covariance functions

Figure 2 – Excel correlation and covariance functions

Click below for more information about each of these functions:

CORREL, COVAR, PEARSON, RSQ, FISHER, FISHERINV

Regression function

Figure 3 – Excel regression functions

Click below for more information about each of these functions:

FORECAST, INTERCEPT, SLOPE, TREND, LINEST, STEYX, GROWTH, LOGEST

Other statistical functions

Figure 4 – Other Excel statistical functions

Click below for more information about each of these functions:

CONFIDENCE, FREQUENCY, PROB

Statistical distribution functions

The following table provides a list of the distributions supported by Excel. For each, the name of cumulative distribution functions (CDF) is given, and where available the name of the inverse function is also provided. For a few of the distributions, the CDF function also has an option to provide the probability density function (PDF). Finally, additional test functions are listed where available.

Figure 5 – Excel 2007 distribution functions

Excel 2010 functions

All the functions defined in previous versions of Excel are available in Excel 2010 and later versions of Excel, but the mathematical accuracy of many of these functions has been improved in Excel 2010 and later versions. In addition, a few new functions have been added and more consistent naming conventions have been introduced, including the following:

Figure 6 – New Excel 2010 statistical functions

For example, if R = {4,6,4,7,6,6}, then RANK(4,R) = 5, RANK(6,R) = 2 and RANK(7,R) = 1, while RANK.AVG(4,R) = 5.5, RANK.AVG(6,R) = 3 and RANK.AVG(7,R) = 1. Also RANK.EQ is the same as RANK. Similarly, RANK(4,R,1) = 1, RANK(6,R,1) = 3 and RANK(7,R,1) = 6, while RANK.AVG(4,R,1) = 1.5, RANK.AVG(6,R,1) = 4 and RANK.AVG(7,R,1) = 6.

MODE.MULT is an array function that is useful with multimodal data. Before using the function you need to highlight a vertical range (i.e. column vector) with at least as many cells as modes and then enter =MODE.MULT(R) and Ctrl-Shft-Enter (or simply Enter if using Excel 365). If you highlight more cells than modes the extra cells will contain the error values #N/A.

The function GAMMALN.PRECISE, which is equivalent to GAMMALN, has also been added in Excel 2010.

Starting with Excel 2010 there are the following alternative names for the distribution functions:

Figure 7 – Excel 2010 distribution functions

The functions that end in .DIST all provide both the probability distribution function (when the cum parameter is FALSE) as well as the left-tailed cumulative distribution function (when the cum parameter is TRUE). These are all left-tailed functions. For the chi-square and F distributions, there is also a right-tailed version (indicated by .RT in the above table) of the distribution and inverse cumulative functions. There is also a right-tailed version of the distribution function and a two-tailed version of the t distribution and its inverse.

The syntax for the various new t distribution functions is T.DIST(x,df,cum), T.DIST.RT(x,df) and T.DIST.2T(x,df). The syntax for the new inverse function is T.INV(p,df) and T.INV.2T(p,df). We have the following equivalences between the Excel 2007 and later versions of the t distribution functions:

Figure 8 – Equivalences for the t distribution

Note that while the old t distribution functions worked differently from the normal and binomial distribution functions, the new functions are all consistent. Also, we can now explicitly calculate the pdf of the t distribution as T.DIST(x, df, FALSE) instead of having to use a complicated formula based on Definition 1 of t Distribution.

We also have the following equivalences between the Excel 2007 and later versions of the chi-square distribution functions:

Figure 9 – Equivalences for the chi-square distribution

Finally, we can now explicitly calculate the pdf of the chi-square distribution as CHISQ.DIST(x, df, FALSE). The equivalences for the F distribution between Excel 2007 and later versions are similar.

Figure 10 – Equivalences for the F distribution

Excel 2013 functions

All the functions defined in previous versions of Excel are available in Excel 2013, but the following additional functions are available in versions of Excel starting with Excel 2013:

Figure 11 – New Excel 2013 statistical functions

Excel 2016 forecast functions

The following forecast functions were introduced with Excel 2016. More details about these functions can be found at Excel 2016 Forecasting Functions.

FORECAST.ETS(x, R1, R2, seasonality, missing, aggregation) = the forecasted value at the time value x

FORECAST.ETS.SEASONALITY(R1, R2, missing, aggregation) = the seasonality value (1 for no seasonality, 4 for quarterly, 12 for monthly, etc.) based on the data in R1 and R2

FORECAST.CONFINT(x, R1, R2, 1 – α, seasonality, missing, aggregation) = k such that (x-pred – k, x-pred + k) is the 1 – α confidence interval for the forecasted value x–pred at the time value x; the default value for 1 – α is .95.

FORECAST.ETS.STAT(R1, R2, stat-type, seasonality, missing, aggregation) = a forecasted statistic based on the value of stat-type.

In addition, the FORECAST.LINEAR function has been added which is equivalent to the FORECAST function described in Figure 3 above.

Reference

Microsoft (2021) Excel functions
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188#bm6