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

### Statistical Functions

Excel's statistical functions operate on lists of data. The simplest of these functions is AVERAGE, but this group of functions also can perform such jobs as calculating deviations, distributions, correlations, slopes, and even. Table B.8 lists Excel's statistical functions.

##### Table B.8. Statistical Functions in Excel
Function SyntaxReturns
=AVEDEV(number1,number2,)Average of absolute deviations of data points from their mean
=AVERAGE(number1,number2,)Arithmetic mean of a series of numbers
=AVERAGEA(value1,value2,)Arithmetic mean of a series of numbers or text entries (text and FALSE arguments evaluate to 0; TRUE arguments evaluate to 1)
=BINOMDIST(number_s,trials,probability_s,cumulative)Individual term binomial distribution probability
=CHIDIST(x,deg_freedom)One-tailed probability of a chi-squared distribution
=CHIINV(probability,deg_freedom)Inverse of the CHIDIST function
=CHITEST(actual_range,expected_range)Test for independence for a chi-squared distribution
=CONFIDENCE(alpha,standard_dev,size)Confidence interval for population mean
=CORREL(array1,array2)Correlation coefficient for two sets of data
=COUNT(value1,value2,)Number of entries in a series that contain number values
=COUNTA(value1,value2,)Number of non-empty cells within a series
=COUNTBLANK(range)Number of empty cells in a specified range
=COUNTIF(range,criteria)Number of cells that meet the specified criteria
=COVAR(array1,array2)Covariance (average of the products of deviations) for two data sets
=CRITBINOM(trials,probability_s,alpha)Smallest value for which the cumulative binomial distribution is greater than or equal to a set criteria
=DEVSQ(number1,number2)Sum of squares of deviations of data points from their sample mean=
EXPONDIST(x,lambda,cumulative)Exponential distribution
=FDIST(x,deg_freedom1,deg_freedom2)F probability distribution (degree of diversity) for two data sets
=FINV(probability,deg_freedom1,deg_freedom2)Inverse of the FDIST function
=FISHER(x)Fisher transformation
=FISHERINV(y)Inverse Fisher transformation
=FORECAST(x,known_y's,known_x's)Predicted future value using a linear trend, based on existing values
=FREQUENCY(data_array,bins_array)Array of value frequency within an array
=FTEST(array1,array2)Result of an F-test (probability that the variances in the arrays aren't significantly different)
=GAMMALN(x)Natural logarithm of the gamma function
=GEOMEAN(number1,number2,)Geometric mean of an array or range of positive numbers
=GROWTH(known_y's,known_x's,new_x's,const)Predicted exponential growth of a series, based on existing data
=HARMEAN(number1,number2,)Harmonic mean of a series of positive numbers
=HYPGEOMDIST(sample_s,number_sample,population_s,number_population)Hypergeometric distribution
=INTERCEPT(known_y's,known_x's)Point at which a line will intersect the y-axis, using given x- and y-values
=KURT(number1,number2,)Kurtosis of a series of numbers
=LARGE(array,k)Kth largest value in an array
=LINEST(known_y's,known_x's,const,stats)Least-squares linear estimate line
=LOGEST(known_y's,known_x's,const,stats)Array of values describing an exponential curve based on regression analysis
=LOGINV(probability,mean,standard_dev)Cumulative lognormal distribution function of x
=MAX(number1,number2,)Largest value in a series
=MAXA(value1,value2,)Largest value in a series, including text and logical values
=MEDIAN(number1,number2,)Median (middle) value in a series
=MIN(number1,number2,)Smallest value in a series
=MINA(value1,value2,)Smallest value in a series, including text and logical values
=MODE(number1,number2,)Most frequently occurring value in a series
=NEGBINOMDIST(number_f,number_s,probability_s)Negative binomial distribution Poisson distribution
=NORMDIST(x,mean,standard_dev,cumulative)normal cumulative distribution
=NORMINV(probability,mean,standard_dev)Inverse of the NORMDIST function
=NORMSDIST(z)Standard normal cumilative distribution(uses a mean of zero, and a standard deviation of one)
=NORMSINV(probability)Inverse of the NORMSDIST function
=PEARSON(array1,array2)Pearson product moment correlation coefficient (r)
=PERCENTILE(array,k)Kth percentile of values in an array
=PERCENTRANK(array,x,significance)Rank of a value returned as a percentage of the set
=PERMUT(number,number_chosen)Number of permutations
=POISSON(x,mean,cumulative)Poisson distributions
=PROB(x_range,prob_range,lower_limit,upper_limit)Probability that values are within upper- and lower-limit
==QUARTILE(array,quart)Quartile of a set of data
=RANK(number,ref,order)Rank of a number in a list (relative size)
=RSQ(known_y's,known_x's)Square of the Pearson product moment correlation coefficient
=SKEW(number1,number2,)Distribution skewness
=SLOPE(known_y's,known_x's)Slope of a linear regression
=SMALL(array,k)Kth smallest value in an array
=STANDARDIZE(x,mean,standard_dev)Normalized value from a distribution
=STDEV(number1,number2,)Standard deviation based on a sample
=STDEVA(value1,value2,)Standard deviation based on a sample, including text and logical values
=STDEVP(number1,number2,)Standard deviation for entire population
=STDEVPA(value1,value2,)Standard deviation for entire population, including text and logical values
=STEYX(known_y's,known_x's)Standard error for predicted y-value for each x in a regression
=TDIST(x,degrees_freedom,tails)Student's t-distribution
=TINV(probability,degrees_freedom)Inverse of the TDIST function
=TREND(known_y's,known_x's,new_x's,const)Values from a linear trend, using the least squares method
=TRIMMEAN(array,percent)Mean of interior portion of a set
=TTEST(array1,array2,tails,type)Probability for Student's t-test
=VAR(number1,number2,)Variance of a sample
=VARA(value1,value2,)Variance of a sample, including text and logical values
=VARP(number1,number2,)Variance for an entire population
=VARPA(value1,value2,)Variance for an entire population, including text and logical values
=WEIBULL(x,alpha,beta,cumulative)Weibull distribution
=ZTEST(array,x,sigma)Two-tailed P-value for a z-test

PREVIEW

Not a subscriber?

Start A Free Trial

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