Contents Introduction File menu Edit menu View menu Format menu Tools menu Statistics menu Graphs menu Tests menu Sampling menu Window menu Help menu Spreadsheet Appendices

Spreadsheet: Statistical functions

In the spreadsheet window statistical functions can be entered that perform a calculation on one or two variables or ranges of cells containing numeric values.

For all statistical functions that accept a range as argument, a list of ranges is also accepted as an argument.

E.g. SUM(A1:A3,A10,B5:B6) will calculate the sum of cells A1, A2, A3, A10, B5 and B6.

If one of the cells in the function's range does not have a numeric value, but is empty or has a string value, then this cell will not be taken into account for calculating the result of the function.

AVEDEV Average of absolute deviations

AVEDEV(range) computes the average of absolute deviations of the data in range.

AVERAGE Average

AVERAGE(range) computes the arithmetic mean of the contents of the cells in the specified range.

CHIDIST One-tailed probability from the Chi-squared distribution

CHIDIST(chisquared,df) returns the one-tailed P associated with the test statistic chisquared and df degrees of freedom.

CHIINV Returns the reverse of the Chi-squared distribution

CHIINV(P,df) returns the Chi-squared value corresponding with the one-tailed P-value P and the specified degrees of freedom df. CHIINV is the inverse of the CHIDIST function.

COEFVAR Coefficient of variation

COEFVAR(range) returns the coefficient of variation of the data in range.

COUNT Count

COUNT(range) counts the number of cells in the specified range that have a numerical contents. See also the COUNTS(range) function: this function counts the number of non-empty cells.

The COUNT function has 2 variants:

COUNTNEG(range) counts the number of negative values in range (< 0);
COUNTPOS(range) counts the number of positive numbers in range (> 0);

COUNTS Count non-empty cells

COUNTS(range) counts the number of cells in range that are non-empty, irrespective if the cell contains a formula or a numeric or text value. This distinguishes the COUNTS function from the COUNT function that only counts cells containing a numeric value, or a formula resulting in a numeric value.

FDIST One-tailed probability from the F distribution

FDIST(F,v1,v2) returns the one-tailed probability P associated with the test statistic F with v1 degrees of freedom for the numerator and v2 degrees of freedom for the denominator.

FINV Inverse of the F probability distribution

FINV(p,v1,v2) returns the inverse of the F probability distribution where p is a probability associated with the F cumulative distribution, v1 is the numerator degrees of freedom and v2 is the denominator degrees of freedom.

FINV is the inverse of the FDIST function.

GEOMEAN Geometric mean

GEOMEAN(range) returns the geometric mean of the data in range.

MAX Maximum

MAX(range) returns the maximum value of the contents of the cells in the specified range.

MIN Minimum

MIN(range) returns the minimum value of the contents of the cells in the specified range.

NORMSDIST One-tailed probability of standardized Normal distribution

NORMSDIST(z) returns the one-tailed probability associated with the standardized Normal deviate z.

NORMSINV Returns a Standardized Normal deviate

NORMSINV(P) returns the standardized Normal deviate z corresponding with the one-tailed probability P. P must be a value between 0 and 1 (0<P<1). NORMSINV is the inverse of the NORMSDIST function.

SEM Standard error of the mean

SEM(range) returns the standard error of the mean of the data in range.

STDEV Standard deviation - sample

STDEV(range) calculates the 'sample' standard deviation of the data in range (divisor n-1).

SUM Sum

SUM(range) computes the sum of the contents of the cells in the specified range. The SUM function is probably the most frequently used function in any spreadsheet model.

The SUM function has 2 variants: SUMNEG(range) and SUMPOS(range) calculating the sum of respectively the negative and positive values in range.

TDIST Two-tailed probability of the Student t distribution

TDIST(t,df) returns the two-tailed probability P associated with the test statistic t and df degrees of freedom.

TINV Returns the recerse of the Student t distribution

TINV(P,df) returns the t-value corresponding with the two-tailed P-value P and the specified degrees of freedom df. TINV is the inverse of the TDIST function.

VAR Variance - sample

VAR(range) computes the correct 'sample' variance of the data in the specified range (divisor n-1).