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.

xThe value at which you want to evaluate the function.
alphaThe alpha value.
betaThe beta value.
AThe lower limit. If this parameter is omitted it defaults to 0.
BThe 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.

probabilityThe cumulative beta distribution probability for which you want the value.
alphaThe alpha value.
betaThe beta value.
AThe lower limit. If this parameter is omitted it defaults to 0.
BThe upper limit. If this parameter is omitted it defaults to 1.



BINOMDIST(number_successes, trials, probability, cumulative)
Returns the binomial distribution probability.

number_successesThe number of trials that are successful.
trialsThe total number of trials.
probabilityThe probability of a single trial being successful.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:

FALSEReturn the value of the function.
TRUEReturn the integral of the function.




CHIDIST(x, degrees_freedom)
Returns the chi-squared distribution probability.

xThe value at which you want to evaluate the function.
degrees_freedomThe number of degrees of freedom.



CHIINV(probability, degrees_freedom)
Returns the value associated with the specified chi-squared distribution probability.

probabilityThe probability for which you want the value.
degrees_freedomThe number of degrees of freedom.



CHITEST(actual_range, expected_range)
Returns the probability result of the chi-squared test.

actual_rangeAn array or reference to cells containing the empirical results.
expected_rangeAn array or reference to cells containing the theoretical results.



CONFIDENCE(alpha, standard_deviation, size)
Returns the confidence interval for a population mean.

alphaThe significance level.
standard_deviationThe population standard deviation.
sizeThe sample size.



CORREL(range1, range2)
Returns the correlation coefficient of two ranges.

range1The first range to be compared.
range2The 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.

range1The first range to be compared.
range2The 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.

trialsThe total number of trials.
probability_sThe probability of a single trial being successful.
alphaThe 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.

xThe value at which you want to evaluate the function.
lambdaThe lambda value.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:

FALSEReturn the value of the function.
TRUEReturn the integral of the function.




FDIST(x, degrees_freedom1, degrees_freedom2)
Returns the F distribution probability.

xThe value at which you want to evaluate the function.
degrees_freedom1The degrees of freedom of the first set.
degrees_freedom2The degrees of freedom of the second set.



FINV(probability, degrees_freedom1, degrees_freedom2)
Returns the value associated with the specified F distribution probability.

probabilityThe probability for which you want the value.
degrees_freedom1The degrees of freedom of the first set.
degrees_freedom2The degrees of freedom of the second set.



FISHER(x)
Returns the Fisher transformation.

xThe value at which to evaluate the function.



FISHERINV(y)
Returns the inverse Fisher transformation.

yThe 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.

xThe x value at which to evaluate the function.
y_rangeThe y values that are already known.
x_rangeThe x values that are already known.



FREQUENCY(data_array, bins_array)
Returns the counts of items in specified numeric categories.

data_arrayAn array or reference to a range of cells containing values to be counted.
bins_arrayAn 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.

range1The first range to be compared.
range2The second range to be compared.



GAMMADIST(x, alpha, beta, cumulative)
Returns the gamma distribution probability.

xThe value at which you want to evaluate the function.
alphaThe alpha value.
betaThe beta value.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:

FALSEReturn the value of the function.
TRUEReturn the integral of the function.




GAMMAINV(probability, alpha, beta)
Returns the value associated with the specified gamma distribution probability.

probabilityThe probability for which you want the value.
alphaThe alpha value.
betaThe beta value.



GAMMALN(x)
Returns the natural logarithm of the gamma function evaluated at x.

xThe 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_ysThe y values that are already known.
known_xsThe 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_xsThe new x values for which y values are required. If this parameter is omitted it defaults to the known xs.
constSpecifies whether the line must pass through the origin. The possible values are:

FALSEThe intercept is forced to be 1.
TRUEThe 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_sThe number of sample trials that are successful.
number_sampleThe total number of trials in the sample.
population_sThe number of population trials that are successful.
number_populationThe 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_rangeThe y values that are already known.
x_rangeThe 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.

rangeAn array or reference to cells containing numbers of which you want the kth largest.
kThe rank of the number that you want.



LOGINV(probability, mean, standard_dev)
Returns the value associated with the specified cumulative lognormal distribution probability.

