Practice free

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

All statistical functions

FunctionWhat it doesLevelSinceSpills?
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