Statistical spreadsheet functions
The statistical spreadsheet functions perform aggregation, counting, and analysis operations on a group of values expressed as a list (or lists) of one or more arguments. These arguments can be numeric values or cell values.
|
•
|
|
Descriptive — These spreadsheet functions return a value that helps you summarize and describe a group of values.
|
|
•
|
|
Inferential — These spreadsheet functions return a value (or values) that helps you draw conclusions about a group (or groups) of values.
|
Descriptive Statistical spreadsheet functions |  |
|
•
|
|
@AVG Returns the average (mean) of all numeric values in a list.
|
|
•
|
|
@COUNT Returns the number of nonblank cells in a list.
|
|
•
|
|
@COUNTIF Count Matching Cells
|
|
•
|
|
@FREQDIST Calculates a frequency distribution, displaying it as a vertical array.
|
|
•
|
|
@GEOMEAN Returns the geometric mean of all numeric values in a list.
|
|
•
|
|
@GRANDTOTAL123 Sums all cells in a designated area that contain @SUBTOTAL123 in their formulas.
|
|
•
|
|
@GROWTH Fits an exponential curve to data, then predicts further y-values on that curve for a specified array of x-values.
|
|
•
|
|
@HARMEAN Returns the harmonic mean of all numeric values in a list.
|
|
•
|
|
@KURT Returns the kurtosis (peakedness or flatness) of a data set.
|
|
•
|
|
@LARGEST Returns the k-th largest value in a data set.
|
|
•
|
|
@LINEST Uses the “least squares” method to calculate a straight line that best fits your data and returns an array to describe the line.
|
|
•
|
|
@LOGEST Calculates an exponential curve that fits your data and returns an array to describe the curve.
|
|
•
|
|
@MAX Returns the largest numeric or last date value in a list.
|
|
•
|
|
@MEDIAN Returns the median of a data set.
|
|
•
|
|
@MIN Returns the smallest numeric or earliest date value in a list.
|
|
•
|
|
@MODE Returns the most common value in a data set.
|
|
•
|
|
@PERCENTILE Returns the value from a group of values at a specified percentile.
|
|
•
|
|
@PERCENTRANK Returns the percentage rank of a value in a data set.
|
|
•
|
|
@PUREAVG Calculates the average of values in a list, ignoring blank cells and labels.
|
|
•
|
|
@PURECOUNT Returns the number of entries and cells in a list, excluding blank cells and labels.
|
|
•
|
|
@PUREMAX Returns the largest numeric value in a list, ignoring blank cells and labels.
|
|
•
|
|
@PUREMIN Retur
ns the smallest numeric value in a list, ignoring blank cells and labels. |
|
•
|
|
@PURESTD Returns the population standard deviation (square root of the population variance) of numeric values in a list, ignoring blank cells and labels.
|
|
•
|
|
@PURESTDS Returns the sample standard deviation (square root of the sample variance) of numeric values in a list, ignoring blank cells and labels.
|
|
•
|
|
@PUREVAR Calculates the population variance of numeric values in a list, ignoring blank cells and labels.
|
|
•
|
|
@PUREVARS Calculates the sample population variance of numeric values in a list, ignoring blank cells and labels.
|
|
•
|
|
@QUARTILE Returns the quartile of a data set.
|
|
•
|
|
@RANK Returns the rank of a number in a list of numbers.
|
|
•
|
|
@REGRESSION Rerforms a multiple linear regression, returning the specified statistic.
|
|
•
|
|
@SEMEAN Returns the standard error of the sample mean for values in specified cells.
|
|
•
|
|
@SKEW Returns the skewness of a distribution.
|
|
•
|
|
@SMALLEST Returns the k-th smallest value in a data set.
|
|
•
|
|
@STANDARDIZE Returns a normalized value.
|
|
•
|
|
@STD Returns the population standard deviation of all values in a list.
|
|
•
|
|
@STDS Returns the sample standard deviation of all values in a list.
|
|
•
|
|
@SUBTOTAL Returns a subtotal in a list or database.
|
|
•
|
|
@SUBTOTAL123 Adds the values in a list or cell reference.
|
|
•
|
|
@SUBTOTAL2 Returns a subtotal in a list or database and it also calculates hidden cells.
|
|
•
|
|
@SUM Returns the total of all numeric values in a list.
|
|
•
|
|
@SUMIF Adds those cells that meet specified criteria. An optional Sum Range may be specified to limit criteria consideration and sum inclusion to particular cells within the cells.
|
|
•
|
|
@SUMNEGATIVE Sums only negative values in a block or list. It ignores blank cells and labels.
|
|
•
|
|
@SUMPOSITIVE Sums only positive values in cells or list. It ignores blank cells and labels.
|
|
•
|
|
@TOTAL Returns the total of all numeric values in a list or reference, excluding any subtotals.
|
|
•
|
|
@TREND Fits a straight line to data, using the “least squares” method, then predicts further y-values on that line for a specified array of x-values.
|
|
•
|
|
@TRIMMEAN Returns the mean of all numeric values in a list with a fraction of values excluded.
|
|
•
|
|
@VAR Returns the population variance of all values in a list.
|
|
•
|
|
@VARS Returns the sample variance of all values in a list.
|
|
•
|
|
@WEIGHTAVG Returns a weighted average of the values in specified cells.
|
|
•
|
|
@XCOUNT Returns the number of non-blank cells in a list.
|
Inferential Statistical spreadsheet functions | |
|
•
|
|
@AVEDEV Performs the average of the absolute deviations of data points from their means.
|
|
•
|
|
@BETA Returns the beta function.
|
|
•
|
|
@BETADIST Returns the cumulative beta probability density function.
|
|
•
|
|
@BETAI Returns the incomplete beta function.
|
|
•
|
|
@BETAINV Returns the inverse of the cumulative beta probability density function.
|
|
•
|
|
@BINOMDIST Returns the binomial probability mass function.
|
|
•
|
|
@CHIDIST Returns the cumulative chi-square distribution.
|
|
•
|
|
@CHIINV Returns the inverse of the cumulative chi-square distribution.
|
|
•
|
|
@CHITEST Computes the probability that the actual and expected frequencies are similar by chance (chi-square test).
|
|
•
|
|
@COMB Calculates the number of unordered subgroups of specified size in a group.
|
|
•
|
|
@CONFIDENCE Returns the confidence interval for a population mean.
|
|
•
|
|
@CORREL Returns the correlation coefficient of two data sets.
|
|
•
|
|
@COVAR Returns the covariance of two data sets.
|
|
•
|
|
@CRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value.
|
|
•
|
|
@DEVSQ Returns the sum of the squares of the deviations.
|
|
•
|
|
@EXPONDIST Returns the exponential distribution.
|
|
•
|
|
@FDIST Returns the F distribution function.
|
|
•
|
|
@FINV Returns the inverse of the cumulative F distribution function.
|
|
•
|
|
@FISHER Returns the Fisher transformation.
|
|
•
|
|
@FISHERINV Returns the inverse of the Fisher transformation.
|
|
•
|
|
@FORECAST Returns a value along a linear trend.
|
|
•
|
|
@FTEST Returns the result of the F-test.
|
|
•
|
|
@GAMMADIST Returns the gamma distribution function.
|
|
•
|
|
@GAMMAINV Computes the inverse of the cumulative Gamma distribution function.
|
|
•
|
|
@GAMMALN Returns the natural logarithm of the gamma function.
|
|
•
|
|
@GAMMAP Returns the incomplete gamma function.
|
|
•
|
|
@GAMMAQ Returns the complement of the incomplete gamma function.
|
|
•
|
|
@HYPGEOMDIST Returns the hypergeometric distribution.
|
|
•
|
|
@INTERCEPT Returns the intercept of the linear regression line.
|
|
•
|
|
@LOGINV Returns the inverse of the lognormal distribution.
|
|
•
|
|
@LOGNORMDIST Returns the lognormal distribution.
|
|
•
|
|
@NEGBINOMDIST Returns the negative binomial distribution.
|
|
•
|
|
@NORMDIST Returns the normal cumulative distribution.
|
|
•
|
|
@NORMINV Computes the inverse of the cumulative normal distribution function.
|
|
•
|
|
@NORMSDIST Computes the standard normal cumulative distribution.
|
|
•
|
|
@NORMSINV Returns the inverse of the standard normal cumulative distribution.
|
|
•
|
|
@PEARSON Returns the Pearson product moment correlation coefficient.
|
|
•
|
|
@PERMUT Calculates the number of ordered subgroups of specified size in a group (permutations).
|
|
•
|
|
@POISSON Returns the Poisson probability distribution.
|
|
•
|
|
@PROB Returns the probability that values in a range are between two limits.
|
|
•
|
|
@RSQ Returns the square of the coefficient of correlation of the linear regression line through data points in known xs and known ys.
|
|
•
|
|
@SLOPE Returns the slope of the linear regression line.
|
|
•
|
|
@STEC Returns the standard error of the regression coefficient.
|
|
•
|
|
@STEYX Standard error of the predicted y-value for each x.
|
|
•
|
|
@SUMPRODUCT The dot (scalar) product of the vectors corresponding to cells.
|
|
•
|
|
@SUMSQ Returns the sum of the squares of the arguments.
|
|
•
|
|
@SUMX2MY2 Returns the sum of the differences of the squares of the corresponding values in two arrays.
|
|
•
|
|
@SUMX2PY2 Returns the sum of the sum of the squares of corresponding values in two arrays.
|
|
•
|
|
@SUMXMY2 Returns the sum of squares of differences of corresponding values in two arrays.
|
|
•
|
|
@SUMXPY2 Returns the sum of the squares of corresponding values in two arrays.
|
|
•
|
|
@SUMXY Sum of the products of the corresponding numbers in two arrays.
|
|
•
|
|
@SUMXY2 Sum of the product of values and the squares of the corresponding numbers in two arrays.
|
|
•
|
|
@TDIST Returns the Student’s t-distribution.
|
|
•
|
|
@TINV Returns the inverse of the Student’s t-distribution.
|
|
•
|
|
@TTEST Returns the probability associated with the Student’s t-test.
|
|
•
|
|
@WEIBULL Returns the Weibull distribution.
|
|
•
|
|
@ZTEST Returns the two-tailed probability value of a z-test.
|