[]
        
(Showing Draft Content)

Statistical Functions

Spread provides the following statistical functions, listed alphabetically.

Function

Description

AVEDEV

This function calculates the average of the absolute deviations of the specified values from their mean.

AVERAGE

This function calculates the average of the specified numeric values.

AVERAGEA

This function calculates the average of the specified values, including text or logical values as well as numeric values.

AVERAGEIF

This function calculates the average of the specified numeric values provided that they meet the specified criteria.

AVERAGEIFS

This function calculates the average of all cells that meet multiple specified criteria.

BETA.DIST

This function returns the beta distribution.

BETA.INV

This function calculates the inverse of the cumulative beta distribution function.

BINOM.DIST

This function calculates the individual term binomial distribution probability.

BINOM.DIST.RANGE

This function calculates the probability of a trial result using a binomial distribution.

BINOM.INV

This function returns the criterion binomial, the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.

CHISQ.DIST

This function returns the chi-squared distribution.

CHISQ.DIST.RT

This function calculates the one-tailed probability of the chi-squared distribution.

CHISQ.INV

This function returns the inverse of left-tailed probability of the chi-squared distribution.

CHISQ.INV.RT

This function calculates the inverse of the one-tailed probability of the chi-squared distribution.

CHISQ.TEST

This function calculates the test for independence from the chi-squared distribution.

CONFIDENCE.NORM

This function returns a confidence interval for a population mean.

CONFIDENCE.T

This function returns the confidence interval for a Student's t distribution.

CORREL

This function returns the correlation coefficient of the two sets of data.

COUNT

This function returns the number of cells that contain numbers.

COUNTA

This function returns the number of cells that contain numbers, text, or logical values.

COUNTBLANK

This function returns the number of empty (or blank) cells in a range of cells on a sheet.

COUNTIF

This function returns the number of cells that meet a certain condition.

COUNTIFS

This function returns the number of cells that meet multiple conditions.

COVARIANCE.P

This function returns the covariance, which is the average of the products of deviations for each data point pair in two sets of numbers.

COVARIANCE.S

This function returns the sample covariance, which is the average of the products of deviations for each data point pair in two sets of numbers.

DEVSQ

This function calculates the sum of the squares of deviations of data points (or of an array of data points) from their sample mean.

EXPON.DIST

This function returns the exponential distribution or the probability density.

F.DIST

This function returns the F probability distribution.

F.DIST.RT

This function calculates the F probability distribution, to see degrees of diversity between two sets of data.

F.INV

This function returns the inverse of the F probability distribution.

F.INV.RT

This function returns the inverse of the F probability distribution.

F.TEST

This function returns the result of an F-test, which returns the one-tailed probability that the variances in two arrays are not significantly different.

FINV

This function returns the inverse of the F probability distribution.

FISHER

This function returns the Fisher transformation for a specified value.

FISHERINV

This function returns the inverse of the Fisher transformation for a specified value.

FORECAST

This function calculates a future value using existing values.

FREQUENCY

This function calculates how often values occur within a range of values. This function returns a vertical array of numbers.

GAMMA

This function returns the gamma function value.

GAMMA.DIST

This function returns the gamma distribution.

GAMMA.INV

This function returns the inverse of the gamma cumulative distribution.

GAMMALN

This function returns the natural logarithm of the Gamma function, G(x).

GAMMALN.PRECISE

This function returns the natural logarithm of the gamma function.

GAUSS

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

GEOMEAN

This function returns the geometric mean of a set of positive data.

GROWTH

This function calculates predicted exponential growth. This function returns the y values for a series of new x values that are specified by using existing x and y values.

HARMEAN

This function returns the harmonic mean of a data set.

HYPGEOM.DIST

This function returns the hypergeometric distribution.

INTERCEPT

This function returns the coordinates of a point at which a line intersects the y-axis, by using existing x values and y values.

KURT

This function returns the kurtosis of a data set.

LARGE

This function returns the nth largest value in a data set, where n is specified.

LINEST

This function calculates the statistics for a line.

LOGEST

This function calculates an exponential curve that fits the data and returns an array of values that describes the curve.

LOGNORM.DIST

This function returns the log-normal distribution of x.

LOGNORM.INV

This function returns the inverse of the lognormal cumulative distribution function of x, where LN(x) is normally distributed with the specified mean and standard deviation.

MAX

This function returns the maximum value, the greatest value, of all the values in the arguments.

MAXA

This function returns the largest value in a list of arguments, including text and logical values.

