SpreadCE Help

Statistical functions

[ ] indicates optional parameters

AVEDEV(number1, [number2, ...])
AVERAGE(number1, [number2, ...])
AVERAGEA(value1, [value2, ...])
BETADIST(x, alpha, beta, [A], [B])
BETAINV(probability, alpha, beta, [A], [B])
BINOMDIST(number_successes, trials, probability, cumulative)
CHIDIST(x, degrees_freedom)
CHIINV(probability, degrees_freedom)
CHITEST(actual_range, expected_range)
CONFIDENCE(alpha, standard_deviation, size)
CORREL(range1, range2)
COUNT(value1, [value2, ...])
COUNTA(value1, [value2, ...])
COVAR(range1, range2)
CRITBINOM(trials, probability_s, alpha)
DEVSQ(number1, [number2, ...])
EXPONDIST(x, lambda, cumulative)
FDIST(x, degrees_freedom1, degrees_freedom2)
FINV(probability, degrees_freedom1, degrees_freedom2)
FISHER(x)
FISHERINV(y)
FORECAST(x, y_range, x_range)
FREQUENCY(data_array, bins_array)
FTEST(range1, range2)
GAMMADIST(x, alpha, beta, cumulative)
GAMMAINV(probability, alpha, beta)
GAMMALN(x)
GEOMEAN(number1, [number2, ...])
GROWTH(known_ys, [known_xs], [new_xs], [const])
HARMEAN(number1, [number2, ...])
HYPGEOMDIST(sample_s, number_sample, population_s, number_population)
INTERCEPT(y_range, x_range)
KURT(number1, [number2, ...])
LARGE(range, k)
LOGINV(probability, mean, standard_dev)
LOGNORMDIST(x, mean, standard_dev)
MAX(number1, [number2, ...])
MAXA(value1, [value2, ...])
MEDIAN(number1, [number2, ...])
MIN(number1, [number2, ...])
MINA(value1, [value2, ...])
MODE(number1, [number2, ...])
NEGBINOMDIST(number_f, number_s, probability_s)
NORMDIST(x, mean, standard_dev, cumulative)
NORMINV(probability, mean, standard_dev)
NORMSDIST(z)
NORMSINV(probability)
PEARSON(range1, range2)
PERCENTILE(range, k)
PERCENTRANK(range, x, [significance])
PERMUT(number, number_chosen)
POISSON(x, mean, cumulative)
PROB(x_range, prob_range, lower_limit, [upper_limit])
QUARTILE(range, quart)
RANK(number, range, [order])
RSQ(y_range, x_range)
SKEW(number1, [number2, ...])
SLOPE(y_range, x_range)
SMALL(range, k)
STANDARDIZE(x, mean, standard_dev)
STDEV(number1, [number2, ...])
STDEVA(value1, [value2, ...])
STDEVP(number1, [number2, ...])
STDEVPA(value1, [value2, ...])
STEYX(y_range, x_range)
TDIST(x, degrees_freedom, tails)
TINV(probability, degrees_freedom)
TREND(known_ys, [known_xs], [new_xs], [const])
TRIMMEAN(range, percent)
TTEST(range1, range2, tails, type)
VAR(number1, [number2, ...])
VARA(value1, [value2, ...])
VARP(number1, [number2, ...])
VARPA(value1, [value2, ...])
WEIBULL(x, alpha, beta, cumulative)
ZTEST(range, x, [sigma])

Statistical functions

AVEDEV(number1, [number2, ...])

Returns the average of the differences of a set of numbers from their mean.

number1, ... | The numbers of which you want the average deviation.

AVERAGE(number1, [number2, ...])

Returns the average of a set of numbers.

number1, ... | The numbers of which you want the average.

AVERAGEA(value1, [value2, ...])

Returns the average of a set of values.

value1, ... | The values of which you want the average.

BETADIST(x, alpha, beta, [A], [B])

Returns the cumulative beta distribution probability.

x | The value at which you want to evaluate the function.
alpha | The alpha value.
beta | The beta value.
A | The lower limit. If this parameter is omitted it defaults to 0.
B | The upper limit. If this parameter is omitted it defaults to 1.

BETAINV(probability, alpha, beta, [A], [B])

Returns the value associated with the specified cumulative beta distribution probability.

probability | The cumulative beta distribution probability for which you want the value.
alpha | The alpha value.
beta | The beta value.
A | The lower limit. If this parameter is omitted it defaults to 0.
B | The upper limit. If this parameter is omitted it defaults to 1.

BINOMDIST(number_successes, trials, probability, cumulative)

Returns the binomial distribution probability.

number_successes | The number of trials that are successful.
trials | The total number of trials.
probability | The probability of a single trial being successful.
cumulative | Specifies whether to return the cumulative probability or not. The possible values are:

FALSE | Return the value of the function.
TRUE | Return the integral of the function.

CHIDIST(x, degrees_freedom)

Returns the chi-squared distribution probability.

x | The value at which you want to evaluate the function.
degrees_freedom | The number of degrees of freedom.

CHIINV(probability, degrees_freedom)

Returns the value associated with the specified chi-squared distribution probability.

probability | The probability for which you want the value.
degrees_freedom | The number of degrees of freedom.

CHITEST(actual_range, expected_range)

Returns the probability result of the chi-squared test.

actual_range | An array or reference to cells containing the empirical results.
expected_range | An array or reference to cells containing the theoretical results.

CONFIDENCE(alpha, standard_deviation, size)

Returns the confidence interval for a population mean.

alpha | The significance level.
standard_deviation | The population standard deviation.
size | The sample size.

CORREL(range1, range2)

Returns the correlation coefficient of two ranges.

range1 | The first range to be compared.
range2 | The second range to be compared.

COUNT(value1, [value2, ...])

Returns the count of numbers in a list.

value1, ... | The items whose numbers are to be counted.

COUNTA(value1, [value2, ...])

Returns the count of values in a list.

value1, ... | The items whose values are to be counted.

COVAR(range1, range2)

Returns the covariance of two ranges.

range1 | The first range to be compared.
range2 | The second range to be compared.

CRITBINOM(trials, probability_s, alpha)

Returns the value at which the cumulative binomial distribution is greater than or equal to alpha.

trials | The total number of trials.
probability_s | The probability of a single trial being successful.
alpha | The value at which you want to evaluate the function.

DEVSQ(number1, [number2, ...])

Returns the sum of the squares of the differences of a set of numbers from their mean.

number1, ... | The numbers of which you want the squared deviations.

EXPONDIST(x, lambda, cumulative)

Returns the exponential distribution probability.

x | The value at which you want to evaluate the function.
lambda | The lambda value.
cumulative | Specifies whether to return the cumulative probability or not. The possible values are:

FALSE | Return the value of the function.
TRUE | Return the integral of the function.

FDIST(x, degrees_freedom1, degrees_freedom2)

Returns the F distribution probability.

x | The value at which you want to evaluate the function.
degrees_freedom1 | The degrees of freedom of the first set.
degrees_freedom2 | The degrees of freedom of the second set.

FINV(probability, degrees_freedom1, degrees_freedom2)

Returns the value associated with the specified F distribution probability.

probability | The probability for which you want the value.
degrees_freedom1 | The degrees of freedom of the first set.
degrees_freedom2 | The degrees of freedom of the second set.

FISHER(x)

Returns the Fisher transformation.

x | The value at which to evaluate the function.

FISHERINV(y)

Returns the inverse Fisher transformation.

y | The value at which to evaluate the function.

FORECAST(x, y_range, x_range)

Returns the expected value of y for a given x value for a line passing through a specified set of points.

x | The x value at which to evaluate the function.
y_range | The y values that are already known.
x_range | The x values that are already known.

FREQUENCY(data_array, bins_array)

Returns the counts of items in specified numeric categories.

data_array | An array or reference to a range of cells containing values to be counted.
bins_array | An array or reference to a range of cells containing the upper limits for each category.

FTEST(range1, range2)

Returns the probability result of the F test.

range1 | The first range to be compared.
range2 | The second range to be compared.

GAMMADIST(x, alpha, beta, cumulative)

Returns the gamma distribution probability.

x | The value at which you want to evaluate the function.
alpha | The alpha value.
beta | The beta value.
cumulative | Specifies whether to return the cumulative probability or not. The possible values are:

FALSE | Return the value of the function.
TRUE | Return the integral of the function.

GAMMAINV(probability, alpha, beta)

Returns the value associated with the specified gamma distribution probability.

probability | The probability for which you want the value.
alpha | The alpha value.
beta | The beta value.

GAMMALN(x)

Returns the natural logarithm of the gamma function evaluated at x.

x | The value at which you want to evaluate the function.

GEOMEAN(number1, [number2, ...])

Returns the geometric mean of a set of numbers.

number1, ... | The numbers of which you want the geometric mean.

GROWTH(known_ys, [known_xs], [new_xs], [const])

Returns the expected values of y for given x values for an exponential curve passing through a specified set of points.

known_ys | The y values that are already known.
known_xs | The x values that are already known. If this parameter is omitted it defaults to an array of values from 1 to the number of known ys.
new_xs | The new x values for which y values are required. If this parameter is omitted it defaults to the known xs.
const | Specifies whether the line must pass through the origin. The possible values are:

FALSE | The intercept is forced to be 1.
TRUE | The intercept is calculated normally.

| If this parameter is omitted it defaults to TRUE.

