stats functions
distributions, averages, spread and regression.
| function | signature | what it does | tests |
|---|---|---|---|
| AVEDEV | AVEDEV(number…) | average of absolute deviations from the mean | — |
| AVERAGEA | AVERAGEA(value…) | averages values including text and logicals | — |
| AVERAGEIF | AVERAGEIF(range, criteria, [average_range]) | averages cells that match a condition | — |
| AVERAGEIFS | AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2…], [criteria2…]) | averages cells matching multiple conditions | — |
| BETA.DIST | BETA.DIST(x, alpha, beta, cumulative, [lower_bound], [upper_bound]) | beta distribution | — |
| BETA.INV | BETA.INV(probability, alpha, beta, [lower_bound], [upper_bound]) | inverse of the beta distribution | — |
| BETADIST | BETADIST(x, alpha, beta, [lower_bound], [upper_bound]) | legacy form of BETA.DIST (cumulative) | — |
| BETAINV | BETAINV(probability, alpha, beta, [lower_bound], [upper_bound]) | legacy form of BETA.INV | — |
| BINOM.DIST | BINOM.DIST(number_s, trials, probability_s, cumulative) | binomial distribution probability | — |
| BINOM.DIST.RANGE | BINOM.DIST.RANGE(trials, probability_s, number_s, [number_s2]) | probability of a trial result range | — |
| BINOM.INV | BINOM.INV(trials, probability_s, alpha) | smallest value with cumulative binomial at or above a criterion | — |
| BINOMDIST | BINOMDIST(number_s, trials, probability_s, cumulative) | legacy form of BINOM.DIST | — |
| CHIDIST | CHIDIST(x, deg_freedom) | legacy form of CHISQ.DIST.RT | — |
| CHIINV | CHIINV(probability, deg_freedom) | legacy form of CHISQ.INV.RT | — |
| CHISQ.DIST | CHISQ.DIST(x, deg_freedom, cumulative) | returns the chi-squared distribution | — |
| CHISQ.DIST.RT | CHISQ.DIST.RT(x, deg_freedom) | returns the right-tailed chi-squared distribution | — |
| CHISQ.INV | CHISQ.INV(probability, deg_freedom) | returns the inverse of the chi-squared distribution | — |
| CHISQ.INV.RT | CHISQ.INV.RT(probability, deg_freedom) | returns the inverse of the right-tailed chi-squared distribution | — |
| CHISQ.TEST | CHISQ.TEST(actual_range, expected_range) | chi-squared test for independence | — |
| CHITEST | CHITEST(actual_range, expected_range) | legacy form of CHISQ.TEST | — |
| CONFIDENCE | CONFIDENCE(alpha, standard_dev, size) | legacy form of CONFIDENCE.NORM | — |
| CONFIDENCE.NORM | CONFIDENCE.NORM(alpha, standard_dev, size) | confidence interval for a population mean, normal | — |
| CONFIDENCE.T | CONFIDENCE.T(alpha, standard_dev, size) | confidence interval for a population mean, Student's t | — |
| CORREL | CORREL(array1, array2) | correlation coefficient between two data sets | — |
| COUNT | COUNT(value…) | counts cells containing numbers | 4 |
| COUNTA | COUNTA(value…) | counts non-empty cells in a range | — |
| COUNTBLANK | COUNTBLANK(range) | counts empty cells in a range | — |
| COUNTIF | COUNTIF(range, criteria) | counts cells that match a condition | 11 |
| COUNTIFS | COUNTIFS(criteria_range1, criteria1, [criteria_range2…], [criteria2…]) | counts cells matching multiple conditions | 1 |
| COVAR | COVAR(array1, array2) | legacy form of COVARIANCE.P: population covariance | — |
| COVARIANCE.P | COVARIANCE.P(array1, array2) | population covariance | — |
| COVARIANCE.S | COVARIANCE.S(array1, array2) | sample covariance | — |
| CRITBINOM | CRITBINOM(trials, probability_s, alpha) | legacy form of BINOM.INV | — |
| DEVSQ | DEVSQ(number…) | sum of squared deviations from the mean | — |
| EXPON.DIST | EXPON.DIST(x, lambda, cumulative) | exponential distribution | — |
| EXPONDIST | EXPONDIST(x, lambda, cumulative) | legacy form of EXPON.DIST | — |
| F.DIST | F.DIST(x, deg_freedom1, deg_freedom2, cumulative) | F probability distribution | — |
| F.DIST.RT | F.DIST.RT(x, deg_freedom1, deg_freedom2) | right-tailed F probability distribution | — |
| F.INV | F.INV(probability, deg_freedom1, deg_freedom2) | inverse of the F distribution | — |
| F.INV.RT | F.INV.RT(probability, deg_freedom1, deg_freedom2) | inverse of the right-tailed F distribution | — |
| F.TEST | F.TEST(array1, array2) | F-test for equality of variances | — |
| FDIST | FDIST(x, deg_freedom1, deg_freedom2) | legacy form of F.DIST.RT | — |
| FINV | FINV(probability, deg_freedom1, deg_freedom2) | legacy form of F.INV.RT | — |
| FISHER | FISHER(x) | returns the Fisher transformation | — |
| FISHERINV | FISHERINV(y) | inverse of the Fisher transformation | — |
| FORECAST | FORECAST(x, known_ys, known_xs) | predicts a value along a linear trend | — |
| FORECAST.ETS | FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]) | forecasts a value using exponential smoothing | — |
| FORECAST.ETS.CONFINT | FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation]) | confidence interval for an ETS forecast | — |
| FORECAST.ETS.SEASONALITY | FORECAST.ETS.SEASONALITY(values, timeline, [data_completion], [aggregation]) | season length detected by ETS | — |
| FORECAST.ETS.STAT | FORECAST.ETS.STAT(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation]) | statistic from the ETS model | — |
| FORECAST.LINEAR | FORECAST.LINEAR(x, known_ys, known_xs) | predicts a value on the linear regression line | — |
| FREQUENCY | FREQUENCY(data_array, bins_array) | counts values falling into bins, spilled as a column | — |
| FTEST | FTEST(array1, array2) | legacy form of F.TEST | — |
| GAMMA.DIST | GAMMA.DIST(x, alpha, beta, cumulative) | gamma distribution | — |
| GAMMA.INV | GAMMA.INV(probability, alpha, beta) | inverse of the gamma distribution | — |
| GAMMADIST | GAMMADIST(x, alpha, beta, cumulative) | legacy form of GAMMA.DIST | — |
| GAMMAINV | GAMMAINV(probability, alpha, beta) | legacy form of GAMMA.INV | — |
| GAMMALN | GAMMALN(x) | natural logarithm of the gamma function | — |
| GAMMALN.PRECISE | GAMMALN.PRECISE(x) | natural logarithm of the gamma function | — |
| GAUSS | GAUSS(z) | probability a standard normal member falls between the mean and z | — |
| GEOMEAN | GEOMEAN(number…) | returns geometric mean of positive values | — |
| GROWTH | GROWTH(known_ys, [known_xs], [new_xs], [const]) | values along an exponential trend | — |
| HARMEAN | HARMEAN(number…) | returns the harmonic mean | — |
| HYPGEOM.DIST | HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative) | hypergeometric distribution | — |
| HYPGEOMDIST | HYPGEOMDIST(sample_s, number_sample, population_s, number_pop) | legacy form of HYPGEOM.DIST | — |
| INTERCEPT | INTERCEPT(known_ys, known_xs) | y-intercept of the linear regression line | — |
| KURT | KURT(number…) | returns the kurtosis of a data set | — |
| LARGE | LARGE(array, k) | returns the k-th largest value in a data set | 2 |
| LINEST | LINEST(known_ys, [known_xs], [const], [stats]) | parameters of the least-squares linear fit | — |
| LOGEST | LOGEST(known_ys, [known_xs], [const], [stats]) | parameters of the exponential curve fit | — |
| LOGINV | LOGINV(probability, mean, standard_dev) | legacy form of LOGNORM.INV | — |
| LOGNORM.DIST | LOGNORM.DIST(x, mean, standard_dev, cumulative) | lognormal distribution | — |
| LOGNORM.INV | LOGNORM.INV(probability, mean, standard_dev) | inverse of the lognormal distribution | — |
| LOGNORMDIST | LOGNORMDIST(x, mean, standard_dev) | legacy form of LOGNORM.DIST (cumulative) | — |
| MAXA | MAXA(value…) | returns the largest value, counting text and logicals | — |
| MAXIFS | MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2…], [criteria2…]) | returns largest value matching conditions | — |
| MEDIAN | MEDIAN(number…) | returns the median of a set of numbers | 2 |
| MINA | MINA(value…) | returns the smallest value, counting text and logicals | — |
| MINIFS | MINIFS(min_range, criteria_range1, criteria1, [criteria_range2…], [criteria2…]) | returns smallest value matching conditions | — |
| MODE | MODE(number…) | legacy form of MODE.SNGL: most frequent value in a data set | — |
| MODE.MULT | MODE.MULT(number…) | returns an array of the most frequently occurring values | — |
| MODE.SNGL | MODE.SNGL(number…) | returns the most frequently occurring value | — |
| NEGBINOM.DIST | NEGBINOM.DIST(number_f, number_s, probability_s, cumulative) | negative binomial distribution | — |
| NEGBINOMDIST | NEGBINOMDIST(number_f, number_s, probability_s) | legacy form of NEGBINOM.DIST | — |
| NORM.DIST | NORM.DIST(x, mean, standard_dev, cumulative) | normal distribution | — |
| NORM.INV | NORM.INV(probability, mean, standard_dev) | inverse of the normal distribution | — |
| NORM.S.DIST | NORM.S.DIST(z, cumulative) | standard normal distribution | — |
| NORM.S.INV | NORM.S.INV(probability) | inverse of the standard normal distribution | — |
| NORMDIST | NORMDIST(x, mean, standard_dev, cumulative) | legacy form of NORM.DIST | — |
| NORMINV | NORMINV(probability, mean, standard_dev) | legacy form of NORM.INV | — |
| NORMSDIST | NORMSDIST(z) | legacy form of NORM.S.DIST (cumulative) | — |
| NORMSINV | NORMSINV(probability) | legacy form of NORM.S.INV | — |
| PEARSON | PEARSON(array1, array2) | Pearson product moment correlation coefficient | — |
| PERCENTILE | PERCENTILE(array, k) | legacy form of PERCENTILE.INC: k-th percentile | 1 |
| PERCENTILE.EXC | PERCENTILE.EXC(array, k) | k-th percentile, exclusive | — |
| PERCENTILE.INC | PERCENTILE.INC(array, k) | returns the k-th percentile, inclusive | 1 |
| PERCENTOF | PERCENTOF(data_subset, data_all) | share of a data subset within a whole | — |
| PERCENTRANK | PERCENTRANK(array, x, [significance]) | legacy form of PERCENTRANK.INC: rank as a percentage | — |
| PERCENTRANK.EXC | PERCENTRANK.EXC(array, x, [significance]) | percentage rank, exclusive | — |
| PERCENTRANK.INC | PERCENTRANK.INC(array, x, [significance]) | percentage rank, inclusive | — |
| PERMUT | PERMUT(number, number_chosen) | returns the number of permutations of a set of objects | — |
| PERMUTATIONA | PERMUTATIONA(number, number_chosen) | returns the number of permutations with repetitions allowed | — |
| PHI | PHI(x) | density function value for the standard normal distribution | — |
| POISSON | POISSON(x, mean, cumulative) | legacy form of POISSON.DIST | — |
| POISSON.DIST | POISSON.DIST(x, mean, cumulative) | Poisson distribution | — |
| PROB | PROB(x_range, prob_range, lower_limit, [upper_limit]) | probability that values fall within limits | — |
| QUARTILE | QUARTILE(array, quart) | legacy form of QUARTILE.INC: quartile of a data set | — |
| QUARTILE.EXC | QUARTILE.EXC(array, quart) | returns a quartile, excluding the median | — |
| QUARTILE.INC | QUARTILE.INC(array, quart) | returns a quartile, including the median | — |
| RANK | RANK(number, ref, [order]) | legacy form of RANK.EQ: rank of a number in a list | 1 |
| RANK.AVG | RANK.AVG(number, ref, [order]) | rank of a number, average rank for ties | — |
| RANK.EQ | RANK.EQ(number, ref, [order]) | rank of a number, top rank for ties | — |
| RSQ | RSQ(known_ys, known_xs) | square of the Pearson correlation coefficient | — |
| SKEW | SKEW(number…) | returns the skewness of a distribution | — |
| SKEW.P | SKEW.P(number…) | skewness based on the entire population | — |
| SLOPE | SLOPE(known_ys, known_xs) | slope of the linear regression line | — |
| SMALL | SMALL(array, k) | returns the k-th smallest value in a data set | 3 |
| STANDARDIZE | STANDARDIZE(x, mean, standard_dev) | normalized value from a distribution | — |
| STDEV | STDEV(number…) | legacy form of STDEV.S: sample standard deviation | — |
| STDEV.P | STDEV.P(number…) | population standard deviation | — |
| STDEV.S | STDEV.S(number…) | sample standard deviation | — |
| STDEVA | STDEVA(value…) | sample standard deviation, counting text and logicals | — |
| STDEVP | STDEVP(number…) | legacy form of STDEV.P: population standard deviation | — |
| STDEVPA | STDEVPA(value…) | population standard deviation, counting text and logicals | — |
| STEYX | STEYX(known_ys, known_xs) | standard error of the predicted y for each x | — |
| T.DIST | T.DIST(x, deg_freedom, cumulative) | Student's t-distribution | — |
| T.DIST.2T | T.DIST.2T(x, deg_freedom) | two-tailed Student's t-distribution | — |
| T.DIST.RT | T.DIST.RT(x, deg_freedom) | right-tailed Student's t-distribution | — |
| T.INV | T.INV(probability, deg_freedom) | left-tailed inverse of the t-distribution | — |
| T.INV.2T | T.INV.2T(probability, deg_freedom) | two-tailed inverse of the t-distribution | — |
| T.TEST | T.TEST(array1, array2, tails, type) | probability from Student's t-test | — |
| TDIST | TDIST(x, deg_freedom, tails) | legacy form of T.DIST.RT / T.DIST.2T | — |
| TINV | TINV(probability, deg_freedom) | legacy form of T.INV.2T | — |
| TREND | TREND(known_ys, [known_xs], [new_xs], [const]) | values along a linear trend | — |
| TRIMMEAN | TRIMMEAN(array, percent) | mean of the interior of a data set | 1 |
| TTEST | TTEST(array1, array2, tails, type) | legacy form of T.TEST | — |
| VAR | VAR(number…) | legacy form of VAR.S: sample variance | — |
| VAR.P | VAR.P(number…) | population variance | — |
| VAR.S | VAR.S(number…) | sample variance | — |
| VARA | VARA(value…) | sample variance, counting text and logicals | — |
| VARP | VARP(number…) | legacy form of VAR.P: population variance | — |
| VARPA | VARPA(value…) | population variance, counting text and logicals | — |
| WEIBULL | WEIBULL(x, alpha, beta, cumulative) | legacy form of WEIBULL.DIST | — |
| WEIBULL.DIST | WEIBULL.DIST(x, alpha, beta, cumulative) | Weibull distribution | — |
| Z.TEST | Z.TEST(array, x, [sigma]) | one-tailed p-value of a z-test | — |
| ZTEST | ZTEST(array, x, [sigma]) | legacy form of Z.TEST | — |