Excel Statistical Functions

  • Post author:
  • Post category:Uncategorized

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
COUNTCalculates the number of numerical values in a given set of values or cells
COUNTACalculates the number of non-blanks in a given set of values or cells
COUNTBLANKCalculates the number of blank cells in a specified range
COUNTIFCalculates the number of cells in a range that satisfy a specified condition
COUNTIFSCalculates the number of cells in a range that satisfies a set of conditions  (New in Excel 2007)
FREQUENCYCalculates an array displaying the number of values in a range that falls within specified ranges

Table 2 – Permutations │Statistical Functions in Excel

PERMUTATIONS
PERMUTPermutations of a specified data
PERMUTATIONANumber 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
CONFIDENCEConfidence interval for the mean population from a distribution (changed to Confidence.Norm in Excel 2010)
CONFIDENCE.NORMConfidence interval for the mean population in a normal distribution (New in Excel 2010 changed to the CONFIDENCE function)
CONFIDENCE.TConfidence 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
PERCENTILECalculates 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.INCCalculates 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.EXCCalculates the K’th percentile of values in a dataset where K is in the range 0 to 1 (exclusive) (New in Excel 2010)
QUARTILECalculates the itemized quartile of a data set using the percentile value 0 to 1 (inclusive) (Changed to QUARTILE.INC function in Excel 2010)
QUARTILE.INCCalculates the itemized quartile of a dataset using the percentile value 0 to 1 (inclusive) (New in Excel 2010 – Changed to the QUARTILE function)
QUARTILE.EXCSpecified quartile of a data set using percentile value 0 – 1 (exclusive) (New in Excel 2010)
RANKStatistical rank of a given value within a dataset (Changed to Rank.Eq function in Excel 2010)
RANK.EQCalculates 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.AVGCalculates 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)
PERCENTRANKCalculates rank of a value in a data set, as a percentage (0 – 1 inclusive) (Changed to Percentrank.Inc function in Excel 2010)
PERCENTRANK.INCCalculates rank of a value in a data set as a percentage (0 – 1 inclusive) (New in Excel 2010 – replaces the Percentrank function)
PERCENTRANK.EXCCalculates 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
AVEDEVAverage of the absolute deviations of data arguments from their mean
DEVSQCalculates sum of the squares of the deviations of a set of data arguments from a given sample mean
STDEVCalculates standard deviation of a data set (which denotes a sample of a population) (Changed to Stdev.S function in Excel 2010)
STDEV.SCalculates standard deviation of a data set (which denotes a sample of a population) (New in Excel 2010 – replaces the Stdev function)
STDEVACalculates 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
STDEVPCalculates standard deviation of a data set (which denotes an entire population) (Changed to Stdev.P function in Excel 2010)
STDEV.PCalculates standard deviation of a data set (which denotes an entire population) (New in Excel 2010 – substitutes the Stdevp function)
STDEVPACalculates 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
VARCalculates variance of a data set (which represent a sample population) (Changed to Var.S function in Excel 2010)
VAR.SCalculates variance of a data set (which denotes a sample population) (New in Excel 2010 – replaces the Var function)
VARACalculates 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
VARPCalculates variance of a data set (which represents a whole population) (Changed to Var.P function in Excel 2010)
VAR.PCalculates variance of a data set (which represent a whole population) (New in Excel 2010 – replaces the Varp function)
VARPACalculates 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
COVARCalculates 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.PCalculates 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.SCalculates 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
FORECASTPredicts 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.ETSUses 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.CONFINTCalculates confidence interval for a forecast value at a given marked date (New in Excel 2016 – not offered in Mac Excel 2016)
FORECAST.ETS.SEASONALITYCalculates 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.STATCalculates the statistical value related of time series forecasting (New in Excel 2016 – not offered in Excel 2016 for Mac)
FORECAST.LINEARPredicts 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)
INTERCEPTCalculates 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
LINESTCalculates the statistical information relating the trend of the line of best fit, through a given data set of x- and y- values
SLOPECalculates the slope of the linear regression line through a supplied series of x- and y- values
TRENDCalculates the trend line through a given set of y-values and returns additional y-values for a data set of new x-values
GROWTHCalculates the numbers in a exponential growth trend, based on a given of x- and y- values
LOGESTCalculates the parameters of an exponential trend for a given x- and y- values
STEYXCalculates 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
MAXCalculates largest value from a list of data sets
MAXACalculates 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
MAXIFSCalculates largest value from a subset of values in a list that are detailed with respect to one or more conditions. (New in Excel 2019)
MINCalculates smallest value from a data set
MINACalculates 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
MINIFSCalculates smallest value from a subset of values in a list that are specified with respect to one or more conditions. (New in Excel 2019)
LARGECalculates Kth LARGEST value from a dataset, for a given value K
SMALLCalculates Kth SMALLEST value from a list of dataset, for a given value K

