Excel provides a wide range of statistical functions beginning from the most basic functions (Mean) to the more complex probability tests. In this tutorial, we will outline many statistical functions in Excel including older functions, as well as the most recently added functions.
Table 1 – Count & Frequency | Statistical Functions in Excel
|
COUNT & FREQUENCY |
|
|
COUNT |
Calculates the number of numerical values in a given set of values or cells |
|
COUNTA |
Calculates the number of non-blanks in a given set of values or cells |
|
COUNTBLANK |
Calculates the number of blank cells in a specified range |
|
COUNTIF |
Calculates the number of cells in a range that satisfy a specified condition |
|
COUNTIFS |
Calculates the number of cells in a range that satisfies a set of conditions (New in Excel 2007) |
|
FREQUENCY |
Calculates an array displaying the number of values in a range that falls within specified ranges |
Table 2 – Permutations │Statistical Functions in Excel
|
PERMUTATIONS |
|
|
PERMUT |
Permutations of a specified data |
|
PERMUTATIONA |
Number of permutations of a data set (with repetitions) selected from a larger (New in Excel 2013) set |
Table 3 – Confidence Intervals │Statistical Excel Functions
|
CONFIDENCE INTERVALS |
|
|
CONFIDENCE |
Confidence interval for the mean population from a distribution (changed to Confidence.Norm in Excel 2010) |
|
CONFIDENCE.NORM |
Confidence interval for the mean population in a normal distribution (New in Excel 2010 changed to the CONFIDENCE function) |
|
CONFIDENCE.T |
Confidence interval for a mean population from a Student’s T distribution (New in Excel 2010) |
Table 4- Percentiles, Quartiles & Rank │Statistical Excel Formulas
|
Percentiles, Quartiles & Rank |
|
|
PERCENTILE |
Calculates the K’th percentile of values in a given range where K is in the range 0 to 1 (inclusive) (Changed to PERCENTILE.INC function in Excel 2010) |
|
PERCENTILE.INC |
Calculates the K’th percentile of values in a specified data set where K is in the range 0 to 1 (inclusive) (New in Excel 2010 – replaced the PERCENTILE function) |
|
PERCENTILE.EXC |
Calculates the K’th percentile of values in a dataset where K is in the range 0 to 1 (exclusive) (New in Excel 2010) |
|
QUARTILE |
Calculates the itemized quartile of a data set using the percentile value 0 to 1 (inclusive) (Changed to QUARTILE.INC function in Excel 2010) |
|
QUARTILE.INC |
Calculates the itemized quartile of a dataset using the percentile value 0 to 1 (inclusive) (New in Excel 2010 – Changed to the QUARTILE function) |
|
QUARTILE.EXC |
Specified quartile of a data set using percentile value 0 – 1 (exclusive) (New in Excel 2010) |
|
RANK |
Statistical rank of a given value within a dataset (Changed to Rank.Eq function in Excel 2010) |
|
RANK.EQ |
Calculates the Mode of a data (if more than one value has same rank, the top rank of that set is returned) (New in Excel 2010 – replaces the Rank function) |
|
RANK.AVG |
Calculates statistical rank of a given value within an array of values (if more than one value has the same rank, the average rank is returned) (New in Excel 2010) |
|
PERCENTRANK |
Calculates rank of a value in a data set, as a percentage (0 – 1 inclusive) (Changed to Percentrank.Inc function in Excel 2010) |
|
PERCENTRANK.INC |
Calculates rank of a value in a data set as a percentage (0 – 1 inclusive) (New in Excel 2010 – replaces the Percentrank function) |
|
PERCENTRANK.EXC |
Calculates rank of a value in a data set as a percentage (0 – 1 exclusive) (New in Excel 2010) |
Table 5 – Variance & Deviation │Statistical Excel Functions
|
Deviation & Variance |
|
|
AVEDEV |
Average of the absolute deviations of data arguments from their mean |
|
DEVSQ |
Calculates sum of the squares of the deviations of a set of data arguments from a given sample mean |
|
STDEV |
Calculates standard deviation of a data set (which denotes a sample of a population) (Changed to Stdev.S function in Excel 2010) |
|
STDEV.S |
Calculates standard deviation of a data set (which denotes a sample of a population) (New in Excel 2010 – replaces the Stdev function) |
|
STDEVA |
Calculates standard deviation of a dataset (which denotes a sample population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 |
|
STDEVP |
Calculates standard deviation of a data set (which denotes an entire population) (Changed to Stdev.P function in Excel 2010) |
|
STDEV.P |
Calculates standard deviation of a data set (which denotes an entire population) (New in Excel 2010 – substitutes the Stdevp function) |
|
STDEVPA |
Calculates standard deviation of a data set (which represents an whole population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 |
|
VAR |
Calculates variance of a data set (which represent a sample population) (Changed to Var.S function in Excel 2010) |
|
VAR.S |
Calculates variance of a data set (which denotes a sample population) (New in Excel 2010 – replaces the Var function) |
|
VARA |
Calculates variance of a data set (which denotes a sample population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 |
|
VARP |
Calculates variance of a data set (which represents a whole population) (Changed to Var.P function in Excel 2010) |
|
VAR.P |
Calculates variance of a data set (which represent a whole population) (New in Excel 2010 – replaces the Varp function) |
|
VARPA |
Calculates variance of a data set (which represents a whole population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 |
|
COVAR |
Calculates population covariance (i.e. the average of the products of deviations for each pair within two supplied data sets) (Changed to Covariance.P function in Excel 2010) |
|
COVARIANCE.P |
Calculates population covariance (i.e. the mean of the products of deviations for each pair within two given data sets) (New in Excel 2010 – substitutes the Covar function) |
|
COVARIANCE.S |
Calculates the sample covariance (i.e. the mean of the products of deviations for each pair within two given data sets) (New in Excel 2010) |
Table 6 – Trend Lines Functions │Excel Statistical Functions
|
Trend Line Functions |
|
|
FORECAST |
Predicts a future point on a linear trend line in a given set of x- and y- values (Changed to FORECAST.LINEAR function in Excel 2016) |
|
FORECAST.ETS |
Uses an exponential smoothing algorithm to predicts a future value on a timeline, built on a series of existing values (New in Excel 2016 – not offered in Mac Excel 2016) |
|
FORECAST.ETS.CONFINT |
Calculates confidence interval for a forecast value at a given marked date (New in Excel 2016 – not offered in Mac Excel 2016) |
|
FORECAST.ETS.SEASONALITY |
Calculates length of the repetitive pattern Excel detects for a given time series (New in Excel 2016 – not offered in Excel 2016 for Mac) |
|
FORECAST.ETS.STAT |
Calculates the statistical value related of time series forecasting (New in Excel 2016 – not offered in Excel 2016 for Mac) |
|
FORECAST.LINEAR |
Predicts a future point on a linear trend line fitted to a specified data set of x- and y- values (New in Excel 2016 (not Excel 2016 for Mac) – replaces the Forecast function) |
|
INTERCEPT |
Calculates the best fit regression line, through a dataset series of x- and y- values and Estimates value at which this line intercepts the y-axis |
|
LINEST |
Calculates the statistical information relating the trend of the line of best fit, through a given data set of x- and y- values |
|
SLOPE |
Calculates the slope of the linear regression line through a supplied series of x- and y- values |
|
TREND |
Calculates the trend line through a given set of y-values and returns additional y-values for a data set of new x-values |
|
GROWTH |
Calculates the numbers in a exponential growth trend, based on a given of x- and y- values |
|
LOGEST |
Calculates the parameters of an exponential trend for a given x- and y- values |
|
STEYX |
Calculates the standard error of the projected y-value for each x in the regression line for a dataset of x- and y- values |
Table 7 – Largest & Smallest Value functions │Excel Statistical Functions
|
Finding the Largest & Smallest Values |
|
|
MAX |
Calculates largest value from a list of data sets |
|
MAXA |
Calculates largest value from a data set counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 |
|
MAXIFS |
Calculates largest value from a subset of values in a list that are detailed with respect to one or more conditions. (New in Excel 2019) |
|
MIN |
Calculates smallest value from a data set |
|
MINA |
Calculates smallest value from a list of specified values, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 |
|
MINIFS |
Calculates smallest value from a subset of values in a list that are specified with respect to one or more conditions. (New in Excel 2019) |
|
LARGE |
Calculates Kth LARGEST value from a dataset, for a given value K |
|
SMALL |
Calculates Kth SMALLEST value from a list of dataset, for a given value K |
Table 8- Averages │Excel Statistical Functions
|
Averages |
|
|
AVERAGE |
Calculates Average of a list of data sets |
|
AVERAGEA |
Calculates Average of a list of data sets, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 |
|
AVERAGEIF |
Calculates the Average of the cells in a given range, that satisfies a given criteria (New in Excel 2007) |
|
AVERAGEIFS |
Calculates the Average of the cells in a given range, that satisfies multiple criteria (New in Excel 2007) |
|
MEDIAN |
Calculates Median (the middle value) of a specified data set |
|
MODE |
Calculates Mode (the most frequently occurring value) of a data set (Changed to MODE.SNGL function in Excel 2010) |
|
MODE.SNGL |
Calculates Mode (the most frequently occurring value) of a data set (New in Excel 2010 – replaces the MODE function) |
|
MODE.MULT |
Calculates a vertical array of the most frequently occurring values in an array or range of data (New in Excel 2010) |
|
GEOMEAN |
Calculates geometric mean of a specified data set |
|
HARMEAN |
Calculates harmonic mean of a set of specified data set |
|
TRIMMEAN |
Calculates mean of the interior of a data set |
Table 9 – Distribution & Probability tests │Excel Statistical Functions
|
Distribution & Probability tests |
|
|
BETADIST |
Calculates cumulative beta probability density function (Changed to BETA.DIST function in Excel 2010) |
|
BETA.DIST |
Calculates cumulative beta distribution function or the beta probability density function (New in Excel 2010 – replaces the BETADIST function) |
|
BETAINV |
Calculates inverse of the cumulative beta probability density function (Changed to BETA.INV function in Excel 2010) |
|
BETA.INV |
Calculates inverse of the cumulative beta probability density function (New in Excel 2010 – replaces the BETAINV function) |
|
BINOMDIST |
Calculates individual term binomial distribution probability (Changed to BINOM.DIST function in Excel 2010) |
|
BINOM.DIST |
Calculates individual term binomial distribution probability (New in Excel 2010 – replaces the BINOMDIST function) |
|
BINOM.DIST.RANGE |
Calculates probability of a trial result using a binomial distribution (New in Excel 2013) |
|
NEGBINOMDIST |
Calculates negative binomial distribution (Changed to NEGBINOM.DIST function in Excel 2010) |
|
NEGBINOM.DIST |
Calculates negative binomial distribution (New in Excel 2010 – replaces the NEGBINOMDIST function) |
|
CRITBINOM |
Calculates smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value (Changed to BINOM.INV function in Excel 2010) |
|
BINOM.INV |
Calculates smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value (New in Excel 2010 – replaces the CRITBINOM function) |
|
CHIDIST |
Calculates right-tailed probability of the chi-squared distribution (Changed to CHISQ.DIST.RT function in Excel 2010) |
|
CHISQ.DIST.RT |
Calculates right-tailed probability of the chi-squared distribution (New in Excel 2010 – replaces the CHIDIST function) |
|
CHISQ.DIST |
Calculates chi-squared distribution (probability density or cumulative distribution function) (New in Excel 2010) |
|
CHIINV |
Calculates inverse of the right-tailed probability of the chi-squared distribution (Changed to CHISQ.INV.RT function in Excel 2010) |
|
CHISQ.INV.RT |
Calculates inverse of the right-tailed probability of the chi-squared distribution (New in Excel 2010 – replaces the CHIINV function) |
|
CHISQ.INV |
Calculates inverse of the left-tailed probability of the chi-squared distribution (New in Excel 2010) |
|
CHITEST |
Calculates chi-squared statistical test for independence (Changed to CHISQ.TEST function in Excel 2010) |
|
CHISQ.TEST |
Calculates chi-squared statistical test for independence (New in Excel 2010 – replaces the CHITEST function) |
|
CORREL |
Calculates correlation coefficient between two sets of values |
|
EXPONDIST |
Calculates exponential distribution (Changed to EXPON.DIST function in Excel 2010) |
|
EXPON.DIST |
Calculates exponential distribution (New in Excel 2010 – replaces the EXPONDIST function) |
|
FDIST |
Calculates right-tailed F probability distribution for two data sets (Changed to F.DIST.RT function in Excel 2010) |
|
F.DIST.RT |
Calculates right-tailed F probability distribution for two data sets (New in Excel 2010 – replaces the FDIST function) |
|
F.DIST |
Calculates F probability distribution (cumulative distribution or probability density function) (New in Excel 2010) |
|
FINV |
Calculates inverse of the right-tailed F probability distribution for two data sets (Changed to F.INV.RT function in Excel 2010) |
|
F.INV.RT |
Calculates inverse of the right-tailed F probability distribution for two data sets (New in Excel 2010 – replaces the FINV function) |
|
F.INV |
Calculates inverse of the Cumulative F distribution (New in Excel 2010) |
|
FISHER |
Calculates Fisher transformation |
|
FISHERINV |
Calculates inverse of the Fisher transformation |
|
FTEST |
Calculates outcome of an F-Test for two data sets (Changed to F.TEST function in Excel 2010) |
|
F.TEST |
Calculates outcome of an F-Test for two data sets (New in Excel 2010 – replaces the FTEST function) |
|
GAMMADIST |
Calculates gamma distribution (Changed to GAMMA.DIST function in Excel 2010) |
|
GAMMA.DIST |
Calculates gamma distribution (New in Excel 2010 – replaces the GAMMADIST function) |
|
GAMMAINV |
Calculates inverse gamma cumulative distribution (Changed to GAMMA.INV function in Excel 2010) |
|
GAMMA.INV |
Calculates inverse gamma cumulative distribution (New in Excel 2010 – replaces the GAMMAINV function) |
|
GAMMA |
Calculates gamma function value for a given data set (New in Excel 2013) |
|
GAMMALN |
Calculates the natural logarithm of the gamma function for a supplied value |
|
GAMMALN.PRECISE |
Calculates natural logarithm of the gamma function for a supplied value (New in Excel 2010) |
|
GAUSS |
Calculates the probability that a member of a standard normal population will fall between the mean and z standard deviations from the mean (New in Excel 2013) |
|
HYPGEOMDIST |
Calculates hypergeometric distribution (Changed to HYPGEOM.DIST function in Excel 2010) |
|
HYPGEOM.DIST |
Calculates hypergeometric distribution (New in Excel 2010 – replaces the HYPGEOMDIST function) |
|
KURT |
Calculates kurtosis of a data set |
|
LOGNORMDIST |
Calculates cumulative log-normal distribution (Changed to LOGNORM.DIST function in Excel 2010) |
|
LOGNORM.DIST |
Calculates log-normal probability density function or the cumulative log- normal distribution (New in Excel 2010 – replaces the LOGNORMDIST function) |
|
LOGINV |
Calculates inverse of the lognormal distribution (Changed to LOGNORM.INV function in Excel 2010) |
|
LOGNORM.INV |
Calculates inverse of the lognormal distribution (New in Excel 2010 – replaces the LOGINV function) |
|
NORMDIST |
Calculates normal cumulative distribution (Changed to NORM.DIST function in Excel 2010) |
|
NORM.DIST |
Calculates normal cumulative distribution (New in Excel 2010 – replaces the NORMDIST function) |
|
NORMINV |
Calculates inverse of the normal cumulative distribution (Changed to NORM.INV function in Excel 2010) |
|
NORM.INV |
Calculates inverse of the normal cumulative distribution (New in Excel 2010 – replaces the NORMINV function) |
|
NORMSDIST |
Calculates standard normal cumulative distribution (Changed to NORM.S.DIST function in Excel 2010) |
|
NORM.S.DIST |
Calculates standard normal cumulative distribution (New in Excel 2010 – replaces the NORMSDIST function) |
|
NORMSINV |
Calculates inverse of the standard normal cumulative distribution (Changed to NORM.S.INV function in Excel 2010) |
|
NORM.S.INV |
Calculates inverse of the standard normal cumulative distribution (New in Excel 2010 – replaces the NORMSINV function) |
|
PEARSON |
Calculates Pearson product moment correlation coefficient |
|
RSQ |
Calculates square of the Pearson product moment correlation coefficient |
|
PHI |
Calculates value of the density function for a standard normal distribution, for a supplied number (New in Excel 2013) |
|
POISSON |
Calculates Poisson distribution (Changed to POISSON.DIST function in Excel 2010) |
|
POISSON.DIST |
Calculates Poisson distribution (New in Excel 2010 – replaces the POISSON function) |
|
PROB |
Calculates probability that values in a given range are within given limits |
|
SKEW |
Calculates skewness of a distribution |
|
SKEW.P |
Calculates skewness of a distribution based on a population (New in Excel 2013) |
|
STANDARDIZE |
Calculates a normalized value |
|
TDIST |
Calculates Student’s T-distribution (Changed to T.DIST.2T & T.DIST.RT functions in Excel 2010) |
|
T.DIST.2T |
Calculates two-tailed Student’s T-distribution (New in Excel 2010 – replaces the TDIST function) |
|
T.DIST.RT |
Calculates right-tailed Student’s T-distribution (New in Excel 2010 – replaces the TDIST function) |
|
T.DIST |
Calculates Student’s T-distribution (probability density or cumulative distribution function) (New in Excel 2010) |
|
TINV |
Calculates two-tailed inverse of the Student’s T-distribution (Changed to T.INV.2T function in Excel 2010) |
|
T.INV.2T |
Calculates two-tailed inverse of the Student’s T-distribution (New in Excel 2010 – replaces the TINV function) |
|
T.INV |
Calculates left-tailed inverse of the Student’s T-distribution (New in Excel 2010) |
|
TTEST |
Calculates probability connected to the Student’s T-Test (Changed to T.TEST function in Excel 2010) |
|
T.TEST |
Calculates probability connected to the Student’s T-Test (New in Excel 2010 – replaces the TTEST function) |
|
WEIBULL |
Calculates Weibull distribution (Changed to WEIBULL.DIST function in Excel 2010) |
|
WEIBULL.DIST |
Calculates Weibull distribution (New in Excel 2010 – replaces the WEIBULL function) |
|
ZTEST |
Calculates one-tailed probability value of a z-test (Changed to Z.TEST function in Excel 2010) |
|
Z.TEST |
Calculates one-tailed probability value of a z-test (New in Excel 2010 – replaces the ZTEST function) |
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment