Statistical functions
Statistical functions summarize a set of numbers: averages, counts, spread, ranks and forecasts. Several come in older and newer versions with dotted names.
111 functions in this category.
Start here
- COUNTIF
Counts the cells that match a single condition, such as how many orders are over 100 or how many names start with A. One of the most used summary functions.
- COUNTIFS
Counts rows where all the listed conditions hold at once, such as orders from one region in a given month. The standard way to do AND-style counting.
- GROWTH
Fits y = b*m^x to your data and returns predictions, suitable for steady percentage growth like compounding sales.
- AVERAGE
Adds up the numbers and divides by how many there are. It is the everyday way to summarise a column of figures.
- NORM.DIST
Tells you how likely a value is under a bell curve, for example the share of test scores below 42 with a mean of 40.
- COUNT
Counts numeric entries, including dates, and skips text and blanks. Use it to check how many figures a column really holds.
All statistical functions
| Function | What it does | Level | Since | Spills? |
|---|---|---|---|---|
AVEDEV | Measures how spread out values are by averaging how far each one sits from the mean, ignoring direction. Handy as a simple, outlier-tolerant alternative to standard deviation. | Expert | 2007 or earlier | No |
AVERAGE | Adds up the numbers and divides by how many there are. It is the everyday way to summarise a column of figures. | Beginner | 2007 or earlier | No |
AVERAGEA | Like AVERAGE, but it does not skip text or logical values: text counts as zero and TRUE counts as one. Use it when non-numeric entries should drag the average down rather than disappear. | Expert | 2007 or earlier | No |
AVERAGEIF | Averages only the rows that satisfy one condition, for example average sales for one region. Use AVERAGEIFS when you need more than one condition. | Beginner | 2007 or earlier | No |
AVERAGEIFS | Averages values only when all of the listed conditions hold, such as sales for a region in a given month. It is the multi-condition version of AVERAGEIF. | Beginner | 2007 or earlier | No |
BETA.DIST | Evaluates the beta distribution, which models quantities that live between two limits, such as a completion percentage or a proportion. Used in project-risk modelling and Bayesian work. | Expert | 2010 | No |
BETA.INV | Works backwards from a probability to the value of x for a beta distribution. It is the inverse of BETA.DIST with cumulative set to TRUE. | Expert | 2010 | No |
BINOM.DIST | Tells you how likely it is to get a set number of successes in a fixed number of yes/no trials, such as defective items in a batch or conversions from sales calls. | Intermediate | 2010 | No |
BINOM.DIST.RANGE | Gives the chance that the number of successes lies between two values, inclusive. It saves subtracting two cumulative BINOM.DIST results by hand. | Expert | 2013 | No |
BINOM.INV | Finds the lowest number of successes at which the cumulative binomial probability meets or beats a threshold. Used in quality control and acceptance sampling. | Expert | 2010 | No |
CHISQ.DIST | Gives the chance of seeing a chi-squared value up to x. It is the building block for goodness-of-fit and independence tests. | Expert | 2010 | No |
CHISQ.DIST.RT | Returns the chance of a chi-squared value at least as large as x, which is exactly a p-value for a chi-squared test statistic. | Expert | 2010 | No |
CHISQ.INV | Turns a left-tail probability into the matching chi-squared value. Useful for building confidence intervals around a variance. | Expert | 2010 | No |
CHISQ.INV.RT | Gives the critical value to compare a chi-squared statistic against at a chosen significance level. | Expert | 2010 | No |
CHISQ.TEST | Compares what you observed to what you expected and returns the p-value. A small value suggests the difference is unlikely to be chance, as in checking whether survey answers depend on a group. | Expert | 2010 | No |
CONFIDENCE.NORM | Returns the plus-or-minus amount to put around a sample mean for a confidence interval when the population standard deviation is known. | Intermediate | 2010 | No |
CONFIDENCE.T | Like CONFIDENCE.NORM but suited to small samples where the population spread is unknown. The result is a little wider to allow for that uncertainty. | Intermediate | 2010 | No |
CORREL | Measures how strongly two series move together in a straight-line way. Close to 1 means they rise together, close to -1 means one rises as the other falls. | Intermediate | 2007 or earlier | No |
COUNT | Counts numeric entries, including dates, and skips text and blanks. Use it to check how many figures a column really holds. | Beginner | 2007 or earlier | No |
COUNTA | Counts every non-empty entry of any type, including text, errors and empty strings. Good for counting filled rows in a list. | Beginner | 2007 or earlier | No |
COUNTBLANK | Counts the blank cells in a range, handy for spotting missing data. Cells holding "" from a formula are counted as blank too. | Intermediate | 2007 or earlier | No |
COUNTIF | Counts the cells that match a single condition, such as how many orders are over 100 or how many names start with A. One of the most used summary functions. | Beginner | 2007 or earlier | No |
COUNTIFS | Counts rows where all the listed conditions hold at once, such as orders from one region in a given month. The standard way to do AND-style counting. | Beginner | 2007 or earlier | No |
COVARIANCE.P | Shows whether two series tend to move in the same direction, averaging the product of their deviations. Use it when you have the full population rather than a sample. | Expert | 2010 | No |
COVARIANCE.S | Like COVARIANCE.P but divides by n-1, which is the right choice when your data is a sample from a larger group. | Expert | 2010 | No |
DEVSQ | Adds up each value's squared distance from the average. It is the raw ingredient behind variance and standard deviation. | Expert | 2007 or earlier | No |
EXPON.DIST | Models the waiting time between random events, such as time between customer arrivals or machine failures. | Expert | 2010 | No |
F.DIST | Evaluates the F distribution, used when comparing variances or running ANOVA. | Expert | 2010 | No |
F.DIST.RT | Gives the p-value for an F statistic, the chance of a ratio at least this large. | Expert | 2010 | No |
F.INV | Converts a left-tail probability into the matching F value. | Expert | 2010 | No |
F.INV.RT | Gives the F critical value to compare against your test statistic at a chosen significance level. | Expert | 2010 | No |
F.TEST | Tests whether two samples have different spread. A small result suggests their variances really differ. | Expert | 2010 | No |
FISHER | Stretches a correlation coefficient into a roughly normal scale, used for testing or building intervals around a correlation. | Expert | 2007 or earlier | No |
FISHERINV | Converts a value from the Fisher scale back to a correlation coefficient between -1 and 1. | Expert | 2007 or earlier | No |
FORECAST | Fits a least-squares line to past data and reads off the predicted value at a new x. Kept for compatibility; FORECAST.LINEAR does the same. | Intermediate | 2007 or earlier | No |
FORECAST.ETS | Projects a future value from a time series, allowing for trend and repeating seasonal patterns. Useful for sales or demand forecasting. | Advanced | 2016 | No |
FORECAST.ETS.CONFINT | Tells you how far above and below the forecast the real value is likely to land. Add and subtract it from FORECAST.ETS to draw a range. | Advanced | 2016 | No |
FORECAST.ETS.SEASONALITY | Reports the number of periods in the seasonal cycle found in your series, for example 12 for monthly data with a yearly pattern. | Advanced | 2016 | No |
FORECAST.ETS.STAT | Returns a parameter or error measure from the ETS fit, letting you judge how well the forecast model matches your history. | Advanced | 2016 | No |
FORECAST.LINEAR | Predicts a value by extending the best-fit line through your data. It is the modern name for FORECAST and gives identical results. | Intermediate | 2016 | No |
FREQUENCY | Builds a frequency table: counts how many values fall at or below each bin limit and above the previous one. The result has one more item than the bins. | Advanced | 2007 or earlier | Yes |
GAMMA | Extends the factorial to non-whole numbers: GAMMA(n) equals (n-1)! for positive integers. | Expert | 2013 | No |
GAMMA.DIST | Evaluates the gamma distribution, which models positive skewed quantities such as waiting times or claim sizes. | Expert | 2010 | No |
GAMMA.INV | Works backwards from a probability to the gamma distribution value; the inverse of GAMMA.DIST with cumulative TRUE. | Expert | 2010 | No |
GAMMALN | Gives the log of GAMMA(x), which avoids overflow when working with very large factorial-like values. | Expert | 2007 or earlier | No |
GAMMALN.PRECISE | Same job as GAMMALN, introduced with updated accuracy and consistent naming. | Expert | 2010 | No |
GAUSS | Returns the area under the standard normal curve from the mean out to z. It is 0.5 less than the cumulative NORM.S.DIST. | Expert | 2013 | No |
GEOMEAN | Averages growth factors or ratios by multiplying and taking the root. Use it for average growth rates over several periods. | Intermediate | 2007 or earlier | No |
GROWTH | Fits y = b*m^x to your data and returns predictions, suitable for steady percentage growth like compounding sales. | Advanced | 2007 or earlier | Yes |
HARMEAN | Averages rates such as speeds or price-to-earnings ratios correctly by using reciprocals. | Expert | 2007 or earlier | No |
HYPGEOM.DIST | Gives the chance of drawing a number of successes when sampling without replacement, such as defective parts drawn from a small batch. | Expert | 2010 | No |
INTERCEPT | Returns the y value at x = 0 for the least-squares line through your data. Pair it with SLOPE to write the line equation. | Intermediate | 2007 or earlier | No |
KURT | Describes how heavy the tails of a data set are compared with a normal distribution; positive means more extreme outliers, negative means fewer. | Expert | 2007 or earlier | No |
LARGE | Picks the nth biggest number, for example the second highest sale or the top three scores when combined with other functions. | Beginner | 2007 or earlier | No |
LINEST | Runs a least-squares regression and returns coefficients, with multiple x columns allowed. The go-to for building a regression model in Excel. | Advanced | 2007 or earlier | Yes |
LOGEST | Fits an exponential curve to your data and gives back the growth factor per x-step and the starting value. Use it for things that grow by a steady percentage, like users or revenue compounding over time. | Expert | 2007 or earlier | Yes |
LOGNORM.DIST | Works with data whose logarithm is normally distributed, such as incomes, prices or claim sizes. Gives the chance of a value being at or below x when cumulative is TRUE. | Expert | 2010 | No |
LOGNORM.INV | The reverse of LOGNORM.DIST: pick a probability and get the cutoff value. Handy for percentile cutoffs in skewed data like salaries or loss amounts. | Expert | 2010 | No |
MAX | Finds the biggest value in a list, like top sale or latest date. One of the most used summary functions. | Beginner | 2007 or earlier | No |
MAXA | Like MAX but it does not skip logical values and text in ranges. Rarely needed, but useful when TRUE/FALSE flags are part of the data. | Intermediate | 2007 or earlier | No |
MAXIFS | A conditional MAX, so you can ask for the top sale for one region or the latest date for one customer without array formulas. | Beginner | 2019 | No |
MEDIAN | Gives the middle of a sorted list, so half the values are above and half below. Better than AVERAGE when a few extreme values would skew the picture, like salaries or house prices. | Beginner | 2007 or earlier | No |
MIN | Finds the lowest value in a list, such as the cheapest price or earliest date. | Beginner | 2007 or earlier | No |
MINA | A version of MIN that counts logical values and text instead of skipping them. Uncommon in practice. | Intermediate | 2007 or earlier | No |
MINIFS | A conditional MIN: find the lowest price in a category or the earliest order date for a customer in one formula. | Beginner | 2019 | No |
MODE.MULT | Lists all modes when several values occur equally often, unlike MODE.SNGL which gives only one. Useful for survey answers or categorical codes stored as numbers. | Advanced | 2010 | Yes |
MODE.SNGL | Returns the value that shows up most often in a set of numbers. If several tie, you get the one that appears first in the data. | Intermediate | 2010 | No |
NEGBINOM.DIST | Models how many failures you see before hitting a set number of successes, such as how many cold calls fail before landing five meetings. | Expert | 2010 | No |
NORM.DIST | Tells you how likely a value is under a bell curve, for example the share of test scores below 42 with a mean of 40. | Intermediate | 2010 | No |
NORM.INV | Works backwards from a probability to a cutoff, like the score needed to be in the top 10 percent. | Intermediate | 2010 | No |
NORM.S.DIST | A normal curve with mean 0 and standard deviation 1. Turns a z-score into a probability, the usual step in hypothesis testing. | Intermediate | 2010 | No |
NORM.S.INV | Converts a confidence level into a z critical value, such as 1.96 for 95 percent two-sided intervals (use 0.975). | Intermediate | 2010 | No |
PEARSON | Measures how strongly two series move together in a straight line. Same result as CORREL, so pick whichever you remember. | Intermediate | 2007 or earlier | No |
PERCENTILE.EXC | Calculates a percentile using the exclusive method, which treats the data as a sample and cannot return the min or max. Use it when a method requires the exclusive definition. | Intermediate | 2010 | No |
PERCENTILE.INC | The everyday percentile function: find the 90th percentile of response times or the cutoff for top scorers. Interpolates between values when needed. | Intermediate | 2010 | No |
PERCENTRANK.EXC | Shows where a value sits relative to a dataset using the exclusive method. A value of 0.5 means roughly the middle. | Expert | 2010 | No |
PERCENTRANK.INC | Tells you what share of the data falls below x, with the smallest value at 0 and the largest at 1. Interpolates if x is between data points. | Intermediate | 2010 | No |
PERMUT | Counts how many ways you can pick and order some items from a larger set, such as podium finishes among runners. | Expert | 2007 or earlier | No |
PERMUTATIONA | Counts sequences where an item can be used again, such as possible PIN codes or passwords of a given length. | Expert | 2013 | No |
PHI | Returns the standard normal density, the curve height rather than an area. Rarely needed outside statistics coursework. | Expert | 2013 | No |
POISSON.DIST | Models counts of random events in a fixed interval, like support tickets per hour or defects per batch. | Intermediate | 2010 | No |
PROB | Adds up probabilities from a custom discrete distribution, e.g. the chance a project takes 2 or 3 weeks. | Expert | 2007 or earlier | No |
QUARTILE.EXC | Gives quartiles without allowing the min or max as a result. Choose it when a textbook or standard demands the exclusive method. | Intermediate | 2010 | No |
QUARTILE.INC | Splits data into quarters, handy for box plots and spotting outliers with the IQR. Same as the older QUARTILE. | Intermediate | 2010 | No |
RANK.AVG | Ranks values and splits ties fairly: two values tied for 2nd and 3rd both get 2.5. | Intermediate | 2010 | No |
RANK.EQ | Standard leaderboard ranking. Tied values get the same rank and the next rank is skipped. | Beginner | 2010 | No |
RSQ | Shows how much of the variation in y is explained by x in a straight-line fit. Used to judge how good a trendline is. | Intermediate | 2007 or earlier | No |
SKEW | Measures asymmetry: positive means a long tail to the right, negative a long tail to the left. Used to check whether data like incomes is lopsided. | Expert | 2007 or earlier | No |
SKEW.P | Same idea as SKEW but treats the data as the whole population instead of a sample. | Expert | 2013 | No |
SLOPE | Tells you how much y changes for each one-unit rise in x, like extra sales per extra ad dollar. | Intermediate | 2007 or earlier | No |
SMALL | Pulls the 2nd lowest price, the bottom 3 scores, and so on. The counterpart of LARGE. | Beginner | 2007 or earlier | No |
STANDARDIZE | Puts values from different scales on the same footing so you can compare, for example two exam scores. | Intermediate | 2007 or earlier | No |
STDEV.P | Measures spread when your data is the entire group, not a sample. | Intermediate | 2010 | No |
STDEV.S | The default choice for spread when your data is a sample of a larger group. Divides by n-1. | Beginner | 2010 | No |
STDEVA | Like STDEV.S but TRUE counts as 1, FALSE and text in ranges as 0. Rarely used. | Expert | 2007 or earlier | No |
STDEVPA | Population version of STDEVA; TRUE is 1, FALSE and text in ranges are 0. | Expert | 2007 or earlier | No |
STEYX | Gives the typical size of prediction errors from a straight-line fit, in the units of y. | Expert | 2007 or earlier | No |
T.DIST | Converts a t statistic into a left-tail probability for small samples. Basis for many t-tests. | Expert | 2010 | No |
T.DIST.2T | Turns a t statistic into a two-sided p-value, the number you compare with 0.05. | Expert | 2010 | No |
T.DIST.RT | Gives the chance of seeing a t value this large or larger, for one-sided tests. | Expert | 2010 | No |
T.INV | Finds a one-sided t critical value, such as 2.23 for 97.5 percent with 10 degrees of freedom. | Expert | 2010 | No |
T.INV.2T | The critical value for confidence intervals: give it 0.05 and your degrees of freedom to get the multiplier for a 95 percent interval. | Expert | 2010 | No |
T.TEST | Compares the means of two samples, for instance before/after results or two sales regions, and returns the p-value directly. | Advanced | 2010 | No |
TREND | Extends a linear trend to new points, such as forecasting next quarter from past sales. | Intermediate | 2007 or earlier | Yes |
TRIMMEAN | An average that ignores outliers at both ends, like judged scores with the highest and lowest removed. | Intermediate | 2007 or earlier | No |
VAR.P | The average squared distance from the mean when the data is the whole population. | Intermediate | 2010 | No |
VAR.S | Variance when the data is a sample; divides by n-1. The usual choice for analysis. | Intermediate | 2010 | No |
VARA | Like VAR.S but TRUE is 1 and FALSE or text in ranges is 0. | Expert | 2007 or earlier | No |
VARPA | Population version of VARA; TRUE is 1, FALSE and text in ranges are 0. | Expert | 2007 or earlier | No |
WEIBULL.DIST | Used in reliability work to model time until failure, like how long a part lasts. | Expert | 2010 | No |
Z.TEST | Tests whether a sample mean is above a hypothesised value. Returns the right-tail probability. | Advanced | 2010 | No |
Other categories
- Lookup and reference functions
43 functions
- Math and trig functions
80 functions
- Logical functions
19 functions
- Text functions
48 functions
- Date and time functions
25 functions
- Financial functions
56 functions
- Information functions
21 functions
- Database functions
12 functions
- Engineering functions
54 functions
- Web functions
3 functions
- Compatibility functions
41 functions
- Cube functions
7 functions
- Add-in and automation functions
5 functions