Statistical Functions

  • 15 minutes to read

This document describes statistical functions supported by the non-visual Spreadsheet component.

Name

Description

Syntax

AVEDEV

Returns the average of the absolute deviations of data points from their mean.

AVEDEV(number1, [number2], …)

AVERAGE

Returns the average (arithmetic mean) of numbers, names, or cell ranges that contain numbers.

AVERAGE(number1, [number2], …)

AVERAGEA

Returns the average (arithmetic mean) of its arguments, including numbers, text, and logical values.

AVERAGEA(value1, [value2], …)

AVERAGEIF

Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.

AVERAGEIF(range, criteria, [average_range])

The range is an array or cell range of values that is tested against the given criteria. If average_range is omitted, values in the initial range are used to calculate the average. Otherwise, cells in the average_range array are used for calculation.

AVERAGEIFS

Returns the average (arithmetic mean) of all cells that meet multiple criteria.

AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], … )

Average_range is the range for calculation. Criteria_range1, [criteria_range2], … - are the arrays to be tested against the supplied criteria. Criteria1, [criteria2], … - are the respective conditions to be tested.

BETA.INV

Returns the inverse of the beta cumulative probability density function (BETA.DIST).

BETA.INV(probability,alpha,beta,[A],[B])

BETA.DIST

Returns the beta distribution.

BETA.DIST(x,alpha,beta,cumulative,[A],[B])

BINOM.DIST

Returns the individual term binomial distribution probability.

BINOM.DIST(number_s,trials,probability_s,cumulative)

BINOM.INV

Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.

BINOM.INV(trials,probability_s,alpha)

BINOM.DIST.RANGE

Returns the probability of a trial result using a binomial distribution.

BINOM.DIST.RANGE(trials,probability_s,number_s,[number_s2])

CHISQ.DIST

Returns the chi-squared distribution.

CHISQ.DIST(x,deg_freedom,cumulative)

X - Required. The value at which you want to evaluate the distribution. Deg_freedom - Required. The number of degrees of freedom. Cumulative - Required. A logical value that determines the form of the function.

CHISQ.DIST.RT

Returns the right-tailed probability of the chi-squared distribution.

CHISQ.DIST.RT(x,deg_freedom)

CHISQ.INV

Returns the inverse of the left-tailed probability of the chi-squared distribution.

CHISQ.INV(probability,deg_freedom)

CHISQ.INV.RT

Returns the inverse of the right-tailed probability of the chi-squared distribution.

CHISQ.INV.RT(probability,deg_freedom)

CHISQ.TEST

Returns the test for independence as the value from the chi-squared distribution for the statistic and the appropriate degrees of freedom.

CHISQ.TEST(actual_range,expected_range)

CONFIDENCE.NORM

Returns the confidence interval for a population mean, using a normal distribution.

CONFIDENCE.NORM(alpha,standard_dev,size)

Alpha is the significance level used to compute the confidence level. The confidence level equals 100*(1 - alpha)%, or in other words, an alpha of 0.05 indicates a 95 percent confidence level. Standard_dev is the population standard deviation for the data range and is assumed to be known. Size is the sample size.

CONFIDENCE.T

Returns the confidence interval for a population mean, using a Student’s t distribution.

CONFIDENCE.T (alpha,standard_dev,size)

CORREL

Returns the correlation coefficient between two data sets.

CORREL(array1, array2)

The arrays should be of equal length.

COUNT

Returns the number of numeric values in a set of cells or values.

COUNT(value1, [value2], …)

Arguments that are numbers, dates, or a text representation of numbers are counted. Logical values and text representations of numbers that you type directly into the list of arguments are also counted.

COUNTA

Returns the number of non-blank cells within a specified range.

COUNTA(value1, [value2], …)

COUNTBLANK

Returns the number of empty cells in a range of cells.

COUNTBLANK(range)

COUNTIF

Returns the number of cells within a range that meet a criterion.

COUNTIF(range, criteria)

COUNTIFS

Counts the number of cells within a range that meet multiple criteria.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other.

COVARIANCE.P

Returns population covariance, the average of the products of deviations for each data point pair in two data sets.

COVARIANCE.P(array1,array2)

COVARIANCE.S

Returns the sample covariance, the average of the products of deviations for each data point pair in two data sets.

COVARIANCE.S(array1,array2)

DEVSQ

Returns the sum of squares of deviations of data points from their sample mean.

DEVSQ(number1, [number2], …)

EXPON.DIST

Returns the exponential distribution.

EXPON.DIST(x,lambda,cumulative)

F.DIST

