# Statistical Functions

Count & Frequency. Finding the Largest & Smallest Values. Permutations. Confidence Intervals. Averages. Percentiles, Quartiles & Rank. Distribution & Tests of Probability. Deviation & Variance. Trend Line Functions.
• AVEDEV

Calculates the average of the magnitudes of deviations of data from a dataset's mean.

AVEDEV(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the sample. value2 (number) Additional values or ranges to include in the sample.
• AVERAGE

The AVERAGE function returns the numerical average value in a dataset, ignoring text.

AVERAGE(1,2,3,4,5)
 value1 (number) The first value or range to consider when calculating the average value. value2 (number) Additional values or ranges to consider when calculating the average value.
• AVERAGEA

Returns the numerical average value in a dataset.

AVERAGEA(1,2,3,4,5)
 value1 (number) The first value or range to consider when calculating the average value. value2 (number) Additional values or ranges to consider when calculating the average value.
• AVERAGEIF

Returns the average of a range depending on criteria.

AVERAGEIF({var1}, "<10")
 criteria_range (range) The range to check against criterion. criterion (string) The pattern or test to apply to criteria_range.
• BETA_DIST

Returns the probability of a given value as defined by the beta distribution function.

BETA_DIST(0.65, 1.234, 7, 0.5, 3)
 value (number) The value at which to evaluate the probability function. alpha (number) The first shape parameter of the distribution.
• BETA_INV

Returns the value of the inverse beta distribution function for a given probability.

BETA_INV(0.65,1.234,7,1,3)
 probability (number) The probability at which to evaluate the function. alpha (number) The first shape parameter of the distribution.

The BETADIST function returns the probability of a given value as defined by the beta distribution function.

 value (number) The value at which to evaluate the probability function. The given value must be a number from the given lower boundary to the given upper boundary. alpha (number) The first shape parameter of the distribution. The given alpha must be a positive number.
• BETAINV

The BETAINV function returns the value of the inverse beta distribution function for a given probability.

BETAINV(0.3,5,1)
 probability (number) The probability at which to evaluate the function. Must be between 0 and 1, inclusive. alpha (number) The first shape parameter of the distribution. Must be positive.
• BINOM_DIST

Calculates the probability of drawing a certain number of successes (or a maximum number of successes) in a certain number of tries given a population of a certain size containing a certain number of successes, with replacement of draws.

BINOMDIST(4,100,0.005,FALSE)
 num_successes (number) The number of successes for which to calculate the probability in num_trials trials. num_trials (number) The number of independent trials.
• BINOMDIST

Calculates the probability of drawing a certain number of successes (or a maximum number of successes) in a certain number of tries given a population of a certain size containing a certain number of successes, with replacement of draws.

BINOMDIST(4,100,0.005,FALSE)
 num_successes (number) The number of successes for which to calculate the probability in num_trials trials. num_trials (number) The number of independent trials.
• CHIDIST

Calculates the right-tailed chi-squared distribution, often used in hypothesis testing.

CHIDIST(3.45, 2)
 x (number) The input to the chi-squared probability distribution function. The value at which to evaluate the function. Must be a positive number. degrees_freedom (number) The number of degrees of freedom of the distribution.
• CHIINV

Calculates the inverse of the right-tailed chi-squared distribution.

CHIINV(0.42, 2)
 probability (number) The probability associated with the right-tailed chi-squared distribution. Must be greater than 0 and less than 1. degrees_freedom (number) The number of degrees of freedom of the distribution.
• CONFIDENCE

Calculates the width of half of the confidence interval for a normal distribution.

CONFIDENCE(0.05, 1.6, 250)
 alpha (number) One minus the desired confidence level, e.g. 0.1 for 0.9, or 90%, confidence. standard_deviation (number) The standard deviation of the population.
• CORREL

Calculates r, the Pearson product-moment correlation coefficient of a dataset.

CORREL({var1},{var2})
 data_y (array) The range representing the array or matrix of dependent data. data_x (array) The range representing the array or matrix of independent data.
• COUNT

Returns the number of numeric values in a dataset.

COUNT(1,2,3,4,5)
 value1 (number) The first value or range to consider when counting. value2 (number) Optional: Additional values or ranges to consider when counting.
• COUNTA

Returns the number of values in a dataset.

COUNTA(1,2,3,4,5)
 value1 (number) The first value or range to consider when counting. value2 (number) Optional: Additional values or ranges to consider when counting.
• COUNTBLANK

Returns the number of empty variables in a given range.

COUNTBLANK({var1})
 value1 (array) The first value or range in which to count the number of blanks. value2 (array) Optional - Additional values or ranges in which to count the number of blanks.
• COUNTIF

Returns a conditional count across a range.

COUNTIF({var1},">20")
 range (array) The range that is tested against criterion. criterion (string) The pattern or test to apply to range.
• COVAR

Calculates the covariance of a dataset.

COVAR({var1},{var2})
 data_y (array) The range representing the array or matrix of dependent data. data_x (array) The range representing the array or matrix of independent data.
• CRITBINOM

Calculates the smallest value for which the cumulative binomial distribution is greater than or equal to a specified criteria.

CRITBINOM(100,0.005,0.8)
 num_trials (number) The number of independent trials. prob_success (number) The probability of success in any given trial.
• DEVSQ

Calculates the sum of squares of deviations based on a sample.

DEVSQ(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the sample. value2 (number) Additional values or ranges to include in the sample.
• EXPON_DIST

Returns the value of the exponential distribution function with a specified lambda at a specified value.

EXPON_DIST(4,0.5,FALSE)
 x (number) The input to the exponential distribution function. lambda (number) The lambda to specify the exponential distribution function.
• EXPONDIST

Returns the value of the exponential distribution function with a specified lambda at a specified value.

EXPONDIST(4,0.5,FALSE)
 x (number) The input to the exponential distribution function. lambda (number) The lambda to specify the exponential distribution function.
• FISHER

Returns the Fisher transformation of a specified value.

FISHER(0.962)
 value (number) The value for which to calculate the Fisher transformation.
• FISHERINV

Returns the inverse Fisher transformation of a specified value.

FISHERINV(0.962)
 value (number) The value for which to calculate the inverse Fisher transformation.
• FORECAST

Calculates the expected y-value for a specified x based on a linear regression of a dataset.

FORECAST(1,{var1},{var2})
 x (number) The value on the x-axis to forecast. data_y (array) The range representing the array or matrix of dependent data.
• GAMMA_DIST

The GAMMA_DIST function calculates the gamma distribution, a 2-parameter continuous probability distribution.

GAMMA_DIST(4.79, 1.234, 7, TRUE)
 x (number) The input to the gamma probability distribution function. The value at which to evaluate the function. alpha (number) The first parameter of the distribution.
• GAMMA_INV

The GAMMA_INV function returns the value of the inverse gamma cumulative distribution function for the specified probability, alpha, and beta parameters.

GAMMA_INV(0.65, 4, 2)
 probability (number) The input to the inverse gamma distribution function. alpha (number) The shape of the gamma distribution.

The GAMMADIST function calculates the gamma distribution, a 2-parameter continuous probability distribution.

 x (number) The input to the gamma probability distribution function. The value at which to evaluate the function. alpha (number) The first parameter of the distribution.
• GAMMAINV

The GAMMAINV function returns the value of the inverse gamma cumulative distribution function for the specified probability, alpha, and beta parameters.

GAMMAINV(0.65, 4, 2)
 probability (number) The input to the inverse gamma distribution function. Must be between 0 and 1. alpha (number) The shape of the gamma distribution. Must be positive. The sign of significance is ignored.
• GAMMALN

Returns the the logarithm of a specified Gamma function, base e (Euler's number).

GAMMALN(4)
 value (number) The input to the Gamma function. The natural logarithm of Gamma (value) will be returned. Value must be positive.
• GEOMEAN

Calculates the geometric mean of a dataset.

GEOMEAN(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the population. value2 (number) Additional values or ranges to include in the population.
• GROWTH

Given partial data about an exponential growth trend, fits an ideal exponential growth trend and/or predicts further values.

GROWTH({var1},{var2})
 known_data_y (array) The array or range containing dependent (y) values that are already known, used to curve fit an ideal exponential growth curve. known_data_x (array) Optional - {1,2,3,...} with same length as known_data_y by default - The values of the independent variable(s) corresponding with known_data_y.
• HARMEAN

Calculates the harmonic mean of a dataset.

HARMEAN(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the population. value2 (number) Additional values or ranges to include in the population.
• HYPGEOM_DIST

Calculates the probability of drawing a certain number of successes in a certain number of tries given a population of a certain size containing a certain number of successes, without replacement of draws.

HYPGEOM_DIST(4,12,20,40)
 num_successes (number) The desired number of successes. num_draws (number) The number of permitted draws.
• HYPGEOMDIST

Calculates the probability of drawing a certain number of successes in a certain number of tries given a population of a certain size containing a certain number of successes, without replacement of draws.

HYPGEOMDIST(4,12,20,40)
 num_successes (number) The desired number of successes. num_draws (number) The number of permitted draws.
• INTERCEPT

Calculates the y-value at which the line resulting from linear regression of a dataset will intersect the y-axis (x=0).

INTERCEPT({var1},{var2})
 data_y (array) The range representing the array or matrix of dependent data. data_x (array) The range representing the array or matrix of independent data.
• KURT

Calculates the kurtosis of a dataset, which describes the shape, and in particular the "peakedness" of that dataset.

KURT(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the dataset. value2 (number) Additional values or ranges to include in the dataset.
• LARGE

Returns the nth largest element from a data set, where n is user-defined.

LARGE({var1},4)
 data (array) Array or range containing the dataset to consider. n (number) The rank from largest to smallest of the element to return.
• LINEST

Given partial data about a linear trend, calculates various parameters about the ideal linear trend using the least-squares method.

LINEST({var1}, {var2})
 known_data_y (array) The array or range containing dependent (y) values that are already known, used to curve fit an ideal linear trend. known_data_x (array) Optional - {1,2,3,...} with same length as known_data_y by default - The values of the independent variable(s) corresponding with known_data_y.
• LOGEST

Given partial data about an exponential growth curve, calculates various parameters about the best fit ideal exponential growth curve.

LOGEST({var1},{var2})
 known_data_y (array) The array or range containing dependent (y) values that are already known, used to curve fit an ideal exponential growth curve. known_data_x (array) Optional - {1,2,3,...} with same length as known_data_y by default - The values of the independent variable(s) corresponding with known_data_y.

Returns the value of the inverse log-normal cumulative distribution with given mean and standard deviation at a specified value.

 x (number) The input to the inverse log-normal cumulative distribution function. mean (number) The mean (mu) of the inverse log-normal cumulative distribution function.
• LOGNORM_DIST

Returns the value of the log-normal cumulative distribution with given mean and standard deviation at a specified value.

LOGNORM_DIST(4,4,6)
 x (number) The input to the log-normal cumulative distribution function. mean (number) The mean (mu) of the log-normal cumulative distribution function.
• LOGNORMDIST

Returns the value of the log-normal cumulative distribution with given mean and standard deviation at a specified value.

LOGNORMDIST(4,4,6)
 x (number) The input to the log-normal cumulative distribution function. mean (number) The mean (mu) of the log-normal cumulative distribution function.
• MAX

Returns the maximum value in a numeric dataset.

MAX(1,2,3,4,5)
 value1 (number) The first value or range to consider when calculating the maximum value. value2 (number) Optional: Additional values or ranges to consider when calculating the maximum value.
• MAXA

Returns the maximum numeric value in a dataset.

MAXA(1,2,3,4,5)
 value1 (number) The first value or range to consider when calculating the maximum value. value2 (number) Optional: Additional values or ranges to consider when calculating the maximum value.
• MAXIFS

Returns the maximum value in a range of cells, filtered by a set of criteria.

MAXIFS({var1}, {var2}, 1, {var3}, "A")
 range (array) The range from which the maximum will be determined. criteria_range1 (array) The range over which to evaluate criterion1.
• MEDIAN

Returns the median value in a numeric dataset.

MEDIAN(1,2,3,4,5)
 value1 (number) The first value or range to consider when calculating the median value. value2 (number) Optional: Additional values or ranges to consider when calculating the median value.
• MIN

Returns the minimum value in a numeric dataset.

MIN(1,2,3,4,5)
 value1 (number) The first value or range to consider when calculating the minimum value. value2 (number) Optional: Additional values or ranges to consider when calculating the minimum value.
• MINA

Returns the minimum numeric value in a dataset.

MINA(1,2,3,4,5)
 value1 (number) The first value or range to consider when calculating the minimum value. value2 (number) Optional: Additional values or ranges to consider when calculating the minimum value.
• MINIFS

Returns the minimum value in a range of cells, filtered by a set of criteria.

MINIFS({var1}, {var2}, 1, {var3}, "A")
 range (array) The range from which the minimum will be determined. criteria_range1 (array) The range over which to evaluate criterion1.
• MODE

Returns the most commonly occurring value in a dataset.

MODE(1,2,3,4,5)
 value1 (number) The first value or range to consider when calculating mode. value2 (number) Optional: Additional values or ranges to consider when calculating mode.
• MODE_SNGL

Returns the most frequently occurring number in a numeric data set. For example, MODE_SNGL(1,2,4,4,5,5,5,6) returns 5.

MODE_SNGL(1,2,4,4,5,5,5,6)
 number1 (number) A number. number2 (number) Optional: A number
• NEGBINOM_DIST

Calculates the probability of drawing a certain number of failures before a certain number of successes given a probability of success in independent trials.

NEGBINOM_DIST(4,2,0.1)
 num_failures (number) The number of failures to model. num_successes (number) The number of successes to model.
• NEGBINOMDIST

Calculates the probability of drawing a certain number of failures before a certain number of successes given a probability of success in independent trials.

NEGBINOMDIST(4,2,0.1)
 num_failures (number) The number of failures to model. num_successes (number) The number of successes to model.
• NORM_DIST

Returns the value of the normal distribution function (or normal cumulative distribution function) for a specified value, mean, and standard deviation.

NORM_DIST(2.4,1,4,FALSE)
 x (number) The input to the normal distribution function. mean (number) The mean (mu) of the normal distribution function.
• NORM_S_DIST

Returns the value of the standard normal cumulative distribution function for a specified value.

NORM_S_DIST(2.4)
 x (number) The input to the standard normal cumulative distribution function.
• NORM_S_INV

Returns the value of the inverse standard normal distribution function for a specified value.

NORM_S_INV(0.75)
 x (number) The input to the inverse standard normal distribution function.
• NORMDIST

The NORMDIST function returns the value of the normal distribution function (or normal cumulative distribution function) for a specified value, mean, and standard deviation.

NORMDIST(2.4,1,4,FALSE)
 x (number) The input to the normal distribution function. mean (number) The mean (mu) of the normal distribution function.
• NORMINV

Returns the value of the inverse normal distribution function for a specified value, mean, and standard deviation.

NORMINV(.75,1,4)
 x (number) The input to the normal distribution function. mean (number) The mean (mu) of the normal distribution function.
• NORMSDIST

Returns the value of the standard normal cumulative distribution function for a specified value.

NORMSDIST(2.4)
 x (number) The input to the standard normal cumulative distribution function.
• NORMSINV

Returns the value of the inverse standard normal distribution function for a specified value.

NORMSINV(.75)
 x (number) The input to the inverse standard normal distribution function.
• PEARSON

Calculates r, the Pearson product-moment correlation coefficient of a dataset.

PEARSON({var1},{var2})
 data_y (array) The range representing the array or matrix of dependent data. data_x (array) The range representing the array or matrix of independent data.
• PERCENTILE

Returns the value at a given percentile of a dataset.

PERCENTILE({var1},12)
 data (array) The array or range containing the dataset to consider. percentile (number) The percentile whose value within data will be calculated and returned.
• PERCENTRANK

Returns the percentage rank (percentile) of a specified value in a dataset.

PERCENTRANK({var1},12)
 data (array) The array or range containing the dataset to consider. value (number) The value whose percentage rank will be determined.
• PERMUT

Returns the number of ways to choose some number of objects from a pool of a given size of objects, considering order.

PERMUT(4,2)
 n (number) The size of the pool of objects to choose from. k (number) The number of objects to choose.
• POISSON

Returns the value of the Poisson distribution function (or Poisson cumulative distribution function) for a specified value and mean.

POISSON(2.4,1,FALSE)
 x (number) The input to the Poisson distribution function. mean (number) The mean (mu) of the Poisson distribution function.
• QUARTILE

Returns a value nearest to a specified quartile of a dataset.

QUARTILE({var1},3)
 data (array) The array or range containing the dataset to consider. quartile_number (number) Which quartile value to return. 0 - 0% mark, 1 - 25%, 2 - 50%, 3 - 75%, 4 - 100%
• RANK

Returns the rank of a specified value in a dataset.

RANK(10,{var1})
 value (number) The value whose rank will be determined. data (array) The array or range containing the dataset to consider.
• RSQ

Calculates the square of r, the Pearson product-moment correlation coefficient of a dataset.

RSQ({var1},{var2})
 data_y (array) The range representing the array or matrix of dependent data. data_x (array) The range representing the array or matrix of independent data.
• SKEW

Calculates the skewness of a dataset, which describes the symmetry of that dataset about the mean.

SKEW(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the dataset. value2 (number) Additional values or ranges to include in the dataset.
• SLOPE

Calculates the slope of the line resulting from linear regression of a dataset.

SLOPE({var1},{var2})
 data_y (array) The range representing the array or matrix of dependent data. data_x (array) The range representing the array or matrix of independent data.
• SMALL

Returns the nth smallest element from a data set, where n is user-defined.

SMALL({var1},4)
 data (array) The array or range containing the dataset to consider. n (number) The rank from smallest to largest of the element to return.
• STANDARDIZE

Calculates the normalized equivalent of a random variable given mean and standard deviation of the distribution.

STANDARDIZE(96,80,6.7)
 value (number) The value of the random variable to normalize. mean (number) The mean of the distribution.
• STDEV

The STDEV function calculates the standard deviation based on a sample.

STDEV(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the sample. value2 (number) Optional: Additional values or ranges to include in the sample.
• STDEV_P

Calculates the standard deviation for a sample set of data. STDEV_P calculates standard deviation using the "n" method, ignoring logical values and text.

STDEV_P({var1})
 number1 (mixed) First number or reference in the sample. value1 (number) The first value or range of the population.
• STDEV_S

Calculates the standard deviation for a sample set of data. STDEV_S replaces the older STDEV function, with the same behavior.

STDEV_S({var1})
 number1 (mixed) First number or reference in the sample. number2 (mixed) Optional: Second number or reference.
• STDEVA

Calculates the standard deviation based on a sample, setting text to the value `0`.

STDEVA(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the sample. value2 (number) Additional values or ranges to include in the sample.
• STDEVP

Calculates the standard deviation based on an entire population.

STDEVP(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the population. value2 (number) Additional values or ranges to include in the population.
• STDEVPA

Calculates the standard deviation based on an entire population, setting text to the value `0`.

STDEVPA(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the population. value2 (number) Additional values or ranges to include in the population.
• STEYX

Calculates the standard error of the predicted y-value for each x in the regression of a dataset.

STEYX({var1},{var2})
 data_y (array) The range representing the array or matrix of dependent data. data_x (array) The range representing the array or matrix of independent data.
• T_DIST

Returns the Percentage Points (probability) for the Student t-distribution

T_DIST(1.96, 60, FALSE)
 x (number) The x value to evaluate the distribution at. degrees_freedom (number) The degrees of freedom.
• T_INV

Calculates the negative inverse of the one-tailed TDIST function.

T_INV(0.35, 1)
 probability (number) The probability associated with the t-distribution. degrees_freedom (number) The number of degrees of freedom.
• TDIST

Calculates the probability for Student's t-distribution with a given input (x).

TDIST(0.5, 1, 2)
 x (number) The input to the t-distribution function. degrees_freedom (number) The number of degrees of freedom.
• TINV

Calculates the inverse of the two-tailed TDIST function.

TINV(0.35, 1)
 probability (number) The probability associated with the two-tailed t-distribution. degrees_freedom (number) The number of degrees of freedom.
• TREND

Given partial data about a linear trend, fits an ideal linear trend using the least squares method and/or predicts further values.

TREND({var1},{var2})
 known_data_y (array) The array or range containing dependent (y) values that are already known, used to curve fit an ideal linear trend. known_data_x (array) Optional - {1,2,3,...} with same length as known_data_y by default - The values of the independent variable(s) corresponding with known_data_y.
• TRIMMEAN

Calculates the mean of a dataset excluding some proportion of data from the high and low ends of the dataset.

TRIMMEAN({1,1,2,3,5,8,13,21,34,55},0.05)
 data (array) Array or range containing the dataset to consider. exclude_proportion (number) The proportion of the dataset to exclude, from the extremities of the set.
• VAR

Calculates the variance based on a sample.

VAR(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the sample. value2 (number) Additional values or ranges to include in the sample.
• VAR_P

Returns the variance in an entire population. If data represents a sample of the population, use the VAR_S function.

VAR_P({var1})
 number1 (mixed) First number or reference. number2 (mixed) Optional: Second number or reference.
• VAR_S

Returns the variance of a sample. If data represents the entire population, use the VAR_P function. VAR_S ignores text values and logicals in references.

VAR_S({var1})
 number1 (mixed) First number or reference. number2 (mixed) Optional: Second number or reference.
• VARA

Calculates the variance based on a sample, setting text to the value `0`.

VARA(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the sample. value2 (number) Additional values or ranges to include in the sample.
• VARP

Calculates the variance based on an entire population.

VARP(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the population. value2 (number) Additional values or ranges to include in the population.
• VARPA

Calculates the variance based on an entire population, setting text to the value `0`.

VARPA(1,2,3,4,5,6,7,8,9,10)
 value1 (number) The first value or range of the population. value2 (number) Additional values or ranges to include in the population.
• WEIBULL

Returns the value of the Weibull distribution function (or Weibull cumulative distribution function) for a specified shape and scale.

WEIBULL(2.4, 2, 3, TRUE)
 x (number) The input to the Weibull distribution function. shape (number) The shape parameter of the Weibull distribution function.
• Z_TEST

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

Z_TEST([1,2,3,4,5,6],5.5,1.2)
 data (array) The array or range containing the dataset to consider. value (number) The test statistic to use in the Z-test.
• ZTEST

Returns the one-tailed P-value of a Z-test with standard distribution.

ZTEST([1,2,3,4,5,6],5.5,1.2)
 data (array) The array or range containing the dataset to consider. value (number) The test statistic to use in the Z-test.