• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 16. Statistical Functions > Statistical Functions Overview

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 38 of the 81 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. Statistical Functions
FunctionSyntaxDescription
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)Returns the inverse of the umulative cbeta probability density function.
BINOMDIST=BINOMDIST(number_s,trials,probability_s,cumulative)Returns the individual term binomial distribution probability.
CHIDIST=CHIDIST(x,degrees_freedom)Returns the one-tailed probability of the chi-squared distribution.
CHIINV=CHINV(probability,degrees_freedom)Returns the 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,...)Counts the number of cells that contain numbers within the list of range arguments.
COUNTA=COUNTA(value1,value2,...)Counts the 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 criteria.
COVAR=COVAR(array1,array2)Returns covariance, the average of the products of deviations for each data-point pair.
CRITBINOM=CRITBINOM(trials,probability_s,alpha)Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.
DEVSQ=DEVSQ(number1,number2,...)Returns the sum of squares of deviations of data points from their sample mean.
EXPONDIST=EXPONDIST(x,lambda,cumulative)Returns the exponential distribution.
FDIST=FDIST(x,degrees_freedom1,degrees_freedom2)Returns the 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)Calculates or 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)Returns the inverse of the gamma cumulative distribution.
GAMMALN=GAMMALN(x)Returns the natural logarithm of the gamma function.
GEOMEAN=GEOMEAN(number1,number2,...)Returns the geometric mean of an array or range of positive data.
GROWTH=GROWTH(known_y's,known_x's,new_x s,const)Calculates predicted exponential growth by using existing data.
HARMEAN=HARMEAN(number1,number2,...)Returns the harmonic mean of a data set.
HYPGEOMDIST=HYPGEOMDIST(samples,number_sample,population_s,number_population)Returns the 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 kth largest value in a data set.
LINEST=LINEST(known_y's,known_x's,const,stats)Calculates the statisticsfor a 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)Regression analysis. Calculates an 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)Returns the 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 the cumulative 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,...)Returns the median, the number at the midpoint, of a given set of numbers.
MIN=MIN(number1,number2,...)Returns the smallest number in 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,...)Returns the most frequently occurring, or repetitive, value in an array or range of data.
NEGBINOMDIST=NEGBINOMDIST(number_f,number_s,probability_s)Returns the 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)Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
NORMSDIST=NORMSDIST(z)Returns the standard normal umulative 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 kth 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)Returns the number of permutations for a given number of objects that can be selected from a number of objects.
POISSON=POISSON(x,mean,cumulative)Returns the Poisson distribution and predicts events over time.
PROB=PROB(x_range,prob_range,lower_limit,upper_limit)Returns the 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 Pearson roduct 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)Returns the 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 kth smallest value in a data set.
STANDARDIZE=STANDARDIZE(x,mean,standard_dev STANDARD_DEV.)Returns a normalized value from a distribution characterized by MEAN and
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)Returns the standard error of the predicted y-value for each x in the regression.
TDISTTDIST(x,degrees_freedom,tails)Returns the 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)Returns values 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,...)Calculates variance 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)Returns the 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


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