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.

**Download Practice File**

You may download the following file and practice yourself.

**Table of Contents**Expand

## 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)`

**Read More:** How to Find Mean, Median, and Mode on Excel

### 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)`

**Read Mode:** MAX vs MAXA vs LARGE and MIN vs MINA vs SMALL Functions in Excel

### 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**to

__X__Range**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`

## Frequently Asked Questions

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

- Bootstrapping in Excel
- Bootstrapping Spot Rates in Excel
- How to Resample Time Series in Excel
- Calculate Margin of Error in Excel
- Scaling Formula in Excel
- Tally in Excel
- Statistical Significance in Excel
- Confidence Interval in Excel
- Excel Interpolation
- How to Extrapolate Data in Excel
- How to Create a Survey in Excel
- Excel Demographic Data
- How to Calculate Median in Excel
- Anova in Excel
- How to Perform Regression Analysis in Excel

**<< Go Back to Learn Excel**