MAXIFS

This function returns the maximum value among cells specified by a given set of conditions or criteria.

MEDIAN

This function returns the median, the number in the middle of the provided set of numbers; that is, half the numbers have values that are greater than the median, and half have values that are less than the median.

MIN

This function returns the minimum value, the least value, of all the values in the arguments.

MINA

This function returns the minimum value in a list of arguments, including text and logical values.

MINIFS

This function returns the minimum value among cells specified by a given set of conditions or criteria.

MODE.MULT

This function returns the most frequently occurring vertical array or the occurring value in a set of data.

MODE.SNGL

This function returns the most frequently occurring value in a set of data.

NEGBINOM.DIST

This function returns the negative binomial distribution.

NORM.DIST

This function returns the normal cumulative distribution for the specified mean and standard deviation.

NORM.S.DIST

This function returns the standard normal distribution.

NORM.S.INV

This function returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one.

NORMINV

This function returns the inverse of the normal cumulative distribution for the given mean and standard deviation.

PEARSON

This function returns the Pearson product moment correlation coefficient, a dimensionless index between -1.0 to 1.0 inclusive indicative of the linear relationship between two data sets.

PERCENTILE.EXC

This function returns the nth percentile of values in a range.

PERCENTILE.INC

This function returns the nth percentile of values in a range.

PERCENTRANK.EXC

This function returns the percentage rank(0..1, exclusive) of a value in a data set.

PERCENTRANK.INC

This function returns the rank of a value in a data set as a percentage of the data set.

PERMUT

This function returns the number of possible permutations for a specified number of items.

PERMUTATIONA

This function returns the number of permutations for a given number of objects that can be selected from the total objects.

PHI

This function returns the value of the density function for a standard normal distribution.

POISSON.DIST

This function returns the Poisson distribution.

PROB

This function returns the probability that values in a range are between two limits.

QUARTILE.EXC

This function returns which quartile (which quarter or 25 percent) of a data set a value is.

QUARTILE.INC

This function returns which quartile (which quarter or 25 percent) of a data set a value is.

RANK.AVG

This function returns the rank of a number in a set of numbers. If some values have the same rank, it will return the average rank.

RANK.EQ

This function returns the rank of a number in a set of numbers. If you were to sort the set, the rank of the number would be its position in the list.

RSQ

This function returns the square of the Pearson product moment correlation coefficient (R-squared) through data points in known y's and known x's.

SKEW

This function returns the skewness of a distribution.

SKEW.P

This function returns the skewness of a distribution base on a population: characterization of the degree of asymmetry of a distribution around its mean.

SLOPE

This function calculates the slope of linear regression.

SMALL

This function returns the nth smallest value in a data set, where n is specified.

STANDARDIZE

This function returns a normalized value from a distribution characterized by mean and standard deviation.

STDEV.P

This function returns the standard deviation for an entire specified population (of numeric values).

STDEV.S

This function returns a standard deviation estimated based on a sample.

STDEVA

This function returns the standard deviation for a set of numbers, text, or logical values.

STDEVPA

This function returns the standard deviation for an entire specified population, including text or logical values as well as numeric values.

STEYX

This function returns the standard error of the predicted y value for each x. The standard error is a measure of the amount of error in the prediction of y for a value of x.

T.DIST

This function returns the t-distribution.

T.DIST.2T

This function returns the two-tailed t-distribution.

T.DIST.RT

This function returns the right-tailed t-distribution.

T.INV

This function returns the left-tailed inverse of the t-distribution.

T.INV.2T

This function returns the t-value of the student's t-distribution as a function of the probability and the degrees of freedom.

T.TEST

This function returns the probability associated with a t-test.

TREND

This function returns values along with a linear trend. This function fits a straight line to the arrays known as x and y values. Trend returns the y values along that line for the array of specified new x values.

TRIMMEAN

This function returns the mean of a subset of data excluding the top and bottom data.

VAR.P

This function returns variance based on the entire population, which uses only numeric values.

VAR.S

This function returns the variance based on a sample of a population, which uses only numeric values.

VARA

This function returns the variance based on a sample of a population, which includes numeric, logical, or text values.

VARPA

This function returns variance based on the entire population, which includes numeric, logical, or text values.

WEIBULL.DIST

This function returns the two-parameter Weibull distribution, often used in reliability analysis.

Z.TEST

This function returns the significance value of a z-test. The z-test generates a standard score for x with respect to the set of data and returns the two-tailed probability for the normal distribution.