Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles Statistical functions Excel Statistical Functions – Excelchat

Excel Statistical Functions – Excelchat

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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc