Statistical functions in Excel are a powerful toolkit for data analysis. They calculate averages, the median, the standard deviation and many other statistics, and they are used everywhere, from financial analysis to scientific research. Some cover basic calculations, others are designed for serious statistical analysis.
Below are the most popular functions with examples on one small dataset, a few tips on choosing between similar functions, ways to get statistics without any formulas, and the full list of Excel statistical functions.
Sample data
All examples below use this table: regions in column A, weekly sales in column B and the week number in column C.
| Row | A: Region | B: Sales | C: Week |
|---|---|---|---|
| 2 | East | 120 | 1 |
| 3 | West | 85 | 2 |
| 4 | East | 200 | 3 |
| 5 | North | 85 | 4 |
| 6 | West | 150 | 5 |
| 7 | East | 95 | 6 |
| 8 | North | 60 | 7 |
| 9 | West | 110 | 8 |
Top 20 popular statistical functions
These are the statistical functions people search for most often. The result column shows what each formula returns on the sample data.
| Function | What it does | Example | Result |
|---|---|---|---|
| COUNT | Counts the cells with numbers in a range. | =COUNT(B2:B9) | 8 |
| COUNTIF | Counts the cells that meet one condition. | =COUNTIF(A2:A9,"East") | 3 |
| COUNTIFS | Counts the cells that meet several conditions. | =COUNTIFS(A2:A9,"East",B2:B9,">100") | 2 |
| COUNTA | Counts non-empty cells, whatever they contain. | =COUNTA(A2:A9) | 8 |
| AVERAGE | The arithmetic mean, one of the most used functions in data analysis. | =AVERAGE(B2:B9) | 113.125 |
| AVERAGEIF | The mean of the cells that meet a condition. | =AVERAGEIF(A2:A9,"West",B2:B9) | 115 |
| MEDIAN | The middle value of the sorted numbers: the “center” of the data, not skewed by outliers. | =MEDIAN(B2:B9) | 102.5 |
| MAX, MIN | The largest and the smallest value. | =MAX(B2:B9) | 200 |
| MAXIFS, MINIFS | The largest or smallest value among the cells that meet one or more conditions. | =MAXIFS(B2:B9,A2:A9,"West") | 150 |
| LARGE, SMALL | The k-th largest and the k-th smallest value. | =LARGE(B2:B9,2) | 150 |
| MODE.SNGL | The most frequent value. | =MODE.SNGL(B2:B9) | 85 |
| STDEV.S | The standard deviation of a sample: how much the values vary. | =STDEV.S(B2:B9) | 44.2 |
| VAR.S | The variance of a sample. | =VAR.S(B2:B9) | 1956.7 |
| FREQUENCY | The distribution of values across bins, returned as a vertical array. | =FREQUENCY(B2:B9,{100;150}) | 4; 3; 1 |
| RANK.EQ | The rank of a number in a list. | =RANK.EQ(B4,B2:B9) | 1 |
| PERCENTILE.INC | The k-th percentile, for example the value below which 90% of the data lies. | =PERCENTILE.INC(B2:B9,0.9) | 165 |
| TRIMMEAN | The mean without the extreme values: here 25% of the points, one from each end. | =TRIMMEAN(B2:B9,0.25) | 107.5 |
| FORECAST.LINEAR | Predicts a future value based on a linear trend. Here: sales in week 9. | =FORECAST.LINEAR(9,B2:B9,C2:C9) | 89.3 |
| CORREL | The correlation coefficient between two data sets, from −1 to 1. | =CORREL(B2:B9,C2:C9) | −0.29 |
| LINEST, INTERCEPT, SLOPE | The parameters of a linear regression line: its slope and the point where it crosses the Y axis. | =SLOPE(B2:B9,C2:C9) | −5.3 |
FREQUENCY returns several values at once: in Excel 365 they spill down automatically, in older versions select three cells and confirm with Ctrl+Shift+Enter as an array formula. The bins {100;150} mean “up to 100”, “101 to 150” and “over 150”.
How to choose between similar functions
- AVERAGE or MEDIAN? The average reacts to outliers, the median doesn’t. In the sample, one strong week of 200 pulls the average to 113.1, while the median is 102.5. For salaries, prices and order values, the median often tells the truth better.
- STDEV.S or STDEV.P? Use .S (sample) when your data is a part of all possible observations, which is almost always the case. Use .P (population) only when you have every value there is. On the sample data, it’s 44.2 vs 41.4. The same goes for VAR.S and VAR.P.
- Functions ending in A, like AVERAGEA, MAXA and STDEVA, count text as 0 and TRUE as 1. The regular versions simply ignore text and logical values, which is usually what you want.
- STDEV, VAR, MODE, RANK, PERCENTILE, QUARTILE and other functions without a dot are the legacy versions. They still work for compatibility, but in new workbooks use the versions with a dot: STDEV.S, MODE.SNGL, RANK.EQ and so on.
- MAXIFS and MINIFS are available from Excel 2019. In older versions, use an array formula like =MAX(IF(A2:A9=”West”,B2:B9)).
To round the results, wrap the formula in ROUND, for example =ROUND(STDEV.S(B2:B9),1). And for sums by condition, see the separate articles on SUMIFS and SUMPRODUCT.
Statistics without formulas
You don’t always need a formula to look at the numbers:
- The status bar. Select a range and look at the bottom of the window: Excel shows the Average, Count and Sum. Right-click the status bar to add Minimum, Maximum and Numerical Count.
- The Analysis ToolPak. Turn it on in File → Options → Add-ins → Excel Add-ins → Analysis ToolPak. Data → Data Analysis → Descriptive Statistics then gives a whole report at once: mean, median, mode, standard deviation, variance, kurtosis, skewness, range, min, max and count.
- Pivot tables summarize large tables by groups: sum, count, average, max, min, standard deviation. A quick way to build one is shown in the article on how to count unique values.
And there are metrics Excel has no single function for at all: the number of occurrences of each value right next to it, the occurrence number, the share of the total, the cumulative share and ABC analysis. With formulas, each of them means a helper column, a formula with absolute references and filling it down. In my !SEMTools add-in, these are statistical metrics procedures: select the column, click, and the result appears right next to the data.
Count occurrences, shares, cumulative shares and ABC classes for a whole column in a couple of clicks, without helper formulas.
!SEMTools solves this and hundreds of other tasks right in Excel!
Full list of Excel statistical functions
Here is the full alphabetical list of statistical functions with a short description. All of them are available in the current version of Excel (Microsoft 365).
The Version column shows the Excel version in which the function first appeared. If it’s empty, the function is available in all versions.
| Function | Version | What it does |
|---|---|---|
| AVEDEV | Returns the average of the absolute deviations of data points from their mean. | |
| AVERAGE | Returns the average of its arguments. | |
| AVERAGEA | Returns the average of its arguments, including numbers, text and logical values. | |
| AVERAGEIF | Returns the average of the cells that meet a condition. | |
| AVERAGEIFS | Returns the average of the cells that meet multiple conditions. | |
| BETA.DIST | 2010 | Returns the beta cumulative distribution function. |
| BETA.INV | 2010 | Returns the inverse of the cumulative distribution function for a specified beta distribution. |
| BINOM.DIST | 2010 | Returns the individual term binomial distribution probability. |
| BINOM.DIST.RANGE | 2013 | Returns the probability of a trial result using a binomial distribution. |
| BINOM.INV | 2010 | Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. |
| CHISQ.DIST | 2010 | Returns the left-tailed probability of the chi-squared distribution. |
| CHISQ.DIST.RT | 2010 | Returns the right-tailed probability of the chi-squared distribution. |
| CHISQ.INV | 2010 | Returns the inverse of the left-tailed probability of the chi-squared distribution. |
| CHISQ.INV.RT | 2010 | Returns the inverse of the right-tailed probability of the chi-squared distribution. |
| CHISQ.TEST | 2010 | Returns the test for independence. |
| CONFIDENCE.NORM | 2010 | Returns the confidence interval for a population mean. |
| CONFIDENCE.T | 2010 | Returns the confidence interval for a population mean, using a Student’s t distribution. |
| CORREL | Returns the correlation coefficient between two data sets. | |
| COUNT | Counts how many numbers are in the list of arguments. | |
| COUNTA | Counts how many values are in the list of arguments. | |
| COUNTBLANK | Counts the number of blank cells within a range. | |
| COUNTIF | Counts the number of cells within a range that meet the given criteria. | |
| COUNTIFS | Counts the number of cells within a range that meet multiple criteria. | |
| COVARIANCE.P | 2010 | Returns the population covariance, the average of the products of paired deviations. |
| COVARIANCE.S | 2010 | Returns the sample covariance, the average of the products of deviations for each data point pair in two data sets. |
| DEVSQ | Returns the sum of squares of deviations. | |
| EXPON.DIST | 2010 | Returns the exponential distribution. |
| F.DIST | 2010 | Returns the F probability distribution. |
| F.DIST.RT | 2010 | Returns the right-tailed F probability distribution. |
| F.INV | 2010 | Returns the inverse of the F probability distribution. |
| F.INV.RT | 2010 | Returns the inverse of the right-tailed F probability distribution. |
| F.TEST | 2010 | Returns the result of an F-test. |
| FISHER | Returns the Fisher transformation. | |
| FISHERINV | Returns the inverse of the Fisher transformation. | |
| FORECAST | Returns a value along a linear trend. In Excel 2016 it was replaced by FORECAST.LINEAR but is still available for compatibility. | |
| FORECAST.ETS | 2016 | Returns a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm. |
| FORECAST.ETS.CONFINT | 2016 | Returns a confidence interval for the forecast value at the specified target date. |
| FORECAST.ETS.SEASONALITY | 2016 | Returns the length of the repetitive pattern Excel detects for the specified time series. |
| FORECAST.ETS.STAT | 2016 | Returns a statistical value as a result of time series forecasting. |
| FORECAST.LINEAR | 2016 | Returns a future value based on existing values. |
| FREQUENCY | Returns a frequency distribution as a vertical array. | |
| GAMMA | 2013 | Returns the Gamma function value. |
| GAMMA.DIST | 2010 | Returns the gamma distribution. |
| GAMMA.INV | 2010 | Returns the inverse of the gamma cumulative distribution. |
| GAMMALN | Returns the natural logarithm of the gamma function, Γ(x). | |
| GAMMALN.PRECISE | 2010 | Returns the natural logarithm of the gamma function, Γ(x). |
| GAUSS | 2013 | Returns 0.5 less than the standard normal cumulative distribution. |
| GEOMEAN | Returns the geometric mean. | |
| GROWTH | Returns values along an exponential trend. | |
| HARMEAN | Returns the harmonic mean. | |
| HYPGEOM.DIST | 2010 | Returns the hypergeometric distribution. |
| INTERCEPT | Returns the intercept of the linear regression line. | |
| KURT | Returns the kurtosis of a data set. | |
| LARGE | Returns the k-th largest value in a data set. | |
| LINEST | Returns the parameters of a linear trend. | |
| LOGEST | Returns the parameters of an exponential trend. | |
| LOGNORM.DIST | 2010 | Returns the cumulative lognormal distribution. |
| LOGNORM.INV | 2010 | Returns the inverse of the lognormal cumulative distribution. |
| MAX | Returns the maximum value in a list of arguments. | |
| MAXA | Returns the maximum value in a list of arguments, including numbers, text and logical values. | |
| MAXIFS | 2019 | Returns the maximum value among cells specified by a given set of conditions or criteria. |
| MEDIAN | Returns the median of the given numbers. | |
| MIN | Returns the minimum value in a list of arguments. | |
| MINA | Returns the smallest value in a list of arguments, including numbers, text and logical values. | |
| MINIFS | 2019 | Returns the minimum value among cells specified by a given set of conditions or criteria. |
| MODE.MULT | 2010 | Returns a vertical array of the most frequently occurring, or repetitive, values in an array or range of data. |
| MODE.SNGL | 2010 | Returns the most common value in a data set. |
| NEGBINOM.DIST | 2010 | Returns the negative binomial distribution. |
| NORM.DIST | 2010 | Returns the normal cumulative distribution. |
| NORM.INV | 2010 | Returns the inverse of the normal cumulative distribution. |
| NORM.S.DIST | 2010 | Returns the standard normal cumulative distribution. |
| NORM.S.INV | 2010 | Returns the inverse of the standard normal cumulative distribution. |
| PEARSON | Returns the Pearson product moment correlation coefficient. | |
| PERCENTILE.EXC | 2010 | Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive. |
| PERCENTILE.INC | 2010 | Returns the k-th percentile of values in a range. |
| PERCENTRANK.EXC | 2010 | Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set. |
| PERCENTRANK.INC | 2010 | Returns the percentage rank of a value in a data set. |
| PERMUT | Returns the number of permutations for a given number of objects. | |
| PERMUTATIONA | 2013 | Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects. |
| PHI | 2013 | Returns the value of the density function for a standard normal distribution. |
| POISSON.DIST | 2010 | Returns the Poisson distribution. |
| PROB | Returns the probability that values in a range are between two limits. | |
| QUARTILE.EXC | 2010 | Returns the quartile of the data set, based on percentile values from 0..1, exclusive. |
| QUARTILE.INC | 2010 | Returns the quartile of a data set. |
| RANK.AVG | 2010 | Returns the rank of a number in a list of numbers; tied values get the average rank. |
| RANK.EQ | 2010 | Returns the rank of a number in a list of numbers; tied values get the top rank. |
| RSQ | Returns the square of the Pearson product moment correlation coefficient. | |
| SKEW | Returns the skewness of a distribution. | |
| SKEW.P | 2013 | Returns the skewness of a distribution based on a population: a measure of the asymmetry of a distribution around its mean. |
| SLOPE | Returns the slope of the linear regression line. | |
| SMALL | Returns the k-th smallest value in a data set. | |
| STANDARDIZE | Returns a normalized value. | |
| STDEV.P | 2010 | Calculates standard deviation based on the entire population. |
| STDEV.S | 2010 | Estimates standard deviation based on a sample. |
| STDEVA | Estimates standard deviation based on a sample, including numbers, text and logical values. | |
| STDEVPA | Calculates standard deviation based on the entire population, including numbers, text and logical values. | |
| STEYX | Returns the standard error of the predicted y-value for each x in the regression. | |
| T.DIST | 2010 | Returns the left-tailed Student’s t-distribution. |
| T.DIST.2T | 2010 | Returns the two-tailed Student’s t-distribution. |
| T.DIST.RT | 2010 | Returns the right-tailed Student’s t-distribution. |
| T.INV | 2010 | Returns the t-value of the Student’s t-distribution as a function of the probability and the degrees of freedom. |
| T.INV.2T | 2010 | Returns the inverse of the two-tailed Student’s t-distribution. |
| T.TEST | 2010 | Returns the probability associated with a Student’s t-test. |
| TREND | Returns values along a linear trend. | |
| TRIMMEAN | Returns the mean of the interior of a data set. | |
| VAR.P | 2010 | Calculates variance based on the entire population. |
| VAR.S | 2010 | Estimates variance based on a sample. |
| VARA | Estimates variance based on a sample, including numbers, text and logical values. | |
| VARPA | Calculates variance based on the entire population, including numbers, text and logical values. | |
| WEIBULL.DIST | 2010 | Returns the Weibull distribution. |
| Z.TEST | 2010 | Returns the one-tailed probability value of a z-test. |
Summary
Statistical functions are used in all kinds of fields, from business analysis and accounting to engineering and research. They let you quickly analyze large amounts of data and make decisions based on that analysis.
I hope you found the function you were looking for on this page. COUNT, AVERAGE, MEDIAN, MAX and MIN cover most everyday tasks, the IF and IFS versions add conditions, and the rest of the list is for real statistical analysis. For shares, cumulative shares and ABC classes, try the !SEMTools procedures: select the column and get the result in a couple of clicks. All function guides are collected in the Excel functions section.