# Excel Statistical Formulas Reference

*By Layne On March 29, 2010 · 3 Comments · In Excel, Software*

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. |

### 3 Responses to *Excel Statistical Formulas Reference*

### Leave a Reply Cancel reply

### Sparkle e-Mail Subscripttion

### Quote of the Day

### Categories

### Sparkle Favorites

- 2013 is the Year of Intention
- Are You Enjoying The Moment?
- Office Chic: Hosiery
- Word: Create an Electronic Signature
- Hot-Key Tip of the Week: Ctrl-S, Ctrl-P, & Alt-F4
- Managing Your Office Supplies
- Meeting Etiquette: 25 Tips
- Writing for Clarity and Readability
- Excel Statistical Formulas Reference
- Assistant Profile – Pepper Potts

### Recent Sparkle Articles

- Nine Tips for Saving Money in Your Office
- Assistant Profile – Donna Paulsen
- Office Etiquette – Breaking In
- Book Review – Sitting on a File Cabinet, Naked with a Gun
- Office Chic – This Should be Called “Man Chic”
- Wrap-Up: Week of June 17, 2013
- Happy Father’s Day to the Greatest Dads EVER!
- An Evolving Sparkle Perspective
- Office Chic – Accessories
- Office Etiquette – Introduction

### Education, Training & Tutorials

- Adobe Design Center
- Brainbench
- Coursera
- Custom Guide Online Learning
- Excel Is Fun
- Excel: Chandoo
- Excel: Motion Training
- GCF Learn Free
- HP Learning Center
- InPictures
- Khan Academy
- Lynda
- Microsoft Office Training
- Microsoft Office Videos
- Mr. Excel
- Photoshop Tutorials
- Ralph at rrphillips
- Richard at 599CD
- Safari Books Online
- Teach Excel
- TED
- Tips for Spreadsheets
- Tips for Word Processing

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

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

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.