• Create BookmarkCreate Bookmark
• Create Note or TagCreate Note or Tag
• PrintPrint

### Statistical Functions Overview

Statistical functions are among the most widely used functions in Excel. You can calculate the average of a group of numbers and determine probabilities, distributions, and trends. For example, the BETADIST function is used to measure variations in the percentage of a particular variable across samples. This chapter describes 40 of the 80 statistical functions in Excel and most are accompanied by an example to help illustrate the value of the function. The functions in bold in Table 16.1 are discussed in this chapter.

##### Table 16.1. Stastical Functions
Function Syntax Description
AVEDEV =AVEDEV(number1, number2,…) Returns the average of the absolute deviations of data points from their mean.
AVERAGE =AVERAGE(number1, number2,…) Returns the average of the arguments.
AVERAGEA =AVERAGEA(value1, value2,…) Calculates the average of the values in the list of arguments. Text and logical values are included in the calculation.
BETADIST =BETADIST(x,alpha, beta,A,B) Returns the cumulative beta probability density function.
BETAINV =BETAINV(probability, alpha,beta,A,B) Returnsthe inverse of the cumulative beta probability density function.
BINOMDIST =BINOMDIST(number_s, trials,probality_s, cumulative) Returns the individual term binomial distribution probability.
CHIDIST =CHIDIST(x, degrees_freedom) Returns the one-tailed probability of the chi-squared distribution.
CHINV =CHINV(probability, degrees_freedom) Returnsthe inverse of the one-tailed probability of the chi-squared distribution.
CHITEST =CHITEST(actual_ range, expected__range Returns the test for independence.
CONFIDENCE =CONFIDENCE(alpha, standard_dev,size) Returns the confidence interval for a population mean.
CORREL =CORREL(array1,array2) Returns the correlation coefficient of the array1 and array2 cell ranges.
COUNT =COUNT(value1, value2,…) Countsthe number of cells that contain numbers within the list of range arguments.
COUNTA =COUNTA(value1, value2,…) Countsthe number of cells that are not empty within the list of range arguments.
COUNTBLANK =COUNTBLANK(range) Counts empty cells in a specified range of cells.
COUNTIF =COUNTIF(range,criteria) Counts the number of cells within a range that meet a given criterion.
COVAR =COVAR(array1,array2) Returns covariance, the average of the products of deviations for each data-point pair.
CRITBINOM =CRITBINOM(trials, probality_s,alpha) Returns the smallest value for whichthe cumulative binomial distribution is greater than or equal to a criterion value.
DEVSQ =DEVSQ(number1, number2,…) Returnsthe sum of squares of deviations of data points from their sample mean.
EXPONDIST =EXPONDIST(x,lambda, cumulative Returnsthe exponential distribution.
FDIST =FDIST(x, degrees_freedom1, degrees_freedom2) Returnsthe F probability distribution.
FINV =FINV(probability, degrees_freedom1, degrees_freedom2) Returns the inverse of the F probability distribution.
FISHER =FISHER(x) Returns the Fisher transformation at X.
FISHERINV =FISHERINV(y) Returns the inverse of the Fisher transformation.
FORECAST =FORECAST(x,known_y's, known_x's) Calculatesor predicts a future value by using existing values. Uses linear regression.
FREQUENCY =FREQUENCY(data_array, bins_array Calculates how often values occur within a range of values, and then returns a vertical array of numbers.
FTEST =FTEST(array1,array2) Returns the result of an F-test.
GAMMADIST =GAMMADIST(x,alpha,beta, cumulative) Returns the gamma distribution.
GAMMAINV =GAMMAINV(probability, alpha,beta) Returnsthe inverse of the gamma cumulative distribution.
GAMMALN =GAMMALN(x) Returns the natural logarithm of the gamma function.
GEOMEAN =GEOMEAN(number1, number2,…) Returnsthe geometric mean of an array or range of positive data.
GROWTH =GROWTH(known_y's, known_x's,new_x's,const Calculatespredicted exponential growth by using existing data.
HARMEAN =HARMEAN(number1, number2,…) Returnsthe harmonic mean of a data set.
HYPGEOMDIST =HYPGEOMDIST(samples, number_sample population_s, number_population Returnsthe hypergeometric distribution.
INTERCEPT =INTERCEPT(known_y's, known_x's) Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values.
KURT =KURT(number1, number2,… Returns the Kurtosis of a data set.
LARGE =LARGE(array,k) Returns the k-th largest value in a data set.
LINEST =LINEST(known_y's, known_x's,const,stats) Calculates the statistics fora line by using the “least squares” method to calculate a straight line that best fits your data, and returns an array that describes the line.
LOGEST =LOGEST(known_y's, known_x's,const,stats) Calculatesan exponential curve that fits your data and returns an array of values that describes the curve in regression analysis.
LOGINV =LOGINV(probability, mean,standard_dev) Returnsthe inverse of the lognormal cumulative distribution function of X, wherein (x) is normally distributed with parameters MEAN and STANDARD_DEV.
LOGNORMDIST =LOGNORMDIST(x,mean, standard_dev) Returns thecumulative LOGNORMAL distribution of X, wherein (x) is normally distributed with parameters MEAN and STANDARD_DEV.
MAX =MAX(number1,number2,…) Returns the largest value in a set of values.
MAXA =MAXA(value1,value2,…) Returns the largest value in a list of arguments. Text and logical values are included in the calculation.
MEDIAN =MEDIAN(number1, number2,…) Returnsthe median, the number at the mid point, of a given set of numbers.
MIN =MIN(number1, number2,…) Returns the smallest numberin a set of values.
MINA =MINA(value1,value2,…) Returns the smallest value in a list of arguments. Text and logical values are included in the calculation.
MODE =MODE(number1, number2,…) Returnsthe most frequently occurring, or repetitive, value in an array or range of data.
NEGBINOMDIST =NEGBINOMDIST(number_f, number_s,probality_s) Returnsthe negative binomial distribution.
NORMDIST =NORMDIST(x,mean, standard_dev,cumulative) Returns the normal cumulative distribution for the specified mean and standard deviation.
NORMINV =NORMINV(probability, mean,standard_dev) Returnsthe inverse of the normal cumulative distribution for the specified mean and standard deviation.
NORMSDIST =NORMSDIST(z) Returns the standard normal cumulative distribution function.
NORMSINV =NORMSINV(probability) Returns the inverse of the standard normal cumulative distribution.
PEARSON =PEARSON(array1,array2) Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets.
PERCENTILE =PERCENTILE(array,k) Returns the k-th percentile of values in a range.
PERCENTRANK =PERCENTRANK(array,x, significance Returns the rank of a value in a data set as a percentage of the data set.
PERMUT =PERMUT(number, number_chosen) Returnsthe number of permutations for a given number of objects that can be selected from number objects.
POISSON =POISSON(x,mean, cumulative) Returnsthe Poisson distribution and predicts events over time.
PROB =PROB(x_range,prob_range, lower_limit,upprt_limit) Returnsthe probability that values in a range are between two specified limits.
QUARTILE =QUARTILE(array,quart) Returns the quartile of a data set.
RANK =RANK(number,ref,order) Returns the rank of a number in a list of numbers.
RSQ =RSQ(known_y's, known_x's) Returns the square of the Pearsonproduct moment correlation coefficient through data points in known_y's and known_x's.
SKEW =SKEW(number1, number2,…) Returns the degree of asymmetry of a distribution around its mean.
SLOPE =SLOPE(known_y's, known_x's) Returnsthe slope or rate of change of the regression line through data points in known_y's and known_x's.
SMALL =SMALL(array,k) Returns the k-th smallest value in a data set.
STANDARDIZE =STANDARDIZE(x,mean, standard_dev) Returnsa normalized value from a distribution characterized by MEAN and STANDARD_DEV.
STDEV =STDEV(number1, number2,…) Estimates standard deviation based on a sample. Nonnumeric values are ignored.
STDEVA =STDEVA(value1, value2,…) Estimates standard deviation based on a sample. STDEVA acts upon values. Text and logical, values are included in the calculation.
STDEVP =STDEVP(number1, number2,…) Calculates standard deviation based on the entire population given as arguments.
STDEVPA =STDEVPA(value1, value2,…) Calculates standard deviation based on the entire population given as arguments. Text and logical values are included in the calculation.
STEYX =STEYX(known_y's, known_x's) Returnsthe standard error of the predicted y-value for each x in the regression.
TDIST TDIST(x,degrees_freedom, tails) Returnsthe probability for a student's t-distribution.
TINV =TINV(probability, degrees_freedom) Returns the inverse of the student's t-distribution for the specified degrees of freedom.
TREND =TREND(known_y's, known_x's,new_x's,const) Returnsvalues along a linear trend using the method of least squares.
TRIMMEAN =TRIMMEAN(array,percent) Returns the mean of the interior of a data set.
TTEST =TTEST(array1,array2, tails,type) Returns the probability associated with the student's t-test.
VAR =VAR(number1, number2,…) Estimates the variance of a sample population.
VARA =VARA(value1,value2,…) Estimates the variance of a sample population. Text and logical values are included in the calculation.
VARP =VARP(number1, number2,…) Calculatesvariance based on the entire population.
VARPA =VARPA(value1, value2,…) Calculates variance based on the entire population. In addition to numbers, text and logical values, such as TRUE and FALSE, are included in the calculation.
WEIBULL =WEIBULL(x,alpha,beta, cumulative) Returnsthe Weibull distribution.
ZTEST =ZTEST(array,x,sigma) Returns the two-tailed P-value of a z-test.

PREVIEW

Not a subscriber?

Start A Free Trial

• Create BookmarkCreate Bookmark
• Create Note or TagCreate Note or Tag
• PrintPrint