Here you will find a list of functions designed specifically for statistical.

You will find the following information for each function:
1. Function name; and
2. Function definition.

NOTE: In the description you will find the function syntax and a description of each argument. Arguments that appear in bold text are required; arguments that do not appear in bold text are option. All function arguments must be entered in the order show and you must not add any spaces between or within arguments. If you need to enter spaces in a text argument or a reference, the argument must be enclosed in double quotation marks.

This list was compiled using several resources to give you the most complete information to put them to use, without over-complicating them. This is MATH, so you might want to refresh you math terminology to start.

Function Description
AVEDEV Returns the average of the absolute deviations of data points from their mean, takes the form =AVEDEV(number1,number2,…), where the numbers can be names, arrays, or references that resolve to numbers, and accepts up to 30 arguments.
AVERAGE Returns the arithmetic mean and the specified numbers and takes the form =AVERAGE(number1,number2,…), where the numbers can be names, arrays, or references that resolve to numbers.  Cells containing text, logical values, or empty cells are ignored, but cells containing a zero value are included.
AVERAGEA Same as AVERAGE, except that text and logical values are included in the calculation.
AVERAGEIF Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria and takes the form =AVERAGEIF(range,criteria,average_range).
AVERAGEIFS Returns the average (arithmetic mean) of all cells that meet multiple criteria and takes the form =AVERAGEIFS(average_range,criteria_range,criteria,…).
BETADIST Returns the beta cumulative beta probability density function and takes the form =BETADIST(x,alpha,beta,A,B), where x is the value between A and B at which to evaluate the function; alpha is a parameter to the distribution; beta is a parameter to the distribution; A is an optional lower bound to the interval of x; and B is an optional upper bound to the interval of x.
BETAINV Returns the inverse of the cumulative beta probability density function and takes the form =BETAINV(probability,alpha,beta,A,B), where probability is a probability associated with the beta distribution.
BINOMDIST Returns the individual term binomial distribution probability and takes the form =BINOMDIST(number_s,trials,probability_s,cumulative), where number_s is the number of successes in trials; trials is the number of independent trials; probability_s is the probability of success on each trial; and cumulative is a logical value that determines the form of the function.  If TRUE, it returns the probability that there are at most number_s successes; if FALSE, it returns the probability that there are number_s successes.
CHIDIST Returns the one-tailed probability of the chi-squared distribution (used to compare observed vs. expected values) and takes the form =CHIDIST(x,degrees_freedom), where x is the value at which you want to evaluate the distribution and degrees_freedom is the number of degrees of freedom.
CHIINV Returns the inverse of the CHIDIST (one-tailed probability of the chi-squared distribution) and takes the form =CHINV(probability,degrees_freedom), where probability is a probability associated with the chi-squared distribution and degrees_freedom is the number of degrees of freedom.
CHITEST Returns the test for independence and takes the form =CHITEST(actual_range,expected_range), where actual_range is the range of data that contains observations to test against expected values and expected_range is the range of data that contains the ratio of the product of row totals and column totals to the grand total.
CONFIDENCE Returns the confidence interval for a population mean and takes the form =CONFIDENCE(alpha,standard_dev,size), where alpha is the significance level used to compute the confidence level (an alpha of 0.1 indicates a 90 percent confidence level); standard_dev is the population standard deviation for the data range and is assumed to be known; and size is the sample size.
CORREL Returns the correlation coefficient array1 and array2 cell ranges and takes the form =CORREL(array1,array2), where arrays are ranges of cells containing values.
COUNT Tells you how many cells in a given range contain numbers, including dates and formulas that evaluate to numbers, takes the form =COUNT(number1,number2,…), and can accept up to 30 arguments, ignoring text, error values, and logical values.
COUNTA Same as COUNT, except that text and logical values are included in the calculation.
COUNTBLANK Counts the number of empty cells in a specified range and takes the form =COUNTBLANK(range).
COUNTIF Counts the number of cells within a range that match specified criteria and takes the form =COUNTIF(range,criteria), where range is the range you want to test and criteria is the logical test to be performed on each cell.
COUNTIFS Counts the number of cells within a range that meet multiple criteria and takes the form =COUNTIF(criteria_ange,criteria,…).
COVAR Returns covariance, the average of the products of paired deviations for each data point pair, and takes the form =COVAR(array1,array2), where arrays are cell ranges containing integers.
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 squares of deviations of data points from their sample mean, takes the form =DEVSQ(number1,number2,…), where numbers can be names, arrays, or references that resolve to numbers, and accepts up to 30 arguments.
EXPONDIST Returns the exponential distribution and takes the form =EXPONDIST(x,lambda,cumulative), where x is the value of the function; lambda is the parameter value; and cumulative is a logical value that indicates which form of the exponential function to provide (if TRUE, returns the cumulative distribution function; if FALSE, returns the probability density function).
FDIST Returns the F probability distribution and takes the form =FDIST(x,degrees_freedom1,degrees_freedom2), where x is the value at which to evaluate the function, degrees_freedom1 is the numerator degrees of freedom, and degrees_freedom2 is the denominator.
FINV Returns the inverse of the F probability distribution a takes the form =FINV(probability,degrees_freedom1,degrees_freedom2), where probability is a probability associated with the F cumulative distribution; degrees_freedom1 is the numerator degrees of freedom; and degrees_freedom2 is the denominator degrees of freedom.
FISHER Returns the Fisher transformation at x and takes the form =FISHER(x), where x is a value between -1 and 1 (not inclusive).
FISHERINV Returns the inverse of the Fisher transformation =FISHERINV(y), where y is any numeric value.
FORECAST Returns a single point along a trend line and takes the form =FORECAST(x,known_y’s,known_x’s).
FREQUENCY Returns the number of times that values occur within a population and takes the form =FREQUENCY(data_array,bins_array).
FTEST Returns the result of an F-test, the one-tailed probability that the variances in array1 and array2 are not significantly different, and takes the form =FTEST(array1,array2).
GAMMADIST Returns the gamma distribution and takes the form =GAMMADIST(x,alpha,beta,cumulative), where x is the value at which you want to evaluate the distribution; alpha is a parameter to the distribution; beta is a parameter to the distribution; and cumulative is a logic al value that determines the form of the function (if TRUE, returns the cumulative distribution function; if FALSE, returns the probability density function).
GAMMAINV Returns the inverse of the gamma cumulative distribution and takes the form =GAMMAINV(probability,alpha,beta), where probability is the probability associated with the gamma distribution; alpha is a parameter to the distribution; and beta is a parameter to the distribution.
GAMMALN Returns the natural logarithm of the gamma function and takes the form =GAMMALN(x), where x is a positive value.
GEOMEAN Returns the geometric mean of any array or range of positive data and takes the form =GEOMEAN(number1,number2,…), where numbers are 1 to 30 positive integer values.
GROWTH Returns values of points that tie along an exponential trend line and takes the form =GROWTH(known_ys,known_x’s,new_x’s,const).
HARMEAN Returns the harmonic mean of a data set and takes the form =HARMEAN(number1,number2,…), where numbers are 1 to 30 positive values.
HYPGEOMDIST Returns the hypergeometric distribution (the probability of a given number of sample successes, given the size of the sample and population, and the number of population successes) and takes the form =HYPGEOMDIST(sample_s,number_sample,population_s,number_population), where sample_s is the number of successes in the sample; number_sample is the size of the sample; population_s is the number of successes in the population; and number_population is the population size.
INTERCEPT Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values and takes the form =INTERCEPT(known_y’s,known_x’s), where known_y’s is the dependent set of observations or data and known_x’s is the independent set of observations or data.
KURT Returns the kurtosis of a data set (characterizes the relative “peaked-ness” or flatness of a distribution compared with the normal distribution), takes the form =KURT(number1,number2,…), and accepts up to 30 numeric arguments.
LARGE Returns the kth largest value in an input range and takes the form =LARGE(array,k), where k is the position from the largest value in array you want to find.
LINEST Calculates the statistics for a line using the least squares method to arrive at a slope that best describes the given data and takes the form =LINEST(known_y’s,known_x’s,const,stats).
LOGEST Returns statistics describing known data in terms of an exponential curve and takes the form =LOGEST(known_y’s,known_x’s,const,stats).
LOGINV Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard_dev, and takes the form =LOGINV(probability,mean,standard_dev), where probability is a probability associated with the lognormal distribution; mean is the mean of ln(x); and standard_dev is the standard deviation of ln(x).
LOGNORMDIST Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard_dev, and takes the form =LOGNORMDIST(x,mean,standard_dev), where x is the value at which to evaluate the function; mean is the mean of ln(x); and standard_dev is the standard deviation of ln(x).
MAX Returns the maximum largest value in a range, takes the form =MAX(number1,number2,…), and can accept up to 30 arguments, ignoring text, error values, and logical values.
MAXA Same as MAX, except that text and logical values are included in the calculation.
MEDIAN Computes the median of a set of numbers, takes the form =MEDIAN(number1,number2,…), and can accept up to 30 arguments, ignoring text, error values, and logical values.
MIN Returns the smallest value in a range, takes the form =MIN(number1,number2,…), and can accept up to 30 arguments, ignoring text, error values, and logical values.
MINA Same as MIN, except that text and logical values are included in the calculation.
MODE Determines which value occurs most frequently in a set of numbers, takes the form =MODE(number1, number2,…), and can accept up to 30 arguments, ignoring text, error values, and logical values.
NEGBINOMDIST Returns the negative binomial distribution (the probability that there will be number_f failures before the number_s-th success, when the constant probability of a success is probability_s) and takes the form =NEGBINOMDIST(number_f,number_s,probability_s), where number_f is the number of failures; number_s is the threshold number of successes; and probability_s is the probability of a success.
NORMDIST Returns the normal cumulative distribution for the specified mean and standard deviation and takes the form =NORMDIST(x,mean,standard_dev,cumulative), where x is the value for which you want the distribution; mean is the arithmetic mean of the distribution; standard_dev is the standard deviation of the distribution; and cumulative is a logical value that determines the form of the function (if TRUE, returns the cumulative distribution function; if FALSE, returns the probability mass function).
NORMINV Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation and takes the form =NORMINV(probability,mean,standard_dev), where probability is a probability corresponding to the normal distribution; mean is the arithmetic mean of the distribution; and standard_dev is the standard deviation of the distribution.
NORMSDIST Returns the standard normal cumulative distribution function and takes the form =NORMSDIST(z).
NORMSINV Returns the inverse of the standard normal cumulative distribution (with a mean of zero and a standard deviation of one) and takes the form =NORMSINV(probability), where probability is a probability corresponding to the normal distribution.
PEARSON Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1 to 1 (inclusive) and reflects the extent of a linear relationship between two data sets, and takes the form =PEARSON(array1,array2) where array1 is a set of independent values and array2 is a set of dependent values.
PERCENTILE Returns the member of an input range that is at a specified percentile ranking and takes the form =PERCENTILE(array,k), where array is the input range and k is the rank you want to find.
PERCENTRANK Returns a percential ranking for any member of a data set and takes the form =PERCENTRANK(array,x,significance), where array specifies the input range; x specifies the value whose rank you want to obtain; and the optional significance indicates the number of digits of precision you want.  If significance is omitted, results are rounded to three digits (0.xxx or xx.x%).
PERMUT Returns the number of permutations for a given number of objects that can be selected from number objects and takes the form =PERMUT(number,number_chosen), where number is an integer that describes the number of objects and number_chosen is an integer that describes the number of objects in each permutation.
POISSON Returns the Poisson distribution and takes the form =POISSON(x,mean,cumulative), where x is the number of events; mean is the expected numeric value; and cumulative is a logical value that determines the form of the probability distribution returned (if TRUE, returns the cumulative Poisson probability that the number of random events occurring will be between zero and x inclusive; if FALSE, returns the Poisson probability mass function that the number of events occurring will be exactly x).
PROB Returns the probability that values in a range are between two limits and takes the form =PROB(x_range,prob_range,lower_limit,upper_limit), where x_range of numberic values of x with which there are associated probabilities; prob_range is a set of probabilities associated with values in x_range; lower_limit is the lower bound on the value for which you want a probability; and upper_limit is the optional upper bound on the value for which you want a probability.
QUARTILE Returns the value in an input range that represents a specified quarter-percentile and takes the for =QUARTILE(array,quart).
RANK Returns the ranked position of a particular number within a set of numbers and takes the form =RANK(number,ref,order).
RSQ Returns the square of the Pearson product moment correlation coefficient through data points in the arrays known_y’s and known_x’s and takes the form =RSQ(known_y’s,known_x’s).
SKEW Returns the skewness of a distribution (the degree of asymmetry of a distribution around its mean), takes the form =SKEW(number1,number2,…), and accepts up to 30 arguments.
SLOPE Returns the slope of the linear regression line and takes the form =SLOPE(known_y’s,known_x’s).
SMALL Returns the k-th smallest value in a data set and takes the form =SMALL(array,k), where k is the position from the smallest value in array you want to find.
STANDARDIZE Returns a normalized value from a distribution characterized by mean and standard_dev and takes the form =STANDARDIZE(x,mean,standard_dev), where x is the value you want to normalize; mean is the arithmetic mean of the distribution; and standard_dev is the standard deviation of the distribution.
STDEV Estimates standard deviation, assuming that the arguments represent only a sample of the total population, and takes the form =STDEV(number1,number2,…), accepting up to 30 arguments.
STDEVA Same as STDEV, except that text and logical values are included in the calculation.
STDEVP Calculates standard deviation, assuming that the arguments represent the total population, and takes the form =STDEVP(number1,number2,…).
STDEVPA Same as STDEVP, except that text and logical values are included in the calculation.
STEYX Calculates the standard error of a regression and takes the form _STEYX(known_y’s,known_x’s).
TDIST Returns the percentage points (probability) for the the student’s t-distribution, where a numberic value (x) is calculated value of t for which the percentage points are to be computed, and takes the form =TDIST(x,degrees_freedom,tails), where x is the numeric value at which to evaluate the distribution; degrees_freedom is an integer indicating the number of degrees of freedom; and tails specifies the number of distribution tails to return (if 1, returns the one-tailed distribution; if 2, returns the two-tailed distribution).
TINV Returns the inverse of the Student’s t-distribution as a function of the probability and the degrees of freedom, and takes the form =TINV(probability,degrees_freedom), where probability is the probability associated with the two-tailed Student’s t-distribution and degrees_freedom is the number of degrees of freedom to characterize the distribution.
TREND Returns values of points that lie along a linear trend line and takes the form =TREND(known_y’s,known_x’s,new_x’s,const).
TRIMMEAN Returns the mean of the interior of a data set (the mean taken by excluding a percentage of data points from the top and bottom tails of a data set), and takes the form =TRIMMEAN(array,percent), where array is the array or range of values to trim and average and percent is the fractional number of data points to exclude from the calculation.
TTEST Returns the probability associated with a Student’s t-test and takes the form =TTEST(array1,array2,tails,type), where array1 is the first data set; array2 is the second data set; tails specifies the number of distribution tails (if 1, uses the one-tailed distribution; if 2, uses the two-tailed distribution); and type is the kind of t-test to perform (1 = paired; 2 = two-sample equal variance; 3 = two=sample unequal variance).
VAR Computes variance, assuming that the arguments represent only a sample of the total population, and takes the form =VAR(number1,number2,…), accepting up to 30 arguments.
VARA Same as VAR, except that text and logical values are included in the calculation.
VARP Computes variance, assuming that the arguments represent the total population, and takes the form =VARP(number1,number2,…).
VARPA Same as VARP, except that text and logical values are included in the calculation.
WEIBULL Returns the Weibull distribution and takes the form =WEIBULL(x,alpha,beta,cumulative), where x is the value at which to evaluate the function; alpha is a parameter to the distribution; beta is a parameter to the distribution; and cumulative determines the form of the function.
ZTEST Returns the two-tailed P-value of a z-test (generates a standard score for x with respect to the data set, array, and returns the two-tailed probability for the normal distribution), and takes the form =ZTEST(array,x,sigma), where array is the array or range of data against which to test x; x is the value to test; and sigma is the known population’s standard deviation.
Reblog this post [with Zemanta]
Tagged with:
 

3 Responses to Excel Statistical Formulas Reference

  1. Donna says:

    Layne – these are some of my FAVORITE and most useful Excel formulas that I thought I would share!
    LOWER – Converts all uppercase letters in a text string to lowercase.

    UPPER – Converts all lowercase letters in a text string to uppercase.

    PROPER – Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

    By using these functions, you can change the case of existing text in a worksheet. Consider the following example.

    http://support.microsoft.com/kb/263580

    • Layne says:

      Hi Donna,
      Thank you so much for your input. I actually did not route this information in my typical way because I just thought it was, well… too geeky. You know, reference sheets. I have a couple more categories that I will be including, so thank you for the encouragement to tap into my inner geekness.

      Also, I LOVE the suggestions that you gave. I hope this encourages many of us to try out some really cool Excel math tricks.
      Layne

  2. Donna says:

    Excel: Transpose Data
    I also found this function to be EXTREMELY helpful. I copy and pasted a signifcant amount of data from a website to create a database but I needed to get everything into specific columns – this SAVED THE DAY!!! And if anyone knows how to do this easier I would love to know!

    If you have data in columns and you wish that data were in rows (or if you have data in rows that you wish were in columns), you do not need to retype it.

    Simply select the data and hit the Copy button.
    Next, select the cell that you want to become the upper left corner of the new range of data.
    Go to the menu bar and choose Edit | Paste Special. In the Paste Special dialog box, choose Transpose.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Spam Protection by WP-SpamFree