Statistical Functions in Excel

Hello All. In this section, let’s look into all the Statistical functions available in Excel. Here is the table with Syntax and Usage

FunctionUsageSyntax
AVEDEVReturns the average of deviations from their mean=avedev(number1,[number2],..)
AVERAGEReturns the average of selected arguments=average(number1,[number2],..)
AVERAGEAReturns the average of selected arguments including logical values TRUE or FALSE=averagea(value1,[value2],..)
AVERAGEIFReturns the average of arguments when a specific condition is met=averageif(range,criteria,[average_criteria])
AVERAGEIFSReturns the average of arguments when multiple conditions are met=averageifs(average_range, criteria_range1, criteria1,..)
BETA.DISTReturns the beta probability distribution=beta.dist(x,alpha,beta, cumulative, [A],[B])
BETA.INVReturns the inverse of beta probability distributionbeta.inv(probability,alpha, beta,[A],[B])
BINOM.DISTReturns the individual term binomial distribution probabilitybinom.dist(number_s,trials, probability_s, cumulative)
BINOM.DIST.RANGEReturns the probability of trial result using binomial distribution=binom.dist.range(trials, probability_s, number_s, [number_s2])
BINOM.INVReturns the smallest value for which the cumulative binomial dist is >= specified criteria=binom.inv(trials, probability_s, alpha)
CHISQ.DISTReturns the left-tailed probability of Chi-squared dist=chisq.dist(x,deg_freedom, cumulative)
CHISQ.DIST.RTReturns the right-tailed probability of chi-squared dist=chisq.dist.rt(x, deg_freedom)
CHISQ.INVReturns the inverse of the left tailed probability of chi-squared dist=chisq.inv(probability, deg_freedom)
CHISQ.INV.RTReturns the inverse of the right tailed probability of chi-squared dist=chisq.inv.rt(probability, deg_freedom)
CHISQ.TESTPerforms Chi-square test on two dataset and returns the probability=chisq.test(actual_range, expected_range)
CONFIDENCE.NORMReturns the confidence interval for population mean using normal distconfidence.norm(alpha, standard_dev,size)
CONFIDENCE.TReturns the confidence interval for population mean using T-dist=confidence.t(alpha, standard_dev, size)
CORRELReturns the correlation coefficient between 2 data sets=correl(array1, array2)
COUNTReturns the count of cells in the range that has numbers=count(value1,[value2],..)
COUNTAReturns the count of cells in the range that are not empty=counta(value1,[value2],..)
COUNTBLANKReturns the count of empty cells in the range=countblank(range)
COUNTIFReturns the count of cells in a range that meets a condition=countif(range, criteria)
COUNTIFSReturns the count of cells in a range that meets 1 or more conditions =countifs(criteria_range1, criteria1,…)
COVARIANCE.PReturns population covariance=covariance.p(array1, array2)
COVARIANCE.SReturns sample covariance=covariance.s(array1, array2)
DEVSQReturns the sum of squared deviations from sample mean=devsq(number1,[number2],..)
EXPON.DISTReturns the exponential distributionexpon.dist(x, lambda, cumulative)
F.DISTReturns the left tailed F probability dist of 2 data sets=f.dist(x, deg_freedom1, deg_freedom2, cumulative)
F.DIST.RTReturns the right tailed F probability dist of 2 data sets=f.dist.rt(x, deg_freedom1, deg_freedom2)
F.INVReturns the inverse of left tailed F probability distribution=f.inv(probability, deg_freedom1, deg_freedom2)
F.INV.RTReturns the inverse of right tailed F probability distribution=f.inv.rt(probability, deg_freedom1, deg_freedom2)
F.TESTReturns the result of F-test=f.test(array1, array2)
FISHERReturns the Fisher transformation=fisher(x)
FISHERINVReturns the inverse of Fisher transformation=fisherinv(y)
FORECAST.ETSReturns the forecasted value for a specific future target date=forecast.ets(target_date, values, timeline, [seasonality],[data_completion], [aggregation])
FORECAST.ETS.CONFINTReturns a confidence interval for the forecast value at target date=forecast.ent.confint(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])
FORECAST.ETS.SEASONALITYReturns the length of repetitive pattern=forecast.ets.seasonality(values, timeline, [data_completion], [aggregation])
FORECAST.ETS.STATReturns the requested statistic for the forecast=forecast.ets.stat(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation])
FORECAST.LINEARCalculates a future values along a linear trend=forecast.linear(x, known_ys, known_xs)
FREQUENCYReturns the frequency of values in dataset=frequency(data_array, bin_array)
GAMMAReturns Gamma value=gamma(x)
GAMMA.DISTReturns the gamma distribution=gamma.dist(x, alpha, beta, cumulative)
GAMMA.INVReturns the inverse of gamma dist=gamma.inv(probability, alpha, beta)
GAMMALNReturns the natural logarithm of gamma function=gammaln(x)
GAMMALN.PRECISEReturns the natural logarithm of gamma function=gammaln.precise(x)
GAUSSReturns 0.5 less than std normal cumulative dist=gauss(x)
GEOMEANReturns the geometric mean of an array=geomean(number1, [number2],..)
GROWTHReturns numbers in exponential growth trend matching data points=growth(known_ys, [known_xs], [new_xs], [const])
HARMEANReturns the harmonic mean of data set=harmean(number1, [number2],..)
HYPGEOM.DISTReturns the hypergeometric distribution=hypgeon.dist(sample_s, number_sample, population_s, number_pop, cumulative)
INTERCEPTCalculates the intercept of linear regression line=intercept(known_ys, known_xs)
KURTReturns the kurtosis of a data set=kurt(number1, [number2],..)
LARGEReturns the k-th largest values in a data set=large(array,k)
LINESTReturns statistics that describe a linear trend=linest(known_ys, [known_xs], [const], [stats])
LOGESTReturns statistics that describe an exponential curve=logest(known_ys, [known_xs], [const], [stats])
LOGNORM.DISTReturns the lognormal distribution of x=lognorm.dist(x, mean, standard_dev, cumulative)
LOGNORM.INVReturns the inverse of lognormal cumulative distribution of x=lognorm.inv(probability, mean, standard_dev)
MAXReturns the largest value in a set=max(number1, [number2],..)
MAXAReturns the largest value in a set including logical value=maxa(value1, [value2],..)
MAXIFSReturns the largest value in a set specified by given condition=maxifs(max_range, criteria_range1, criteria1,..)
MEDIANReturns the median value=median(number1, [number2],..)
MINReturns the smallest number =min(number1, [number2],..)
MINAReturns the smallest number including logical value=mina(value1, [value2],..)
MINIFSReturns the smallest number given set of conditions are met=minifs(min_range, criteria_range1, criteria1,..)
MODE.MULTReturns a vertical array of repetitive values in the range =mode.mult(number1, [number2],..)
MODE.SNGLReturns most frequently occurring value=mode.sngl(number1, [number2],..)
NEGBINOM.DISTReturns the negative binomial distribution=negbinom.dist(number_f, number_s, probability_s, cumulative)
NORM.DISTReturns the normal distribution=norm.dist(x, mean, standard_dev, cumulative)
NORM.INVReturns the inverse of normal cumulative distribution=norm.inv(probability, mean, standard_dev)
NORM.S.DISTReturns the standard normal distribution=norm.s.dist(z, cumulative)
NORM.S.INVReturns the inverse of standard normal cumulative distribution=norms.s.inv(probability)
PEARSONReturns the pearson product moment correlation coefficient=pearson(array1, array2)
PERCENTILE.EXCReturns the kth percentile of values in range (0..1 exclusive)=percentile.exc(array,k)
PERCENTILE.INCReturns the kth percentile of values in range (0..1 inclusive)=percentile.inc(array,k)
PERCENTRANK.EXCReturns the percentile rank exclusive of range 0..1=percentrank.exc(array,x,[significance])
PERCENTRANK.INCReturns the percentile rank inclusive of range 0..1=percentrank.inc(array,x, [significance])
PERMUTReturns the total number of permutations=permut(number, number_chosen)
PERMUTATIONAReturns the number of permutations with repetitionspermuatationa(number, number_chosen)
PHIReturns density function for a standard normal distribution=phi(x)
POISSON.DISTReturns the poisson distribution=poisson.dist(x, mean, cumulative)
PROBReturns the probability in a range between two limits=prob(x_range, prob_range, lower_limit, [upper_limit])
QUARTILE.EXCReturns the quartile of data set – exclusive=quartile.exc(array, quart)
QUARTILE.INCReturns the quartile of data set – inclusive=quartile.inc(array, quart)
RANK.AVGReturns rank of a number in a list. Average rank is returned if more than one value has same rank=rank.avg(number, ref, [order])
RANK.EQReturns rank of a number in a list. Top rank of the set is returned if more than one value has same rank=rank.eq(number, ref, [order])
RSQReturns the square of pearson product moment correlation coefficient=rsq(known_ys, konwn_xs)
SKEWReturns the skewness of a distribution=skew(number1, [number2],..)
SKEW.PReturns the skewness of a distribution based on population=skew.p(number1, [number2],..)
SLOPEReturns the slope of linear regression line=slope(known_ys, known_xs)
SMALLReturn the k-th smallest value=small(array,k)
STANDARDIZEReturns a normalized value from distribution=standardize(x, mean, standard_dev)
STDEV.PCalculates standard deviation based on entire population=stdev.p(number1, [number2],..)
STDEV.SEstimates standard deviation based on sample=stdev.s(number1, [number2],..)
STDEVAEstimates standard deviation based on sample including logical values=stdeva(value1, [value2],..)
STDEVPACalculates standard deviation based on entire population including logical values=stdevpa(value1, [value2],..)
STEYXReturns standard error=steyx(known_ys, known_xs)
T.DISTReturns the left tailed student’s t-dist=t.dist(x, deg_freedom, cumulative)
T.DIST.2TReturns two tailed student’s t-dist=t.dist.2t(x, deg_freedom)
T.DIST.RTReturns the right tailed student’s t-dist=t.dist.rt(x, deg_freedom)
T.INVReturns the left tailed inverse of student’s t-dist=t.inv(probability, deg_freedom)
T.INV.2TReturns the two tailed inverse of student’s t-dist=t.inv.2t(probability, deg_freedom)
T.TESTReturns the probability associated with student’s T-dist=t.test(array1, array2, tails, type)
TRENDReturn numbers in a linear trend=trend(known_ys, [known_xs], [new_xs], [const])
TRIMMEANReturns the mean of interior portion=trimmean(array, percent)
VAR.PCalculates variance based on entire population=var.p(number1, [number2],..)
VAR.SEstimates variance based on a sample=var.s(number1, [number2],..)
VARAEstimates variance based on a sample including logical values=vara(value1, [value2],..)
VARPACalculates variance based on entire population including logical values=varpa(value1, [value2],..)
WEIBULL.DISTReturns the Weibull distribution=weibull.dist(x, alpha, beta, cumulative)
Z.TESTReturns the one tailed P-value of a Z test=z.test(array,x,[sigma])

I hope this article was helpful to you. Please let us know your feedback that would help in improving the content. Thanks for reading.

Leave a Reply

%d bloggers like this: