# Excel for Statistics (Functions, Tools and Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will present 111 Excel functions for statistics and 10 practical examples to apply some of these functions. We will also discuss the 2 most common Excel tools for statistics.

Excel has a wide variety of applications in statistics. We can apply Excel functions and tools for determining descriptive statistics measures (mean, median, mode, standard deviation, percentile, quartile, etc.), data visualization, random sampling, regression analysis, time series analysis, correlation analysis, etc. purposes.

## 111 Excel Functions for Statistics in Alphabetical Order

Excel has more than 450 functions for various purposes. Among them, the following 111 formulas are the most common and crucial for statistical uses.

Function Purpose
AVEDEV Calculates the average of absolute deviations of its argument numbers from their mean.
AVERAGE Computes the arithmetic average of its argument numbers.
AVERAGEA Returns the arithmetic average of its arguments. The arguments can be numbers, texts, or logical operators. Text and FALSE values are evaluated as 0 whereas TRUE values are evaluated as 1.
AVERAGEIF Calculates the average of all the cells in its argument range that meet a single criterion.
AVERAGEIFS Returns the average of all the cells in its argument range based on multiple criteria.
BETA.DIST Returns the Beta distributionâ€™s probability density function or cumulative distribution function.
BETA.INV Calculates the inverse of the cumulative distribution function for a given Beta distribution.
BINOM.DIST Calculates the probability of a certain number of successes in a fixed number of independent trials using the Binomial distribution.
BINOM.DIST.RANGE Computes the sum of the probabilities of a range of successes in a fixed number of trials.
BINOM.INV Returns the smallest value for which the cumulative Binomial distribution is greater than or equal to a given criterion.
CHISQ.DIST Calculates the probability density function or cumulative distribution function of the Chi-squared distribution.
CHISQ.DIST.RT Returns the one-tailed (right tail) probability of the Chi-squared distribution.
CHISQ.INV Computes the inverse of the cumulative distribution function of a given left-tailed Chi-squared distribution.
CHISQ.IVT.RT Computes the inverse of the cumulative distribution function of a given right-tailed Chi-squared distribution.
CHISQ.TEST Performs a Chi-squared test of independence to determine whether the null hypothesis is TRUE or FALSE.
CONFIDENCE.NORM Uses Normal distribution to return the confidence interval for a population mean.
CONFIDENCE.T UsesÂ  Studentâ€™s t-distribution to return the confidence interval for a population mean.
CORREL Computes the correlation coefficient between two given data sets.
COUNT Counts the number of numeric values in a list of arguments.
COUNTA Returns the count of non-blank cells in a given range.
COUNTBLANK Returns the count of blank cells in a given range.
COUNTIF Counts the cells in its argument range that meet a single criterion.
COUNTIFS Counts the cells in its argument range based on multiple criteria.
COVARIANCE.P Computes population covariance, the average of the products of deviations for each data point pair between two given data sets.
COVARIANCE.S Computes the sample covariance between two data sets.
DEVSQ Calculates the sum of squares of deviations from the sample mean for its argument points.
EXPON.DIST Calculates the probability density function or cumulative distribution function of the Exponential distribution.
F.DIST Computes the probability density function of the F-distribution to determine whether two given data sets have different degrees of diversity.
F.DIST.RT Returns the right-tailed F-distribution for two given data sets.
F.INV Computes the inverse of the cumulative distribution function of the F-distribution.
F.INV.RT Computes the inverse of the right-tailed probability density function of the F-distribution.
F.TEST Performs a two-tailed F-test to compare variances of two given data sets.
FISHER Calculates the Fisher transformation at any given numeric value x where -1<x<1.
FISHERINV Computes the inverse of the Fisher transformation.
FORECAST Uses Linear Regression to predict a future value based on historical data.
FORECAST.ETS Uses the Exponential Tripple Smoothing algorithm to predict a future value based on historical data.
FORECAST.ETS.CONFINT Returns the confidence interval of any future prediction for a specific date. The future predictions are based on the Exponential Tripple Smoothing algorithm.
FORECAST.ETS.SEASONALITY Computes the seasonality (length of repetitive pattern) for any forecast based on the ExponentialTripple Smoothing Algorithm.
FORECAST.ETS.STAT Uses Time Series Forecasting to return statistical information of a forecast based on a specified statistic type.
FORECAST.LINEAR Uses Linear Regression to predict a future value based on existing historical data.
FREQUENCY Returns count of how often values occur within a range of values in a vertical array.
GAMMA Computes the Gamma function value of a given number.
GAMMA.DIST Returns the Gamma distributionâ€™s probability density function or cumulative distribution function.
GAMMA.INV Calculates the inverse of the cumulative distribution function for a given Gamma distribution.
GAMMALN Computes the Natural Logarithm of a specified numberâ€™s Gamma function.
GAMMALN.PRECISE Computes the Natural Logarithm of a specified numberâ€™s Gamma function using higher precision.
GAUSS Calculates the probability that a member of a standard normal population will fall between the mean and z standard deviations from the mean. The returned value is 0.5 less than the standard normal cumulative distribution.
GEOMEAN Returns the Geometric Mean of an array of positive numbers.
GROWTH Predicts exponential growth based on existing data.
HARMEAN Returns the Harmonic Mean of an array of numbers.
HYPGEOM.DIST Returns the Hypergeometric distribution value of a finite population.
INTERCEPT Calculates the y-axis intercept of the linear regression line based on known x and y values.
KURT Calculates the kurtosis of a data set to characterize the relative peakedness or flatness of a distribution compared with the normal distribution.
LARGE Returns the k-th largest value in an argument array.
LINEST Computes Slope, Intercept, and other parameters of a linear trend.
LOGEST Calculates the parameters of an Exponential Regression curve.
LOGNORM.DIST Calculates the probability density function or cumulative distribution function of the Lognormal distribution.
LOGNORM.INV Computes the inverse of the cumulative distribution function of a given left-tailed Lognormal distribution.
MAX Returns the maximum value from a range or an array.
MAXA Returns the maximum value from a list of arguments that can include numbers, names, text, or logical values.
MAXIFS Returns the maximum of all the values in its argument range based on multiple criteria.
MEDIAN Computes the median of a set of numbers.
MIN Returns the minimum value from a range or an array.
MINIFS Returns the minimum of all the values in its argument range based on multiple criteria.
MINA Returns the minimum value from a list of arguments that can include numbers, names, text, or logical values.
MODE.MULT Returns an array of the most frequently occurring values in a range of data.
MODE.SNGL Returns the most frequently occurring value in a range of data.
NEGBINOM.DIST Computes the Negative Binomial distributionâ€™s probability density function or cumulative distribution function.
NORM.DIST Returns the probability density function or cumulative distribution function of the Normal distribution for a specified mean and standard deviation.
NORM.INV Calculates the inverse of the cumulative distribution function for a given Normal distribution with a specified mean and standard deviation.
NORM.S.DIST Returns the probability density function or cumulative distribution function of the standard Normal distribution with a mean of 0 and standard deviation of 1.
NORM.S.INV Computes the inverse of the standard normal cumulative distribution function (has a mean of 0 and standard deviation of 1).
PEARSON Computes the Pearson correlation coefficient between two data sets.
PERCENTILE.EXC Calculates the k-th percentile of a data set excluding the data points at both ends of the dataset where 0<k<1
PERCENTILE.INC Calculates the k-th percentile of a data set including the data points at both ends of the dataset where 0<k<1.
PERCENTRANK.EXC Calculates the relative rank of a value in a data set, excluding the value itself.
PERCENTRANK.INC Calculates the relative rank of a value in a data set, including the value itself.
PERMUT Computes the number of permutations for a given number of items that can be selected from the total number of items.
PERMUTATIONA Computes the number of permutations for a given number of items (with repetitions) that can be selected from the total number of items.
PHI Returns the density functionâ€™s value of a Standard Normal distribution (has a mean of 0 and standard deviation of 1).
POISSON.DIST Computes the probability density function or cumulative distribution function of the Poisson distribution.
PROB Calculates the probability that values in a range are between two limits based on a set of values and associated probabilities.
QUARTILE.EXC Calculates the k-th quartile of an argument data set, excluding the quartile value.
QUARTILE.INC Calculates the k-th quartile of an argument data set, including the quartile value.
RANK.AVG Returns the rank of a value in a data set (the average of the ranks is returned for ties).
RANK.EQ Returns the rank of a value in a data set (the top rank is returned for ties).
RSQ Computes the coefficient of determination (square of the Pearson product moment correlation coefficient).
SKEW Returns the skewness (the amount of asymmetry of a distribution around its mean) of a data setâ€™s distribution.
SKEW.P Returns the skewness (the amount of asymmetry of a distribution around its mean) of a population.
SLOPE Calculates the slope of the linear regression line specified by argument x and y values.
SMALL Returns the k-th smallest value in an argument array.
STANDARDIZE Normalizes a value based on mean and standard deviation to return a standardized z-score.
STDEV.P Returns the standard deviation of a population while ignoring text and logical values.
STDEV.S Returns the standard deviation of a sample while ignoring text and logical values.
STDEVA Returns the standard deviation of a sample including numbers, text, and logical values.
STDEVPA Returns the standard deviation of a population including numbers, text, and logical values.
STEYX Computes the standard error of the forecasted y-value for each x-value in the regression.
T.DIST Calculates the probability density function or cumulative distribution function of the left-tailed Studentâ€™s t-distribution.
T.DIST.2T Returns the probability density function or cumulative distribution function of the two-tailed Studentâ€™s t-distribution.
T.DIST.RT Calculates the probability density function or cumulative distribution function of the right-tailed Studentâ€™s t-distribution.
T.INV Computes the inverse of the cumulative distribution function of a given left-tailed Studentâ€™s t-distribution.
T.INV.2T Computes the inverse of the cumulative distribution function of a given two-tailed Studentâ€™s t-distribution.
T.TEST Performs a t-test to compare means of two samples.
TREND Returns values in a linear trend by fitting a straight line using known x and y values.
TRIMMEAN Calculates the mean of a data set after removing a certain percentage of outliers from both ends.
VAR.P Returns the variance of a population while ignoring text and logical values.
VAR.S Returns the variance of a sample while ignoring text and logical values.
VARA Computes the variance of a sample including numbers, text, and logical values.
VARPA Computes the variance of a population including numbers, text, and logical values.
WEIBULL.DIST Returns Weibull distributionâ€™s probability density function or cumulative distribution function.
Z.TEST Calculates the P-value of a one-tailed Z-test to compare two sample means.
Note: Some of the functions may not be available in older versions of Excel.