probabilityThe probability for which you want the value.
meanThe mean of the natural logarithms of the values.
standard_devThe standard deviation of the natural logarithms of the values.



LOGNORMDIST(x, mean, standard_dev)
Returns the cumulative lognormal distribution probability.

xThe value at which you want to evaluate the function.
meanThe mean of the natural logarithms of the values.
standard_devThe 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_fThe number of trials that fail.
number_sThe threshold number of trials that are successful.
probability_sThe probability of a single trial being successful.



NORMDIST(x, mean, standard_dev, cumulative)
Returns the normal distribution probability.

xThe value at which you want to evaluate the function.
meanThe mean of the values.
standard_devThe standard deviation of the values.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:

FALSEReturn the value of the function.
TRUEReturn the integral of the function.




NORMINV(probability, mean, standard_dev)
Returns the value associated with the specified cumulative normal distribution probability.

probabilityThe probability for which you want the value.
meanThe mean of the values.
standard_devThe standard deviation of the values.



NORMSDIST(z)
Returns the cumulative standard normal distribution probability.

zThe value at which you want to evaluate the function.



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

probabilityThe probability for which you want the value.



PEARSON(range1, range2)
Returns the Pearson correlation coefficient.

range1The first range to be compared.
range2The second range to be compared.



PERCENTILE(range, k)
Returns the kth percentile of a set of values.

rangeAn array or reference to cells containing the values.
kThe percentile value.



PERCENTRANK(range, x, [significance])
Returns the percentile of a value in a set of values.

rangeAn array or reference to cells containing the values.
xThe value of which you want the percentile.
significanceThe 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.

numberThe total number of items.
number_chosenThe number of items chosen.



POISSON(x, mean, cumulative)
Returns the Poisson distribution probability.

xThe value at which you want to evaluate the function.
meanThe mean of the values.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:

FALSEReturn the value of the function.
TRUEReturn 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_rangeAn array or reference to cells containing the numbers.
prob_rangeAn array or reference to cells containing the probabilities associated with each number. These values must add up to 1.
lower_limitThe lower limit of the test.
upper_limitThe 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.

rangeAn array or reference to cells containing the numbers.
quartSpecifies which quartile to return. The possible values are:

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




RANK(number, range, [order])
Returns the rank of a number in a set of numbers.

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

0The list is in descending order (the highest value has rank 1).
any other valueThe 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_rangeThe first range to be compared.
x_rangeThe 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_rangeThe y values that are already known.
x_rangeThe x values that are already known.



SMALL(range, k)
Returns the kth smallest number in a set of numbers.

rangeAn array or reference to cells containing numbers of which you want the kth smallest.
kThe 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.

xThe value that you want to standardize.
meanThe mean of the values.
standard_devThe 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_rangeThe y values that are already known.
x_rangeThe x values that are already known.



TDIST(x, degrees_freedom, tails)
Returns the Student's T distribution probability.

xThe value at which you want to evaluate the function.
degrees_freedomThe degrees of freedom.
tailsSpecifies 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.

probabilityThe probability for which you want the value.
degrees_freedomThe 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_ysThe y values that are already known.
known_xsThe 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_xsThe new x values for which y values are required. If this parameter is omitted it defaults to the known xs.
constSpecifies whether the line must pass through the origin. The possible values are:

FALSEThe line is forced to pass through the origin.
TRUEThe 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.

rangeAn array or reference to cells containing the numbers.
percentThe percentage of the numbers to exclude from the calculation.



TTEST(range1, range2, tails, type)
Returns the probability result of the Student's T test.

range1The first range to be compared.
range2The second range to be compared.
tailsSpecifies the tails to include in the distribution. Should be 1 or 2.
typeSpecifies which type of test is required. The possible values are:

1Paired.
2Equal variance.
3Unequal 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.

xThe value at which you want to evaluate the function.
alphaThe alpha value.
betaThe beta value.
cumulativeSpecifies whether to return the cumulative probability or not. The possible values are:

FALSEReturn the value of the function.
TRUEReturn the integral of the function.




ZTEST(range, x, [sigma])
Returns the probability result of the z test.

rangeAn array or reference to cells containing the data against which x is to be tested.
xThe value to be tested.
sigmaThe population standard deviation. If this parameter is omitted it defaults to the sample standard deviation of the data.