Excel Statistical Functions
Basic Description
Excel provides a large selection of Statistical Functions, that perform most of the common statistical calculations, from basic mean, median & mode calculations to the more complex statistical distribution and probability tests. Note, however, that some of the functions are new to Excel 2007, Excel 2010 or Excel 2013, so are not available in earlier versions of Excel. The Excel Statistical functions are all listed in the table below.
The functions have been grouped into categories, to enable you to easily find the function you need. Selecting a function name will take you to a full description of the function, with examples of use and common errors.
Finding the Largest & Smallest Values | |
MAX | Returns the largest value from a list of supplied numbers |
MAXA | Returns the largest value from a list of supplied values, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 |
MIN | Returns the smallest value from a list of supplied numbers |
MINA | Returns the smallest value from a list of supplied values, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 |
LARGE | Returns the Kth LARGEST value from a list of supplied numbers, for a given value K |
SMALL | Returns the Kth SMALLEST value from a list of supplied numbers, for a given value K |
Counting Cells | |
COUNT | Returns the number of numerical values in a supplied set of cells or values |
COUNTA | Returns the number of non-blanks in a supplied set of cells or values |
COUNTBLANK | Returns the number of blank cells in a supplied range |
COUNTIF | Returns the number of cells (of a supplied range), that satisfy a given criteria |
COUNTIFS | Returns the number of cells (of a supplied range), that satisfy a set of given criteria (New in Excel 2007) |
Averages, Frequency & Rank | |
AVERAGE | Returns the Average of a list of supplied numbers |
AVERAGEA | Returns the Average of a list of supplied numbers, 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 supplied range, that satisfy a given criteria (New in Excel 2007) |
AVERAGEIFS | Calculates the Average of the cells in a supplied range, that satisfy multiple criteria (New in Excel 2007) |
MEDIAN | Returns the Median (the middle value) of a list of supplied numbers |
MODE | Returns the Mode (the most frequently occurring value) of a list of supplied numbers (Replaced by MODE.SNGL function in Excel 2010) |
MODE.SNGL | Returns the Mode (the most frequently occurring value) of a list of supplied numbers (New in Excel 2010 – replaces MODE function) |
MODE.MULT | Returns a vertical array of the most frequently occurring values in an array or range of data (New in Excel 2010) |
GEOMEAN | Returns the geometric mean of a set of supplied numbers |
HARMEAN | Returns the harmonic mean of a set of supplied numbers |
TRIMMEAN | Returns the mean of the interior of a supplied set of values |
FREQUENCY | Returns an array showing the number of values from a supplied array, which fall into specified ranges |
RANK | Returns the statistical rank of a given value, within a supplied array of values (Replaced by RANK.EQ function in Excel 2010) |
RANK.EQ | Returns the Mode (the most frequently occurring value) of a list of supplied numbers (if more than one value has same rank, the top rank of that set is returned) (New in Excel 2010 – replaces RANK function) |
RANK.AVG | Returns the statistical rank of a given value, within a supplied array of values (if more than one value has same rank, the average rank is returned) (New in Excel 2010) |
KURT | Returns the kurtosis of a data set |
PERCENTILE | Returns the K’th percentile of values in a supplied range, where K is in the range 0 – 1 (inclusive) (Replaced by PERCENTILE.INC function in Excel 2010) |
PERCENTILE.INC | Returns the K’th percentile of values in a supplied range, where K is in the range 0 – 1 (inclusive) (New in Excel 2010 – replaces PERCENTILE function) |
PERCENTILE.EXC | Returns the K’th percentile of values in a supplied range, where K is in the range 0 – 1 (exclusive) (New in Excel 2010) |
PERCENTRANK | Returns the rank of a value in a data set, as a percentage (0 – 1 inclusive) (Replaced by PERCENTRANK.INC function in Excel 2010) |
PERCENTRANK.INC | Returns the rank of a value in a data set, as a percentage (0 – 1 inclusive) (New in Excel 2010 – replaces PERCENTRANK function) |
PERCENTRANK.EXC | Returns the rank of a value in a data set, as a percentage (0 – 1 exclusive) (New in Excel 2010) |
QUARTILE | Returns the specified quartile of a set of supplied numbers, based on percentile value 0 – 1 (inclusive) (Replaced by QUARTILE.INC function in Excel 2010) |
QUARTILE.INC | Returns the specified quartile of a set of supplied numbers, based on percentile value 0 – 1 (inclusive) (New in Excel 2010 – replaces QUARTILE function) |
QUARTILE.EXC | Returns the specified quartile of a set of supplied numbers, based on percentile value 0 – 1 (exclusive) (New in Excel 2010) |
Deviation & Variance | |
AVEDEV | Returns the average of the absolute deviations of data points from their mean |
STDEV | Returns the standard deviation of a supplied set of values (which represent a sample of a population) (Replaced by STDEV.S function in Excel 2010) |
STDEV.S | Returns the standard deviation of a supplied set of values (which represent a sample of a population) (New in Excel 2010 – replaces STDEV function) |
STDEVA | Returns the standard deviation of a supplied set of values (which represent a sample of a population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 |
STDEVP | Returns the standard deviation of a supplied set of values (which represent an entire population) (Replaced by STDEV.P function in Excel 2010) |
STDEV.P | Returns the standard deviation of a supplied set of values (which represent an entire population) (New in Excel 2010 – replaces STDEVP function) |
STDEVPA | Returns the standard deviation of a supplied set of values (which represent an entire population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 |
VAR | Returns the variance of a supplied set of values (which represent a sample of a population) (Replaced by VAR.S function in Excel 2010) |
VAR.S | Returns the variance of a supplied set of values (which represent a sample of a population) (New in Excel 2010 – replaces VAR function) |
VARA | Returns the variance of a supplied set of values (which represent a sample of a population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 |
VARP | Returns the variance of a supplied set of values (which represent an entire population) (Replaced by VAR.P function in Excel 2010) |
VAR.P | Returns the variance of a supplied set of values (which represent an entire population) (New in Excel 2010 – replaces VARP function) |
VARPA | Returns the variance of a supplied set of values (which represent an entire population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 |
COVAR | Returns population covariance (ie. the average of the products of deviations for each pair within two supplied data sets) (Replaced by COVARIANCE.P function in Excel 2010) |
COVARIANCE.P | Returns population covariance (ie. the average of the products of deviations for each pair within two supplied data sets) (New in Excel 2010 – replaces COVAR function) |
COVARIANCE.S | Returns sample covariance (ie. the average of the products of deviations for each pair within two supplied data sets) (New in Excel 2010) |
CONFIDENCE | Returns the confidence interval for a population mean, using a normal distribution (Replaced by CONFIDENCE.NORM function in Excel 2010) |
CONFIDENCE.NORM | Returns the confidence interval for a population mean, using a normal distribution (New in Excel 2010 – replaces CONFIDENCE function) |
CONFIDENCE.T | Returns the confidence interval for a population mean, using a Student’s t distribution (New in Excel 2010) |
DEVSQ | Returns the sum of the squares of the deviations of a set of data points from their sample mean |
Trend Line Functions | |
FORECAST | Predicts a future point on a linear trend line fitted to a supplied set of x- and y- values |
INTERCEPT | Calculates the best fit regression line, through a supplied series of x- and y- values and returns the value at which this line intercepts the y-axis |
LINEST | Returns statistical information describing the trend of the line of best fit, through a supplied series of x- and y- values |
SLOPE | Returns 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 supplied set of new x-values |
GROWTH | Returns numbers in a exponential growth trend, based on a set of supplied x- and y- values |
LOGEST | Returns the parameters of an exponential trend for a supplied set of x- and y- values |
STEYX | Returns the standard error of the predicted y-value for each x in the regression line for a set of supplied x- and y- values |
Distribution & Tests of Probability | |
BETADIST | Returns the cumulative beta probability density function (Replaced by BETA.DIST function in Excel 2010) |
BETA.DIST | Returns the cumulative beta distribution function or the beta probability density function (New in Excel 2010 – replaces BETADIST function) |
BETAINV | Returns the inverse of the cumulative beta probability density function (Replaced by BETA.INV function in Excel 2010) |
BETA.INV | Returns the inverse of the cumulative beta probability density function (New in Excel 2010 – replaces BETAINV function) |
BINOMDIST | Returns the individual term binomial distribution probability (Replaced by BINOM.DIST function in Excel 2010) |
BINOM.DIST | Returns the individual term binomial distribution probability (New in Excel 2010 – replaces BINOMDIST function) |
BINOM.DIST.RANGE | Returns the probability of a trial result using a binomial distribution (New in Excel 2013) |
NEGBINOMDIST | Returns the negative binomial distribution (Replaced by NEGBINOM.DIST function in Excel 2010) |
NEGBINOM.DIST | Returns the negative binomial distribution (New in Excel 2010 – replaces NEGBINOMDIST function) |
CRITBINOM | Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value (Replaced by BINOM.INV function in Excel 2010) |
BINOM.INV | Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value (New in Excel 2010 – replaces CRITBINOM function) |
CHIDIST | Returns the right-tailed probability of the chi-squared distribution (Replaced by CHISQ.DIST.RT function in Excel 2010) |
CHISQ.DIST.RT | Returns the right-tailed probability of the chi-squared distribution (New in Excel 2010 – replaces CHIDIST function) |
CHISQ.DIST | Returns the chi-squared distribution (probability density or cumulative distribution function) (New in Excel 2010) |
CHIINV | Returns the inverse of the right-tailed probability of the chi-squared distribution (Replaced by CHISQ.INV.RT function in Excel 2010) |
CHISQ.INV.RT | Returns the inverse of the right-tailed probability of the chi-squared distribution (New in Excel 2010 – replaces CHIINV function) |
CHISQ.INV | Returns the inverse of the left-tailed probability of the chi-squared distribution (New in Excel 2010) |
CHITEST | Returns the chi-squared statistical test for independence (Replaced by CHISQ.TEST function in Excel 2010) |
CHISQ.TEST | Returns the chi-squared statistical test for independence (New in Excel 2010 – replaces CHITEST function) |
CORREL | Returns the correlation coefficient between two sets of values |
EXPONDIST | Returns the exponential distribution (Replaced by EXPON.DIST function in Excel 2010) |
EXPON.DIST | Returns the exponential distribution (New in Excel 2010 – replaces EXPONDIST function) |
FDIST | Returns the right-tailed F probability distribution for two data sets (Replaced by F.DIST.RT function in Excel 2010) |
F.DIST.RT | Returns the right-tailed F probability distribution for two data sets (New in Excel 2010 – replaces FDIST function) |
F.DIST | Returns the F probability distribution (probability density or cumulative distribution function) (New in Excel 2010) |
FINV | Returns the inverse of the right-tailed F probability distribution for two data sets (Replaced by F.INV.RT function in Excel 2010) |
F.INV | Returns the inverse of the Cumulative F distribution (New in Excel 2010) |
FISHER | Returns the Fisher transformation |
FISHERINV | Returns the inverse of the Fisher transformation |
FTEST | Returns the result of an F-Test for 2 supplied data sets (Replaced by F.TEST function in Excel 2010) |
F.TEST | Returns the result of an F-Test for 2 supplied data sets (New in Excel 2010 – replaces FTEST function) |
GAMMADIST | Returns the gamma distribution (Replaced by GAMMA.DIST function in Excel 2010) |
GAMMA.DIST | Returns the gamma distribution (New in Excel 2010 – replaces GAMMADIST function) |
GAMMAINV | Returns the inverse gamma cumulative distribution (Replaced by GAMMA.INV function in Excel 2010) |
GAMMA.INV | Returns the inverse gamma cumulative distribution (New in Excel 2010 – replaces GAMMAINV function) |
GAMMALN | Calculates the natural logarithm of the gamma function for a supplied value |
GAMMALN.PRECISE | Returns the natural logarithm of the gamma function for a supplied value (New in Excel 2010) |
GAMMA | Return the gamma function value for a supplied number (New in Excel 2013) |
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 | Returns the hypergeometric distribution (Replaced by HYPGEOM.DIST function in Excel 2010) |
HYPGEOM.DIST | Returns the hypergeometric distribution (New in Excel 2010 – replaces HYPGEOMDIST function) |
LOGNORMDIST | Returns the cumulative log-normal distribution (Replaced by LOGNORM.DIST function in Excel 2010) |
LOGNORM.DIST | Returns the log-normal probability density function or the cumulative log- normal distribution (New in Excel 2010 – replaces LOGNORMDIST function) |
LOGINV | Returns the inverse of the lognormal distribution (Replaced by LOGNORM.INV function in Excel 2010) |
LOGNORM.INV | Returns the inverse of the lognormal distribution (New in Excel 2010 – replaces LOGINV function) |
NORMDIST | Returns the normal cumulative distribution (Replaced by NORM.DIST function in Excel 2010) |
NORM.DIST | Returns the normal cumulative distribution (New in Excel 2010 – replaces NORMDIST function) |
NORMINV | Returns the inverse of the normal cumulative distribution (Replaced by NORM.INV function in Excel 2010) |
NORM.INV | Returns the inverse of the normal cumulative distribution (New in Excel 2010 – replaces NORMINV function) |
NORMSDIST | Returns the standard normal cumulative distribution (Replaced by NORM.S.DIST function in Excel 2010) |
NORM.S.DIST | Returns the standard normal cumulative distribution (New in Excel 2010 – replaces NORMSDIST function) |
NORMSINV | Returns the inverse of the standard normal cumulative distribution (Replaced by NORM.S.INV function in Excel 2010) |
NORM.S.INV | Returns the inverse of the standard normal cumulative distribution (New in Excel 2010 – replaces NORMSINV function) |
PEARSON | Returns the Pearson product moment correlation coefficient |
RSQ | Returns the square of the Pearson product moment correlation coefficient |
PHI | Returns the value of the density function for a standard normal distribution, for a supplied number (New in Excel 2013) |
POISSON | Returns the Poisson distribution (Replaced by POISSON.DIST function in Excel 2010) |
POISSON.DIST | Returns the Poisson distribution (New in Excel 2010 – replaces POISSON function) |
PROB | Returns the probability that values in a supplied range are within given limits |
SKEW | Returns the skewness of a distribution |
SKEW.P | Returns the skewness of a distribution based on a population (New in Excel 2013) |
STANDARDIZE | Returns a normalized value |
TDIST | Returns the Student’s T-distribution (Replaced by T.DIST.2T & T.DIST.RT functions in Excel 2010) |
T.DIST.2T | Returns the two-tailed Student’s T-distribution (New in Excel 2010 – replaces TDIST function) |
T.DIST.RT | Returns the right-tailed Student’s T-distribution (New in Excel 2010 – replaces TDIST function) |
T.DIST | Returns the Student’s T-distribution (probability density or cumulative distribution function) (New in Excel 2010) |
TINV | Returns the two-tailed inverse of the Student’s T-distribution (Replaced by T.INV.2T function in Excel 2010) |
T.INV.2T | Returns the two-tailed inverse of the Student’s T-distribution (New in Excel 2010 – replaces TINV function) |
T.INV | Returns the left-tailed inverse of the Student’s T-distribution (New in Excel 2010) |
TTEST | Returns the probability associated with a Student’s T-Test (Replaced by T.TEST function in Excel 2010) |
T.TEST | Returns the probability associated with a Student’s T-Test (New in Excel 2010 – replaces TTEST function) |
WEIBULL | Returns the Weibull distribution (Replaced by WEIBULL.DIST function in Excel 2010) |
WEIBULL.DIST | Returns the Weibull distribution (New in Excel 2010 – replaces WEIBULL function) |
ZTEST | Returns the one-tailed probability value of a z-test (Replaced by Z.TEST function in Excel 2010) |
Z.TEST | Returns the one-tailed probability value of a z-test (New in Excel 2010 – replaces ZTEST function) |
Miscellaneous | |
PERMUT | Returns the number of permutations for a given number of objects |
PERMUTATIONA | Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects (New in Excel 2013) |