HARMEAN(number1, [number2, ...])

Returns the harmonic mean of a set of numbers.

number1, ... | The numbers of which you want the harmonic mean.

HYPGEOMDIST(sample_s, number_sample, population_s, number_population)

Returns the hypergeometric distribution probability.

sample_s | The number of sample trials that are successful.
number_sample | The total number of trials in the sample.
population_s | The number of population trials that are successful.
number_population | The total number of trials in the population.

INTERCEPT(y_range, x_range)

Returns the expected value of y when x is zero for a line passing though a specified set of points.

y_range | The y values that are already known.
x_range | The x values that are already known.

KURT(number1, [number2, ...])

Returns the kurtosis of a set of numbers.

number1, ... | The numbers of which you want the kurtosis.

LARGE(range, k)

Returns the kth largest number in a set of numbers.

range | An array or reference to cells containing numbers of which you want the kth largest.
k | The rank of the number that you want.

LOGINV(probability, mean, standard_dev)

Returns the value associated with the specified cumulative lognormal distribution probability.

probability | The probability for which you want the value.
mean | The mean of the natural logarithms of the values.
standard_dev | The standard deviation of the natural logarithms of the values.

LOGNORMDIST(x, mean, standard_dev)

Returns the cumulative lognormal distribution probability.

x | The value at which you want to evaluate the function.
mean | The mean of the natural logarithms of the values.
standard_dev | The standard deviation of the natural logarithms of the values.

MAX(number1, [number2, ...])

Returns the maximum of a set of numbers.

number1, ... | The numbers of which you want the maximum.

MAXA(value1, [value2, ...])

Returns the maximum of a set of values.

value1, ... | The values of which you want the maximum.

MEDIAN(number1, [number2, ...])

Returns the median of a set of numbers.

number1, ... | The numbers of which you want the median.

MIN(number1, [number2, ...])

Returns the minimum of a set of numbers.

number1, ... | The numbers of which you want the minimum.

MINA(value1, [value2, ...])

Returns the minimum of a set of values.

value1, ... | The values of which you want the minimum.

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

Returns the mode of a set of numbers.

number1, ... | The numbers of which you want the mode.

NEGBINOMDIST(number_f, number_s, probability_s)

Returns the negative binomial distribution probability.

number_f | The number of trials that fail.
number_s | The threshold number of trials that are successful.
probability_s | The probability of a single trial being successful.

NORMDIST(x, mean, standard_dev, cumulative)

Returns the normal distribution probability.

x | The value at which you want to evaluate the function.
mean | The mean of the values.
standard_dev | The standard deviation of the values.
cumulative | Specifies whether to return the cumulative probability or not. The possible values are:

FALSE | Return the value of the function.
TRUE | Return the integral of the function.

NORMINV(probability, mean, standard_dev)

Returns the value associated with the specified cumulative normal distribution probability.

probability | The probability for which you want the value.
mean | The mean of the values.
standard_dev | The standard deviation of the values.

NORMSDIST(z)

Returns the cumulative standard normal distribution probability.

z | The value at which you want to evaluate the function.

NORMSINV(probability)

Returns the value associated with the specified cumulative standard normal distribution probability.

probability | The probability for which you want the value.

PEARSON(range1, range2)

Returns the Pearson correlation coefficient.

range1 | The first range to be compared.
range2 | The second range to be compared.

PERCENTILE(range, k)

Returns the kth percentile of a set of values.

range | An array or reference to cells containing the values.
k | The percentile value.

PERCENTRANK(range, x, [significance])

Returns the percentile of a value in a set of values.

range | An array or reference to cells containing the values.
x | The value of which you want the percentile.
significance | The number of decimal places required in the result. If this parameter is omitted it defaults to 3.

PERMUT(number, number_chosen)

Returns the number of permutations in which a number of items can be chosen from a total number.

number | The total number of items.
number_chosen | The number of items chosen.

POISSON(x, mean, cumulative)

Returns the Poisson distribution probability.

x | The value at which you want to evaluate the function.
mean | The mean of the values.
cumulative | Specifies whether to return the cumulative probability or not. The possible values are:

FALSE | Return the value of the function.
TRUE | Return the integral of the function.

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

Returns the probability that numbers in a set are between the specified limits.

x_range | An array or reference to cells containing the numbers.
prob_range | An array or reference to cells containing the probabilities associated with each number. These values must add up to 1.
lower_limit | The lower limit of the test.
upper_limit | The upper limit of the test. If this value is omitted it defaults to the value specified for the lower limit.

QUARTILE(range, quart)

Returns the specified quartile of a set of numbers.

range | An array or reference to cells containing the numbers.
quart | Specifies which quartile to return. The possible values are:

0 | Returns the minimum value.
1 | Returns the first quartile.
2 | Returns the second quartile.
3 | Returns the third quartile.
4 | Returns the maximum value.

RANK(number, range, [order])

Returns the rank of a number in a set of numbers.

number | The number of which you want the rank.
range | An array or reference to cells containing the values.
order | Specifies whether the list is treated as being in ascending or descending order of value. The possible values are:

0 | The list is in descending order (the highest value has rank 1).
any other value | The list is in ascending order (the lowest value has rank 1).

| If this parameter is omitted it defaults to 0.

RSQ(y_range, x_range)

Returns the square of the Pearson correlation coefficient.

y_range | The first range to be compared.
x_range | The second range to be compared.

SKEW(number1, [number2, ...])

Returns the skewness of a set of numbers.

number1, ... | The numbers of which you want the skewness.

SLOPE(y_range, x_range)

Returns the slope of a line passing through a specified set of points.

y_range | The y values that are already known.
x_range | The x values that are already known.

SMALL(range, k)

Returns the kth smallest number in a set of numbers.

range | An array or reference to cells containing numbers of which you want the kth smallest.
k | The rank of the number that you want.

STANDARDIZE(x, mean, standard_dev)

Returns the standardized value of x for the specified mean and standard deviation.

x | The value that you want to standardize.
mean | The mean of the values.
standard_dev | The standard deviation of the values.

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

Returns the standard deviation (based on a population sample) of a set of numbers.

number1, ... | The numbers of which you want the standard deviation.

STDEVA(value1, [value2, ...])

Returns the standard deviation (based on a population sample) of a set of values.

value1, ... | The values of which you want the standard deviation.

STDEVP(number1, [number2, ...])

Returns the standard deviation (based on the entire population) of a set of numbers.

number1, ... | The numbers of which you want the standard deviation.

STDEVPA(value1, [value2, ...])

Returns the standard deviation (based on the entire population) of a set of values.

value1, ... | The values of which you want the standard deviation.

STEYX(y_range, x_range)

Returns the standard error of the y values of a line passing through a specified set of points.

y_range | The y values that are already known.
x_range | The x values that are already known.

TDIST(x, degrees_freedom, tails)

Returns the Student's T distribution probability.

x | The value at which you want to evaluate the function.
degrees_freedom | The degrees of freedom.
tails | Specifies the tails to include in the distribution. Should be 1 or 2.

TINV(probability, degrees_freedom)

Returns the value associated with the specified Student's T distribution probability.

probability | The probability for which you want the value.
degrees_freedom | The degrees of freedom.

TREND(known_ys, [known_xs], [new_xs], [const])

Returns the expected values of y for given x values for a line passing through a specified set of points.

known_ys | The y values that are already known.
known_xs | The x values that are already known. If this parameter is omitted it defaults to an array of values from 1 to the number of known ys.
new_xs | The new x values for which y values are required. If this parameter is omitted it defaults to the known xs.
const | Specifies whether the line must pass through the origin. The possible values are:

FALSE | The line is forced to pass through the origin.
TRUE | The line is not forced to pass through the origin.

| If this parameter is omitted it defaults to TRUE.

TRIMMEAN(range, percent)

Returns the mean of a set of numbers with the extreme values removed.

range | An array or reference to cells containing the numbers.
percent | The percentage of the numbers to exclude from the calculation.

TTEST(range1, range2, tails, type)

Returns the probability result of the Student's T test.

range1 | The first range to be compared.
range2 | The second range to be compared.
tails | Specifies the tails to include in the distribution. Should be 1 or 2.
type | Specifies which type of test is required. The possible values are:

1 | Paired.
2 | Equal variance.
3 | Unequal variance.

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

Returns the variance (based on a population sample) of a set of numbers.

number1, ... | The numbers of which you want the variance.

VARA(value1, [value2, ...])

Returns the variance (based on a population sample) of a set of values.

value1, ... | The values of which you want the variance.

VARP(number1, [number2, ...])

Returns the variance (based on the entire population) of a set of numbers.

number1, ... | The numbers of which you want the variance.

VARPA(value1, [value2, ...])

Returns the variance (based on the entire population) of a set of values.

value1, ... | The values of which you want the variance.

WEIBULL(x, alpha, beta, cumulative)

Returns the Weibull distribution probability.

x | The value at which you want to evaluate the function.
alpha | The alpha value.
beta | The beta value.
cumulative | Specifies whether to return the cumulative probability or not. The possible values are:

FALSE | Return the value of the function.
TRUE | Return the integral of the function.

ZTEST(range, x, [sigma])

Returns the probability result of the z test.

range | An array or reference to cells containing the data against which x is to be tested.
x | The value to be tested.
sigma | The population standard deviation. If this parameter is omitted it defaults to the sample standard deviation of the data.