Table 8- Averages │Excel Statistical Functions

Averages
AVERAGECalculates Average of a list of data sets
AVERAGEACalculates 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
AVERAGEIFCalculates the Average of the cells in a given range, that satisfies a given criteria (New in Excel 2007)
AVERAGEIFSCalculates the Average of the cells in a given range, that satisfies multiple criteria (New in Excel 2007)
MEDIANCalculates Median (the middle value) of a specified data set
MODECalculates Mode (the most frequently occurring value) of a data set (Changed to MODE.SNGL function in Excel 2010)
MODE.SNGLCalculates Mode (the most frequently occurring value) of a data set (New in Excel 2010 – replaces the MODE function)
MODE.MULTCalculates a vertical array of the most frequently occurring values in an array or range of data (New in Excel 2010)
GEOMEANCalculates geometric mean of a specified data set
HARMEANCalculates harmonic mean of a set of specified data set
TRIMMEANCalculates mean of the interior of a data set

Table 9 – Distribution & Probability tests │Excel Statistical Functions

Distribution & Probability tests
BETADISTCalculates cumulative beta probability density function (Changed to BETA.DIST function in Excel 2010)
BETA.DISTCalculates cumulative beta distribution function or the beta probability density function (New in Excel 2010 – replaces the BETADIST function)
BETAINVCalculates inverse of the cumulative beta probability density function (Changed to BETA.INV function in Excel 2010)
BETA.INVCalculates inverse of the cumulative beta probability density function (New in Excel 2010 – replaces the BETAINV function)
BINOMDISTCalculates individual term binomial distribution probability (Changed to BINOM.DIST function in Excel 2010)
BINOM.DISTCalculates individual term binomial distribution probability (New in Excel 2010 – replaces the BINOMDIST function)
BINOM.DIST.RANGECalculates probability of a trial result using a binomial distribution (New in Excel 2013)
NEGBINOMDISTCalculates negative binomial distribution (Changed to NEGBINOM.DIST function in Excel 2010)
NEGBINOM.DISTCalculates negative binomial distribution (New in Excel 2010 – replaces the NEGBINOMDIST function)
CRITBINOMCalculates 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.INVCalculates 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)
CHIDISTCalculates right-tailed probability of the chi-squared distribution (Changed to CHISQ.DIST.RT function in Excel 2010)
CHISQ.DIST.RTCalculates right-tailed probability of the chi-squared distribution (New in Excel 2010 – replaces the CHIDIST function)
CHISQ.DISTCalculates chi-squared distribution (probability density or cumulative distribution function) (New in Excel 2010)
CHIINVCalculates inverse of the right-tailed probability of the chi-squared distribution (Changed to CHISQ.INV.RT function in Excel 2010)
CHISQ.INV.RTCalculates inverse of the right-tailed probability of the chi-squared distribution (New in Excel 2010 – replaces the CHIINV function)
CHISQ.INVCalculates inverse of the left-tailed probability of the chi-squared distribution (New in Excel 2010)
CHITESTCalculates chi-squared statistical test for independence (Changed to CHISQ.TEST function in Excel 2010)
CHISQ.TESTCalculates chi-squared statistical test for independence (New in Excel 2010 – replaces the CHITEST function)
CORRELCalculates correlation coefficient between two sets of values
EXPONDISTCalculates exponential distribution (Changed to EXPON.DIST function in Excel 2010)
EXPON.DISTCalculates exponential distribution (New in Excel 2010 – replaces the EXPONDIST function)
FDISTCalculates right-tailed F probability distribution for two data sets (Changed to F.DIST.RT function in Excel 2010)
F.DIST.RTCalculates right-tailed F probability distribution for two data sets (New in Excel 2010 – replaces the FDIST function)
F.DISTCalculates F probability distribution (cumulative distribution or probability density function) (New in Excel 2010)
FINVCalculates inverse of the right-tailed F probability distribution for two data sets (Changed to F.INV.RT function in Excel 2010)
F.INV.RTCalculates inverse of the right-tailed F probability distribution for two data sets (New in Excel 2010 – replaces the FINV function)
F.INVCalculates inverse of the Cumulative F distribution (New in Excel 2010)
FISHERCalculates Fisher transformation
FISHERINVCalculates inverse of the Fisher transformation
FTESTCalculates outcome of an F-Test for two data sets (Changed to F.TEST function in Excel 2010)
F.TESTCalculates outcome of an F-Test for two data sets (New in Excel 2010 – replaces the FTEST function)
GAMMADISTCalculates gamma distribution (Changed to GAMMA.DIST function in Excel 2010)
GAMMA.DISTCalculates gamma distribution (New in Excel 2010 – replaces the GAMMADIST function)
GAMMAINVCalculates inverse gamma cumulative distribution (Changed to GAMMA.INV function in Excel 2010)
GAMMA.INVCalculates inverse gamma cumulative distribution (New in Excel 2010 – replaces the GAMMAINV function)
GAMMACalculates gamma function value for a given data set (New in Excel 2013)
GAMMALNCalculates the natural logarithm of the gamma function for a supplied value
GAMMALN.PRECISECalculates natural logarithm of the gamma function for a supplied value (New in Excel 2010)
GAUSSCalculates 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)
HYPGEOMDISTCalculates hypergeometric distribution (Changed to HYPGEOM.DIST function in Excel 2010)
HYPGEOM.DISTCalculates hypergeometric distribution (New in Excel 2010 – replaces the HYPGEOMDIST function)
KURTCalculates kurtosis of a data set
LOGNORMDISTCalculates cumulative log-normal distribution (Changed to LOGNORM.DIST function in Excel 2010)
LOGNORM.DISTCalculates log-normal probability density function or the cumulative log- normal distribution (New in Excel 2010 – replaces the LOGNORMDIST function)
LOGINVCalculates inverse of the lognormal distribution (Changed to LOGNORM.INV function in Excel 2010)
LOGNORM.INVCalculates inverse of the lognormal distribution (New in Excel 2010 – replaces the LOGINV function)
NORMDISTCalculates normal cumulative distribution (Changed to NORM.DIST function in Excel 2010)
NORM.DISTCalculates normal cumulative distribution (New in Excel 2010 – replaces the NORMDIST function)
NORMINVCalculates inverse of the normal cumulative distribution (Changed to NORM.INV function in Excel 2010)
NORM.INVCalculates inverse of the normal cumulative distribution (New in Excel 2010 – replaces the NORMINV function)
NORMSDISTCalculates standard normal cumulative distribution (Changed to NORM.S.DIST function in Excel 2010)
NORM.S.DISTCalculates standard normal cumulative distribution (New in Excel 2010 – replaces the NORMSDIST function)
NORMSINVCalculates inverse of the standard normal cumulative distribution (Changed to NORM.S.INV function in Excel 2010)
NORM.S.INVCalculates inverse of the standard normal cumulative distribution (New in Excel 2010 – replaces the NORMSINV function)
PEARSONCalculates Pearson product moment correlation coefficient
RSQCalculates square of the Pearson product moment correlation coefficient
PHICalculates value of the density function for a standard normal distribution, for a supplied number (New in Excel 2013)
POISSONCalculates Poisson distribution (Changed to POISSON.DIST function in Excel 2010)
POISSON.DISTCalculates Poisson distribution (New in Excel 2010 – replaces the POISSON function)
PROBCalculates probability that values in a given range are within given limits
SKEWCalculates skewness of a distribution
SKEW.PCalculates skewness of a distribution based on a population (New in Excel 2013)
STANDARDIZECalculates a normalized value
TDISTCalculates Student’s T-distribution (Changed to T.DIST.2T & T.DIST.RT functions in Excel 2010)
T.DIST.2TCalculates two-tailed Student’s T-distribution (New in Excel 2010 – replaces the TDIST function)
T.DIST.RTCalculates right-tailed Student’s T-distribution (New in Excel 2010 – replaces the TDIST function)
T.DISTCalculates Student’s T-distribution (probability density or cumulative distribution function) (New in Excel 2010)
TINVCalculates two-tailed inverse of the Student’s T-distribution (Changed to T.INV.2T function in Excel 2010)
T.INV.2TCalculates two-tailed inverse of the Student’s T-distribution (New in Excel 2010 – replaces the TINV function)
T.INVCalculates left-tailed inverse of the Student’s T-distribution (New in Excel 2010)
TTESTCalculates probability connected to the Student’s T-Test (Changed to T.TEST function in Excel 2010)
T.TESTCalculates probability connected to the Student’s T-Test (New in Excel 2010 – replaces the TTEST function)
WEIBULLCalculates Weibull distribution (Changed to WEIBULL.DIST function in Excel 2010)
WEIBULL.DISTCalculates Weibull distribution (New in Excel 2010 – replaces the WEIBULL function)
ZTESTCalculates one-tailed probability value of a z-test (Changed to Z.TEST function in Excel 2010)
Z.TESTCalculates one-tailed probability value of a z-test (New in Excel 2010 – replaces the ZTEST function)