## 10 Practical Examples to Apply Excel Functions for Statistics

This section presents 10 practical examples to use some of the most common Excel functions statistics. We will use the following dataset. It contains Sales values and Advertising Expenses for different months.

### 1. Calculate Average

First, we will calculate the average of sales using the AVERAGE function. Select Cell D18 >> type in the following formula >> press Enter key.

`=AVERAGE(C5:C16)`

We can also filter cells based on a condition using the AVERAGEIF function. Select Cell D18 >> insert the following formula >> press the Enter key.

`=AVERAGEIF(D5:D16,">6000",C5:C16)`

### 2. Determine Standard Deviation

We can use the STDEV.S function to determine the standard variation of the sales sample. Select Cell D18 >> insert the following formula >> press the Enter key.

`=STDEV.S(C5:C16)`

Read More: How to Calculate Sigma in Excel

### 3. Calculate Variance

Like standard deviation, variance also depicts the dispersion of a data set. Here, we will use the VAR.S function to calculate the variance of advertising expenses. Select Cell D18 >> type in the following formula >> press Enter key.

`=VAR.S(D5:D16)`

### 4. Calculate Median

We will use the MEDIAN function to calculate the median (i.e. the midpoint of a frequency distribution) of sales values. Select Cell D18 >> insert the following formula >> press the Enter key.

`=MEDIAN(C5:C16)`

### 5. Find the Most Frequently Occurring Value

To calculate the most frequently occurring value in a range, we can use the MODE.SNGL function. Insert the following formula in Cell D18 and press the Enter key.

`=MODE.SNGL(D5:D16)`

### 6. Count Cells

We can count the number of cells with numerical values using the COUNT function. Select Cell D18 >> type in the following formula >> press Enter key.

`=COUNT(D5:D16)`

If we require counting cells with numbers, text, or logical values, then we can use the COUNTA function. It returns the number of non-blank cells in a range. Insert the following formula in Cell D18 and press the Enter key.

`=COUNTA(D5:D16)`

We can filter which cells to count by applying one or multiple conditions. Here, we will apply the COUNTIFS function to count months where sales in more than \$50,000 and advertising expense is less than \$7000. Type in the following formula in Cell D18 and press the Enter key.

`=COUNTIFS(C5:C16,">50000",D5:D16,"<7000")`

### 7. Calculate Quartile and Quartile Deviation

Quartile Deviation values are often used for examining the spread of a data range about a central value (mean or average). Here, we will use the QUARTILE.INC function to determine Quartile and Quartile Deviation of sales.

First, we have to determine the First Quartile (Q1) value. Select Cell D18 >> type in the following formula >> press the Enter key.

`=QUARTILE.INC(C5:C16,1)`

Similarly, insert the following formula in Cell D19 and press the Enter key.

`=QUARTILE.INC(C5:C16,3)`

Finally, select Cell D20 >> type in the following formula >> press Enter key to calculate the Quartile Deviation value.

`=(D19-D18)/2`

### 8. Determine Percentile Value

Percentile values are particularly useful when we want to understand how a particular data point compares to the rest of the data distribution. Here, weâ€™ll use the PERCENTILE.INC function to determine the 90th percentile of sales.

Insert the following formula in Cell D18 and press the Enter key.

`=PERCENTILE.INC(C5:C16,90%)`

### 9. Determine Maximum and Minimum Values

Maximum and minimum values in a range also help us depict the dispersion of a data set and measure the performance of any organization. First, we will use the MAX function to determine the maximum sales value.

Select Cell D18 >> type in the following formula >> press Enter key.

`=MAX(C5:C16)`

We can also use the LARGE function to determine the maximum value. Apart from the largest value, it can also return the second, third, or other largest values. Select Cell D18 >> type in the following formula >> press Enter key to get the third largest sales value.

`=LARGE(C5:C16,3)`

Similarly, we can apply the MIN function to calculate the minimum value in a data set. Insert the following formula in Cell D18 and press the Enter key to determine the smallest advertising expense.

`=MIN(D5:D16)`

To get the second, third, or other smallest values, we can use the SMALL function. Select Cell D18 >> insert the following formula >> press the Enter key to get the second smallest value of advertising expense.

`=SMALL(D5:D16,2)`

### 10. Find Correlation Between Two Columns

Here, we will check the correlation between advertising expense and sales values by using the CORREL function. Select Cell D18 >> type in the following formula >> press the Enter key.

`=CORREL(C5:C16,D5:D16)`

As the output (correlation coefficient) is closer to 1, we can say there is a positive correlation between advertising expenses and sales.

## 2 Most Common Statistical Tools in Excel and Their Uses

Along with the Excel functions, we also have several Excel tools for statistics. In this section, we will discuss the 2 most common Excel tools for statistics.

### 1. Apply the Regression Tool

The Regression tool is one of the most common tools in Excel. To apply this tool, first, go to the Data tab and click on the Data Analysis option from the Analyze menu.

At this time, the Data Analysis dialogue box will pop up. Scroll down and select the Regression option. Then, click the OK button.

Afterward, the Regression dialog box will appear. Set the Input Y Range to C5:C16 and Input X Range to D5:D16 >> check the checkbox of Labels >> click the radio button of the Output Range option and set it to Cell B20 >> click the OK button.

The following output will appear. It contains a summary of the Regression Analysis.

Read More: How to Find Critical Value in Excel

### 2. Use the Descriptive Statistics Tool

Descriptive Statistics is another common statistical tool that is used for determining common statistical measures such as mean, median, mode, standard deviation, skewness, count, etc. To apply this tool, first, go to the Data tab and select the Data Analysis option from the Analyze menu.

At this point, the Data Analysis dialogue box will pop up. Scroll down and select the Descriptive Statistics option. Then, click the OK button.

After that, the Descriptive Statistics dialog box will appear. Set the Input Range to D5:D16 (advertising expenses) >> set the Grouped by option to Column >> click the radio button of the Output Range option and set it to F2 >> check the checkbox of the Summary statistics option >> click the OK button.

All descriptive statistics measures for the advertising expenses will appear.

Read More: Descriptive Statistics in Excel

## Things to Remember

• Many Excel functions were introduced in Excel 2010, Excel 2016, and Excel 365 versions. Therefore, some functions may not be available in older versions.
• If the Data Analysis ToolPak is not available in Analyze menu of the Data tab, then load it from the following directory.
`File >> Options >> Add-ins >> Excel Add-ins >> Go >> Analysis ToolPak`

• What is the advantage of using Excel for statistical analysis?

Answer: Excel has various built-in functions and tools for statistics. These are suitable for quick analyses, data exploration, and basic statistical calculations without requiring specialized statistical knowledge.

• How to calculate Margin Percentage in Excel?

Answer: To calculate Margin Percentage, we require Revenue (total sales) and Margin (difference between Revenue and Cost of Items Sold) values. Assuming Revenue and Margin values are in Columns C and D respectively, we can apply the following formula to calculate Margin Percentage in Excel.

`=(D2/C2)`

We may also need to apply the Percent Style formatting from the Number menu of the Home tab.

• What is the difference between CORREL and PEARSON functions?

Answer: In Excel,Â  both the CORREL and PEARSON functions are used to calculate the correlation coefficient between two sets of data. There is actually no functional difference between these functions and we can use them interchangeably.

• How can I handle missing data and outliers in Excel for statistical analysis?

Answer: Excel has functions like IF and FILTER to clean unwanted data. Several Excel functions such as AVERAGEIF, COUNTIF, MAXIFS, MINIFS, TRIMMEAN, etc. can also clean unwanted data and handle outliers. Other functions like TREND, GROWTH, FORECAST, etc. can predict data to handle missing data.

## Conclusion

This concludes our article on the application of Excel for statistics. We discussed 111 statistical functions and 10 practical examples to use them. Afterward, we discussed 2 Excel tools for statistical analysis as well. We hope the demonstrated examples were sufficient for your quest to learn the application of Excel for Statistics. Let us know your feedback. Visit our website ExcelDemy.com for more articles related to Excel.

## Excel for Statistics: Knowledge Hub

<< Go Back to Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Seemanto Saha

Seemanto Saha graduated in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. He has been with ExcelDemy for a year, where he wrote 40+ articles and reviewed 50+ articles. He has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, he is working as a team leader for ExcelDemy. His role is to guide his team to write reader-friendly content. His interests are Advanced Excel, Data Analysis, Charts & Dashboards, Power Query,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF