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 probability P associated with the test statistic chisquared and df degrees of freedom.

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:

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.

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 from 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 function 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 from 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 a value t from 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 function of the TDIST function.

VAR Variance - sample

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

See also

• Spreadsheet: Introduction
• Operators
• Relative and absolute cell addresses
• Mathematical functions
• Statistical functions on variables
• String functions
• Logical functions
• Date and time functions
• Miscellaneous functions