Returns the F probability distribution.

F.DIST(x,deg_freedom1,deg_freedom2,cumulative)

F.DIST.RT

Returns the (right-tailed) F probability distribution (degree of diversity) for two data sets.

F.DIST.RT(x,deg_freedom1,deg_freedom2)

F.INV

Returns the inverse of the F probability distribution.

F.INV(probability,deg_freedom1,deg_freedom2)

F.INV.RT

Returns the inverse of the (right-tailed) F probability distribution.

F.INV.RT(probability,deg_freedom1,deg_freedom2)

F.TEST

Returns the result of an F-test, the two-tailed probability that the variances in array1 and array2 are not significantly different.

F.TEST(array1,array2)

FISHER

Returns the Fisher transformation at x.

FISHER(x)

FISHERINV

Returns the inverse of the Fisher transformation.

FISHERINV(y)

FORECAST

Calculates, or predicts, a future value along a linear trend by using existing values.

FORECAST(x,known_y’s,known_x’s)

X is the data point for which you want to predict a value. Known_y’s is the dependent array or range of data. Known_x’s is the independent array or range of data.

FREQUENCY

Calculates how often values occur within a range of values, and then returns a vertical array of numbers.

FREQUENCY(data_array, bins_array)

Data_array is an array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros. Bins_array is an array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.

GAMMA

Return the gamma function value.

GAMMA(number)

GAMMA.DIST

Returns the gamma distribution.

GAMMA.DIST(x,alpha,beta,cumulative)

GAMMA.INV

Returns the inverse of the gamma cumulative distribution.

GAMMA.INV(probability,alpha,beta)

GAMMALN

Returns the natural logarithm of the gamma function.

GAMMALN(x)

GAMMALN.PRECISE

Returns the natural logarithm of the gamma function (new version).

GAMMALN.PRECISE(x)

GAUSS

Calculates the probability that a member of a standard normal population will fall between the mean and z standard deviations from the mean.

GAUSS(z)

GEOMEAN

Returns the geometric mean of an array or range of positive data.

GEOMEAN(number1, [number2], …)

GROWTH

Calculates an exponential curve that best fits your data based on a number of known X and Y values. Returns the y-values for a series of new x-values.

GROWTH(known_y’s, [known_x’s], [new_x’s], [const])

Known_y’s is the set of y-values for the relationship y = b*m^x. Known_x’s is an optional set of x-values for the relationship; if omitted, it is assumed to be the array {1,2,3,…} that is the same size as known_y’s. New_x’s is an optional set of new x-values for which you want GROWTH to return corresponding y-values. If omitted, it is assumed to be the same as known_x’s. Const is an optional logical value. It is TRUE or omitted, to calculate the b constant; otherwise b is set to 1. GROWTH is the exponential counterpart to the linear regression function TREND.

HARMEAN

Returns the harmonic mean of a data set.

HARMEAN(number1,number2,…)

HYPGEOM.DIST

Returns the hypergeometric distribution.

HYPGEOM.DIST(sample_s,number_sample,population_s,number_pop,cumulative)

INTERCEPT

Calculates the point at which a line will intersect the y-axis by using a best-fit regression line plotted through the known x-values and y-values.

INTERCEPT(known_y’s, known_x’s)

Known_y’s is the dependent set of observations or data. Known_x’s is the independent set of observations or data.

KURT

Returns the kurtosis of a data set.

KURT(number1,number2,…)

LARGE

Returns the k-th largest value in a data set.

LARGE(array, k)

The array argument is an array or range of data for which the k-th largest value is determined. The k argument is the position (from the largest) in the array or cell range of data to return.

LINEST

Uses the least squares method to calculate a straight line that best fits your data and returns an array that describes this line.

LINEST(known_y’s,known_x’s,const,stats)

Known_y’s is the set of y-values you already know in the relationship y = mx + b. Known_x’s is an optional set of x-values that you may already know in the relationship y = mx + b. Const is a logical value specifying whether to force the constant b to equal 0. Stats is a logical value that specifies whether to return additional regression statistics.

LOGEST

Calculates an exponential curve that fits your data and returns an array of values that describes this curve.

LOGEST(known_y’s, [known_x’s], [const], [stats])

Known_y’s is the set of dependent values. Known_x’s is an optional set of independent values. The argument “const” is a logical value that specifies whether to force the constant b to equal 1. The argument “stats” is a logical value that specifies whether to return additional regression statistics.

LOGNORM.DIST

Returns the lognormal distribution of x, where ln(x) is normally distributed with parameters Mean and Standard_dev.

LOGNORM.DIST(x,mean,standard_dev,cumulative)

X is the value at which to evaluate the function. Mean is the mean of ln(x). Standard_dev is the standard deviation of ln(x). Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, LOGNORM.DIST returns the cumulative distribution function; otherwise, it returns the probability density function.

LOGNORM.INV

Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters Mean and Standard_dev.

LOGNORM.INV(probability,mean,standard_dev)

The probability argument specifies a probability associated with the lognormal distribution. Mean is the mean of ln(x). Standard_dev is the standard deviation of ln(x).

MAX

Returns the largest value in a set of values.

MAX(number1, [number2], …)

MAXA

Returns the maximum value in a list of arguments, including numbers, text, and logical values.

MAXA(value1,[value2],…)

MAXIFS

Returns the maximum value among cells specified by a given set of conditions or criteria.

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

MEDIAN

Returns the statistical median (the middle value) of the given numbers.

MEDIAN(number1, [number2], …)

MIN

Returns the smallest number in a set of values.

MIN(number1, [number2], …)

MINA

Returns the smallest value in a list of arguments, including numbers, text, and logical values.

MINA(value1, [value2], …)

MINIFS

Returns the minimum value among cells specified by a given set of conditions or criteria.

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

MODE.MULT

Returns a vertical array of the most frequently occurring, or repetitive, values in an array or range of data.

MODE.MULT((number1,[number2],…])

The arguments number1, [number2], … are numerical values, arrays, or references to cells that contain numbers. You can enter up to 255 number arguments. If an array or reference argument contains text, logical values, or empty cells, those values are ignored. If the data set contains no duplicate data points, the #N/A error value is returned.

MODE.SNGL

Returns the most frequently occurring, or repetitive, value in an array or range of data.

MODE.SNGL(number1,[number2],…])

The arguments number1, [number2], … are numerical values, arrays, or references to cells that contain numbers. You can enter up to 255 number arguments. If an array or reference argument contains text, logical values, or empty cells, those values are ignored. If the data set contains no duplicate data points, the #N/A error value is returned.

NEGBINOM.DIST

Returns the negative binomial distribution.

NEGBINOM.DIST(number_f,number_s,probability_s,cumulative)

NORM.DIST

Returns the normal distribution for the specified mean and standard deviation.

NORM.DIST(x,mean,standard_dev,cumulative)

X is the value for which you want the distribution. Mean is the arithmetic mean of the distribution. Standard_dev is the standard deviation of the distribution. Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, NORM.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.

NORM.INV

Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.

NORM.INV(probability,mean,standard_dev)

Probability is a probability corresponding to the normal distribution. Mean is the arithmetic mean of the distribution. Standard_dev is the standard deviation of the distribution.

NORM.S.DIST

Returns the standard normal distribution (has a mean of zero and a standard deviation of one).

NORM.S.DIST(z,cumulative)

Z is the value for which you want the distribution. Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, NORMS.DIST returns the cumulative distribution function; otherwise it returns the probability mass function.

NORM.S.INV

Returns the inverse of the standard normal cumulative distribution.

NORM.S.INV(probability)

Probability is a probability that corresponds to the normal distribution.

PEARSON

Returns the Pearson product moment correlation coefficient, a statistical measurement of the correlation between two sets of values.

PEARSON(array1,array2)

Array1 is a set of independent values, and array2 is a set of dependent values.

PERCENTILE.EXC

Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive.

PERCENTILE.EXC(array, k)

PERCENTILE.INC

Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive.

PERCENTILE.INC(array, k)

PERCENTRANK.EXC

Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set.

PERCENTRANK.EXC(array, x, [significance])

PERCENTRANK.INC

Returns the rank of a value in a data set as a percentage (0..1, inclusive) of the data set.

PERCENTRANK.INC(array, x, [significance])

PERMUT

Returns the number of permutations for a given number of objects that can be selected from the total objects.

PERMUT(number, number_chosen)

PERMUTATIONA

Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects.

PERMUTATIONA(number, number-chosen)

PHI

Returns the value of the density function for a standard normal distribution.

PHI(x)

POISSON.DIST

Returns the Poisson distribution.

POISSON.DIST(x,mean,cumulative)

PROB

Returns the probability that values in a range are between two limits.

PROB(x_range, prob_range, [lower_limit], [upper_limit])

QUARTILE.EXC

Returns the quartile of a data set, based on percentile values from 0..1, exclusive.

QUARTILE.EXC(array, quart)

QUARTILE.INC

Returns the quartile of a data set, based on percentile values from 0..1, inclusive.

QUARTILE.INC(array,quart)

RANK.AVG

Returns the rank of a number in a list of numbers: its size relative to other values in the list. If more than one value has the same rank, the average rank is returned.

RANK.AVG(number,ref,[order])

RANK.EQ

Returns the rank of a number in a list of numbers. Its size is relative to other values in the list. If more than one value has the same rank, the top rank of that set of values is returned.

RANK.EQ(number,ref,[order])

RSQ

Returns the square of the Pearson product moment correlation coefficient through data points in known_y’s and known_x’s.

RSQ(known_y’s,known_x’s)

SKEW

Returns the skewness (the degree of asymmetry) of a distribution.

SKEW(number1, [number2], …)

Number1 is required, subsequent numbers are optional. You can supply up to 255 arguments for which you want to calculate skewness or use a single array or a reference to an array instead of arguments separated by commas.

SKEW.P

Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean.

SKEW.P(number 1, [number 2],…)

SLOPE

Returns the slope of the linear regression line through data points in known_y’s and known_x’s.

SLOPE(known_y’s, known_x’s)

Known_y’s is an array or cell range of numeric dependent data points. Known_x’s is the set of independent data points.

SMALL

Returns the k-th smallest value in a data set.

SMALL array, k )

The array argument is an array or range of data for which the k-th smallest value is determined. The k argument is the position (from the smallest) in the array or range of data to return.

STANDARDIZE

Returns a normalized value from a distribution characterized by mean and standard_dev.

STANDARDIZE(x,mean,standard_dev)

STEYX

Returns the standard error of the predicted y-value for each x in the regression.

STEYX(known_y’s, known_x’s)

STDEVA

Calculates standard deviation based on a sample.

STDEVA(value1, [value2], …)

The arguments value1, [value2], … are numerical values or references to cells. You can enter up to 255 arguments. Arguments can be the following: numbers, names, arrays, references that contain numbers, text representations of numbers, or logical values.

STDEVPA

Calculates standard deviation based on the entire population, including numbers, text, and logical values.

STDEVPA(value1,value2,…)

STDEV.P

Calculates the standard deviation based on the entire population (ignores logical values and text).

STDEV.P(number1,[number2],…])

STDEV.S

Calculates the standard deviation based on a sample (ignores logical values and text in the sample).

STDEV.S(number1,[number2],…)

T.DIST

Returns the Student’s left-tailed t-distribution.

T.DIST(x,deg_freedom, cumulative)

T.DIST.2T

Returns the two-tailed Student’s t-distribution.

T.DIST.2T(x,deg_freedom)

T.DIST.RT

Returns the right-tailed Student’s t-distribution.

T.DIST.RT(x,deg_freedom)

T.INV

Returns the left-tailed inverse of the Student’s t-distribution.

T.INV(probability,deg_freedom)

T.INV.2T

Returns the two-tailed inverse of the Student’s t-distribution.

T.INV.2T(probability,deg_freedom)

T.TEST

Returns the probability associated with a Student’s t-Test.

T.TEST(array1,array2,tails,type)

TREND

Returns values along a linear trend. It fits a straight line (using the method of least squares) to the arrays known_y’s and known_x’s. TREND returns the y-values along that line for the specified array of new_x’s.

TREND(known_y’s, [known_x’s], [new_x’s], [const])

Known_y’s is the set of y-values for the relationship y = mx + b. Known_x’s is an optional set of x-values for the relationship. If omitted, it is assumed to be the array {1,2,3,…} that is the same size as known_y’s.

New_x’s is an optional set of new x-values for which you want TREND to return corresponding y-values. If omitted, it is assumed to be the same as known_x’s.

Const is an optional logical value that specifies whether to force the constant b to equal 0.

TRIMMEAN

Returns the mean of the interior of a data set.

TRIMMEAN(array, percent)

VARA

Estimates variance based on a sample, including numbers, text, and logical values.

VARA(value1, [value2], …)

VARPA

Calculates variance based on the entire population, including numbers, text, and logical values.

VARPA(value1, [value2], …)

VAR.P

Calculates variance based on the entire population (ignores logical values and text in the population).

VAR.P(number1,[number2],…])

VAR.S

Estimates variance based on a sample (ignores logical values and text in the sample).

VAR.S(number1,[number2],…])

WEIBULL.DIST

Returns the Weibull distribution.

WEIBULL.DIST(x,alpha,beta,cumulative)

Z.TEST

Returns the one-tailed probability-value of a z-test.

Z.TEST(array,x,[sigma])

See Also