Hello All. In this section, let’s look into all the Statistical functions available in Excel. Here is the table with Syntax and Usage
| Function | Usage | Syntax |
| AVEDEV | Returns the average of deviations from their mean | =avedev(number1,[number2],..) |
| AVERAGE | Returns the average of selected arguments | =average(number1,[number2],..) |
| AVERAGEA | Returns the average of selected arguments including logical values TRUE or FALSE | =averagea(value1,[value2],..) |
| AVERAGEIF | Returns the average of arguments when a specific condition is met | =averageif(range,criteria,[average_criteria]) |
| AVERAGEIFS | Returns the average of arguments when multiple conditions are met | =averageifs(average_range, criteria_range1, criteria1,..) |
| BETA.DIST | Returns the beta probability distribution | =beta.dist(x,alpha,beta, cumulative, [A],[B]) |
| BETA.INV | Returns the inverse of beta probability distribution | beta.inv(probability,alpha, beta,[A],[B]) |
| BINOM.DIST | Returns the individual term binomial distribution probability | binom.dist(number_s,trials, probability_s, cumulative) |
| BINOM.DIST.RANGE | Returns the probability of trial result using binomial distribution | =binom.dist.range(trials, probability_s, number_s, [number_s2]) |
| BINOM.INV | Returns the smallest value for which the cumulative binomial dist is >= specified criteria | =binom.inv(trials, probability_s, alpha) |
| CHISQ.DIST | Returns the left-tailed probability of Chi-squared dist | =chisq.dist(x,deg_freedom, cumulative) |
| CHISQ.DIST.RT | Returns the right-tailed probability of chi-squared dist | =chisq.dist.rt(x, deg_freedom) |
| CHISQ.INV | Returns the inverse of the left tailed probability of chi-squared dist | =chisq.inv(probability, deg_freedom) |
| CHISQ.INV.RT | Returns the inverse of the right tailed probability of chi-squared dist | =chisq.inv.rt(probability, deg_freedom) |
| CHISQ.TEST | Performs Chi-square test on two dataset and returns the probability | =chisq.test(actual_range, expected_range) |
| CONFIDENCE.NORM | Returns the confidence interval for population mean using normal dist | confidence.norm(alpha, standard_dev,size) |
| CONFIDENCE.T | Returns the confidence interval for population mean using T-dist | =confidence.t(alpha, standard_dev, size) |
| CORREL | Returns the correlation coefficient between 2 data sets | =correl(array1, array2) |
| COUNT | Returns the count of cells in the range that has numbers | =count(value1,[value2],..) |
| COUNTA | Returns the count of cells in the range that are not empty | =counta(value1,[value2],..) |
| COUNTBLANK | Returns the count of empty cells in the range | =countblank(range) |
| COUNTIF | Returns the count of cells in a range that meets a condition | =countif(range, criteria) |
| COUNTIFS | Returns the count of cells in a range that meets 1 or more conditions | =countifs(criteria_range1, criteria1,…) |
| COVARIANCE.P | Returns population covariance | =covariance.p(array1, array2) |
| COVARIANCE.S | Returns sample covariance | =covariance.s(array1, array2) |
| DEVSQ | Returns the sum of squared deviations from sample mean | =devsq(number1,[number2],..) |
| EXPON.DIST | Returns the exponential distribution | expon.dist(x, lambda, cumulative) |
| F.DIST | Returns the left tailed F probability dist of 2 data sets | =f.dist(x, deg_freedom1, deg_freedom2, cumulative) |
| F.DIST.RT | Returns the right tailed F probability dist of 2 data sets | =f.dist.rt(x, deg_freedom1, deg_freedom2) |
| F.INV | Returns the inverse of left tailed F probability distribution | =f.inv(probability, deg_freedom1, deg_freedom2) |
| F.INV.RT | Returns the inverse of right tailed F probability distribution | =f.inv.rt(probability, deg_freedom1, deg_freedom2) |
| F.TEST | Returns the result of F-test | =f.test(array1, array2) |
| FISHER | Returns the Fisher transformation | =fisher(x) |
| FISHERINV | Returns the inverse of Fisher transformation | =fisherinv(y) |
| FORECAST.ETS | Returns the forecasted value for a specific future target date | =forecast.ets(target_date, values, timeline, [seasonality],[data_completion], [aggregation]) |
| FORECAST.ETS.CONFINT | Returns 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.SEASONALITY | Returns the length of repetitive pattern | =forecast.ets.seasonality(values, timeline, [data_completion], [aggregation]) |
| FORECAST.ETS.STAT | Returns the requested statistic for the forecast | =forecast.ets.stat(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation]) |
| FORECAST.LINEAR | Calculates a future values along a linear trend | =forecast.linear(x, known_ys, known_xs) |
| FREQUENCY | Returns the frequency of values in dataset | =frequency(data_array, bin_array) |
| GAMMA | Returns Gamma value | =gamma(x) |
| GAMMA.DIST | Returns the gamma distribution | =gamma.dist(x, alpha, beta, cumulative) |
| GAMMA.INV | Returns the inverse of gamma dist | =gamma.inv(probability, alpha, beta) |
| GAMMALN | Returns the natural logarithm of gamma function | =gammaln(x) |
| GAMMALN.PRECISE | Returns the natural logarithm of gamma function | =gammaln.precise(x) |
| GAUSS | Returns 0.5 less than std normal cumulative dist | =gauss(x) |
| GEOMEAN | Returns the geometric mean of an array | =geomean(number1, [number2],..) |
| GROWTH | Returns numbers in exponential growth trend matching data points | =growth(known_ys, [known_xs], [new_xs], [const]) |
| HARMEAN | Returns the harmonic mean of data set | =harmean(number1, [number2],..) |
| HYPGEOM.DIST | Returns the hypergeometric distribution | =hypgeon.dist(sample_s, number_sample, population_s, number_pop, cumulative) |
| INTERCEPT | Calculates the intercept of linear regression line | =intercept(known_ys, known_xs) |
| KURT | Returns the kurtosis of a data set | =kurt(number1, [number2],..) |
| LARGE | Returns the k-th largest values in a data set | =large(array,k) |
| LINEST | Returns statistics that describe a linear trend | =linest(known_ys, [known_xs], [const], [stats]) |
| LOGEST | Returns statistics that describe an exponential curve | =logest(known_ys, [known_xs], [const], [stats]) |
| LOGNORM.DIST | Returns the lognormal distribution of x | =lognorm.dist(x, mean, standard_dev, cumulative) |
| LOGNORM.INV | Returns the inverse of lognormal cumulative distribution of x | =lognorm.inv(probability, mean, standard_dev) |
| MAX | Returns the largest value in a set | =max(number1, [number2],..) |
| MAXA | Returns the largest value in a set including logical value | =maxa(value1, [value2],..) |
| MAXIFS | Returns the largest value in a set specified by given condition | =maxifs(max_range, criteria_range1, criteria1,..) |
| MEDIAN | Returns the median value | =median(number1, [number2],..) |
| MIN | Returns the smallest number | =min(number1, [number2],..) |
| MINA | Returns the smallest number including logical value | =mina(value1, [value2],..) |
| MINIFS | Returns the smallest number given set of conditions are met | =minifs(min_range, criteria_range1, criteria1,..) |
| MODE.MULT | Returns a vertical array of repetitive values in the range | =mode.mult(number1, [number2],..) |
| MODE.SNGL | Returns most frequently occurring value | =mode.sngl(number1, [number2],..) |
| NEGBINOM.DIST | Returns the negative binomial distribution | =negbinom.dist(number_f, number_s, probability_s, cumulative) |
| NORM.DIST | Returns the normal distribution | =norm.dist(x, mean, standard_dev, cumulative) |
| NORM.INV | Returns the inverse of normal cumulative distribution | =norm.inv(probability, mean, standard_dev) |
| NORM.S.DIST | Returns the standard normal distribution | =norm.s.dist(z, cumulative) |
| NORM.S.INV | Returns the inverse of standard normal cumulative distribution | =norms.s.inv(probability) |
| PEARSON | Returns the pearson product moment correlation coefficient | =pearson(array1, array2) |
| PERCENTILE.EXC | Returns the kth percentile of values in range (0..1 exclusive) | =percentile.exc(array,k) |
| PERCENTILE.INC | Returns the kth percentile of values in range (0..1 inclusive) | =percentile.inc(array,k) |
| PERCENTRANK.EXC | Returns the percentile rank exclusive of range 0..1 | =percentrank.exc(array,x,[significance]) |
| PERCENTRANK.INC | Returns the percentile rank inclusive of range 0..1 | =percentrank.inc(array,x, [significance]) |
| PERMUT | Returns the total number of permutations | =permut(number, number_chosen) |
| PERMUTATIONA | Returns the number of permutations with repetitions | permuatationa(number, number_chosen) |
| PHI | Returns density function for a standard normal distribution | =phi(x) |
| POISSON.DIST | Returns the poisson distribution | =poisson.dist(x, mean, cumulative) |
| PROB | Returns the probability in a range between two limits | =prob(x_range, prob_range, lower_limit, [upper_limit]) |
| QUARTILE.EXC | Returns the quartile of data set – exclusive | =quartile.exc(array, quart) |
| QUARTILE.INC | Returns the quartile of data set – inclusive | =quartile.inc(array, quart) |
| RANK.AVG | Returns 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.EQ | Returns 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]) |
| RSQ | Returns the square of pearson product moment correlation coefficient | =rsq(known_ys, konwn_xs) |
| SKEW | Returns the skewness of a distribution | =skew(number1, [number2],..) |
| SKEW.P | Returns the skewness of a distribution based on population | =skew.p(number1, [number2],..) |
| SLOPE | Returns the slope of linear regression line | =slope(known_ys, known_xs) |
| SMALL | Return the k-th smallest value | =small(array,k) |
| STANDARDIZE | Returns a normalized value from distribution | =standardize(x, mean, standard_dev) |
| STDEV.P | Calculates standard deviation based on entire population | =stdev.p(number1, [number2],..) |
| STDEV.S | Estimates standard deviation based on sample | =stdev.s(number1, [number2],..) |
| STDEVA | Estimates standard deviation based on sample including logical values | =stdeva(value1, [value2],..) |
| STDEVPA | Calculates standard deviation based on entire population including logical values | =stdevpa(value1, [value2],..) |
| STEYX | Returns standard error | =steyx(known_ys, known_xs) |
| T.DIST | Returns the left tailed student’s t-dist | =t.dist(x, deg_freedom, cumulative) |
| T.DIST.2T | Returns two tailed student’s t-dist | =t.dist.2t(x, deg_freedom) |
| T.DIST.RT | Returns the right tailed student’s t-dist | =t.dist.rt(x, deg_freedom) |
| T.INV | Returns the left tailed inverse of student’s t-dist | =t.inv(probability, deg_freedom) |
| T.INV.2T | Returns the two tailed inverse of student’s t-dist | =t.inv.2t(probability, deg_freedom) |
| T.TEST | Returns the probability associated with student’s T-dist | =t.test(array1, array2, tails, type) |
| TREND | Return numbers in a linear trend | =trend(known_ys, [known_xs], [new_xs], [const]) |
| TRIMMEAN | Returns the mean of interior portion | =trimmean(array, percent) |
| VAR.P | Calculates variance based on entire population | =var.p(number1, [number2],..) |
| VAR.S | Estimates variance based on a sample | =var.s(number1, [number2],..) |
| VARA | Estimates variance based on a sample including logical values | =vara(value1, [value2],..) |
| VARPA | Calculates variance based on entire population including logical values | =varpa(value1, [value2],..) |
| WEIBULL.DIST | Returns the Weibull distribution | =weibull.dist(x, alpha, beta, cumulative) |
| Z.TEST | Returns 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.