Averages, spread, ranks, forecasting and probability distributions — everything for summarising and testing data. This page lists all 147 of them; each links to a full lesson with an example you can copy and a practice file.
AVEDEVAVEDEV is the average of absolute deviations — an easier-to-explain measure of spread than standard deviation (“on average…AVERAGEAVERAGE returns the arithmetic mean: the sum divided by the count of numbers. Empty cells and text are…AVERAGEAAVERAGEA treats text as 0 and TRUE as 1. Use it when “absent” should drag the average down…AVERAGEIFAVERAGEIF averages values that meet one condition — often used to ignore zeros.AVERAGEIFSAVERAGEIFS averages values where all conditions are true.BETA.DISTBETA.DIST gives the beta distribution, used for proportions and task-duration estimates (PERT). With A and B you can…BETA.INVBETA.INV is the reverse of BETA.DIST: for a probability it returns the value, e.g. the duration you will…BETADISTBETADIST returns the cumulative beta probability. It is a compatibility function: Excel keeps it so workbooks from Excel…BETAINVBETAINV returns the value at a beta probability. It is a compatibility function: Excel keeps it so workbooks…BINOM.DISTBINOM.DIST handles yes/no events with a fixed chance — defects in a batch, customers who convert.BINOM.DIST.RANGEBINOM.DIST.RANGE gives the chance of getting between s and s2 successes in n trials, e.g. 3 to 5…BINOM.INVBINOM.INV returns the smallest number of successes whose cumulative probability reaches the target, e.g. how many defects is…BINOMDISTBINOMDIST returns the binomial probability. It is a compatibility function: Excel keeps it so workbooks from Excel 2007…CHIDISTCHIDIST returns the right-tailed chi-square probability. It is a compatibility function: Excel keeps it so workbooks from Excel…CHIINVCHIINV returns the chi-square critical value. It is a compatibility function: Excel keeps it so workbooks from Excel…CHISQ.DISTCHISQ.DIST gives the left-tailed chi-square probability; most tests need the right tail, CHISQ.DIST.RT.CHISQ.DIST.RTCHISQ.DIST.RT turns a chi-square statistic into a p-value: the chance of a result at least this extreme if…CHISQ.INVCHISQ.INV is the inverse of CHISQ.DIST (left tail).CHISQ.INV.RTCHISQ.INV.RT gives the critical value for a chi-square test: if your statistic is bigger, the result is significant.CHISQ.TESTCHISQ.TEST compares observed counts with expected counts and returns a p-value.CHITESTCHITEST returns the p-value comparing observed and expected counts. It is a compatibility function: Excel keeps it so…CONFIDENCECONFIDENCE returns the normal margin of error for a mean. It is a compatibility function: Excel keeps it…CONFIDENCE.NORMCONFIDENCE.NORM gives the ± margin around a sample mean for a chosen confidence level.CONFIDENCE.TCONFIDENCE.T is the margin of error using the t-distribution, correct for small samples (under ~30) where CONFIDENCE.NORM is…CORRELCORREL returns a number between −1 and 1: near 1 means both rise together, near −1 means one…COUNTCOUNT counts cells containing numbers (including dates, which are numbers in Excel). Text and blanks are not counted.COUNTACOUNTA counts every cell that is not empty — numbers, text, errors and even formulas that return an…COUNTBLANKCOUNTBLANK counts empty cells — and cells with formulas returning "" — a quick data-completeness check.COUNTIFCOUNTIF counts cells that meet one condition. It is also the simplest duplicate checker.COUNTIFSCOUNTIFS counts rows where every condition is true.COVARCOVAR returns the population covariance. It is a compatibility function: Excel keeps it so workbooks from Excel 2007…COVARIANCE.PCOVARIANCE.P measures how two complete data sets move together, dividing by n (COVARIANCE.S divides by n-1).COVARIANCE.SCOVARIANCE.S measures whether two variables rise and fall together. Its size depends on units, so CORREL is easier…CRITBINOMCRITBINOM returns the smallest count whose cumulative binomial probability reaches alpha. It is a compatibility function: Excel keeps…DEVSQDEVSQ adds up how far each value is from the mean, squared. It is the raw ingredient of…EXPON.DISTEXPON.DIST gives the chance the wait until the next event is under a given time.EXPONDISTEXPONDIST returns the exponential probability. It is a compatibility function: Excel keeps it so workbooks from Excel 2007…F.DISTF.DIST gives the left-tailed F probability; for ANOVA and variance tests use F.DIST.RT.F.DIST.RTF.DIST.RT turns an F statistic (from ANOVA or a variance ratio) into a p-value.F.INVF.INV returns the F value for a left-tail probability.F.INV.RTF.INV.RT gives the critical F value for ANOVA at a chosen significance level.F.TESTF.TEST checks whether two samples have different variances — for instance, before choosing T.TEST type 2 or 3.FDISTFDIST returns the right-tailed F probability. It is a compatibility function: Excel keeps it so workbooks from Excel…FINVFINV returns the F critical value. It is a compatibility function: Excel keeps it so workbooks from Excel…FISHERFISHER transforms a correlation coefficient so it is roughly normal, which lets you build confidence intervals and compare…FISHERINVFISHERINV reverses FISHER, turning z back into a correlation.FORECASTFORECAST predicts a value on a straight-line trend. It is a compatibility function: Excel keeps it so workbooks…FORECAST.ETSFORECAST.ETS uses exponential smoothing and detects seasonal patterns — the engine behind Excel’s Forecast Sheet.FORECAST.ETS.CONFINTFORECAST.ETS.CONFINT returns the ± interval around a FORECAST.ETS value. A forecast of 1,200 ± 400 tells a very…FORECAST.ETS.SEASONALITYFORECAST.ETS.SEASONALITY reports the season length Excel detected — 12 for a yearly cycle in monthly data, 7 for…FORECAST.ETS.STATFORECAST.ETS.STAT returns a statistic about the ETS model behind FORECAST.ETS, such as the smoothing parameters (alpha, beta, gamma)…FORECAST.LINEARFORECAST.LINEAR predicts a y-value for a given x using a straight-line trend fitted to past data.FREQUENCYFREQUENCY counts how many values fall into each bin — the data behind a histogram.FTESTFTEST returns the p-value comparing two variances. It is a compatibility function: Excel keeps it so workbooks from…GAMMAGAMMA extends factorials to non-whole numbers: GAMMA(n) = (n-1)! for whole n.GAMMA.DISTGAMMA.DIST models waiting times and amounts that are always positive and skewed, like claim sizes or repair times.GAMMA.INVGAMMA.INV returns the value at a given gamma probability.GAMMADISTGAMMADIST returns the gamma probability. It is a compatibility function: Excel keeps it so workbooks from Excel 2007…GAMMAINVGAMMAINV returns the value at a gamma probability. It is a compatibility function: Excel keeps it so workbooks…GAMMALNGAMMALN returns ln(GAMMA(x)); useful when GAMMA itself would overflow for large x.GAMMALN.PRECISEGAMMALN.PRECISE is the 2010 version of GAMMALN with the same result and improved accuracy.GAUSSGAUSS returns the probability that a standard normal value falls between the mean and z standard deviations: NORM.S.DIST(z)…GEOMEANGEOMEAN multiplies values and takes the n-th root. For growth rates it gives the honest average: +50% followed…GROWTHGROWTH is TREND for things that grow by a percentage — sign-ups, compounding balances — rather than by…HARMEANHARMEAN averages rates properly. Drive 60 km/h one way and 40 km/h back, and your average speed is…HYPGEOM.DISTHYPGEOM.DIST gives the chance of k successes when you draw a sample without putting items back, e.g. defective…HYPGEOMDISTHYPGEOMDIST returns the probability of k successes when sampling without replacement. It is a compatibility function: Excel keeps…INTERCEPTINTERCEPT gives the y-value of the trend line when x is 0. With SLOPE you have the full…KURTKURT measures whether data has more extreme values than a normal distribution (positive) or fewer (negative).LARGELARGE returns the k-th largest value; SMALL returns the k-th smallest. Together they build top-N lists in any…LINESTLINEST fits a straight line (or a multiple regression) and returns slope, intercept and, optionally, R², standard errors…LOGESTLOGEST fits y = b·mˣ and returns m (the growth factor) and b. m of 1.05 means 5%…LOGINVLOGINV returns the value at a lognormal probability. It is a compatibility function: Excel keeps it so workbooks…LOGNORM.DISTLOGNORM.DIST models values whose logarithm is normal: incomes, house prices, stock prices, file sizes.LOGNORM.INVLOGNORM.INV returns the value at a lognormal probability.LOGNORMDISTLOGNORMDIST returns the cumulative lognormal probability. It is a compatibility function: Excel keeps it so workbooks from Excel…MAXMAX returns the largest number. MIN is its mirror. Both ignore text and blanks.MAXAMAXA is MAX that counts TRUE as 1 and text as 0.MAXIFSMAXIFS returns the maximum of values where all conditions are met; MINIFS returns the minimum.MEDIANMEDIAN returns the middle value — more representative than AVERAGE when a few huge values distort the mean…MINMIN returns the smallest number in a set of values, ignoring text and blanks.MINAMINA is MIN that also counts text as 0 and TRUE as 1, FALSE as 0.MINIFSMINIFS returns the minimum of values where all conditions are true.MODEMODE returns the most frequent number. It is a compatibility function: Excel keeps it so workbooks from Excel…MODE.MULTMODE.MULT returns every value tied for most frequent, where MODE.SNGL returns only the first.MODE.SNGLMODE.SNGL returns the value that appears most often in a set of numbers.NEGBINOM.DISTNEGBINOM.DIST gives the chance of a number of failures before reaching a set number of successes, e.g. calls…NEGBINOMDISTNEGBINOMDIST returns the probability of a number of failures before the k-th success. It is a compatibility function:…NORM.DISTNORM.DIST gives the probability (cumulative) or the curve height (density) of a normal distribution at a value.NORM.INVNORM.INV is the reverse of NORM.DIST: give it a probability and it returns the value. Great for safety…NORM.S.DISTNORM.S.DIST is NORM.DIST with mean 0 and standard deviation 1 — perfect for z-scores.NORM.S.INVNORM.S.INV returns the z-score for a cumulative probability — where the famous 1.96 comes from.NORMDISTNORMDIST gives the normal distribution for a mean and SD. It is a compatibility function: Excel keeps it…NORMINVNORMINV returns the value at a normal probability. It is a compatibility function: Excel keeps it so workbooks…NORMSDISTNORMSDIST gives the cumulative standard normal probability for z. It is a compatibility function: Excel keeps it so…NORMSINVNORMSINV returns z for a probability. It is a compatibility function: Excel keeps it so workbooks from Excel…PEARSONPEARSON returns the same value as CORREL: −1 to 1.PERCENTILEPERCENTILE returns the k-th percentile (inclusive). It is a compatibility function: Excel keeps it so workbooks from Excel…PERCENTILE.EXCPERCENTILE.EXC returns the k-th percentile but treats 0 and 1 as out of range. On small samples its…PERCENTILE.INCPERCENTILE.INC returns the value below which a given percentage of data falls — for salary bands, delivery-time targets…PERCENTRANKPERCENTRANK returns the rank of a value as a percentage of the data. It is a compatibility function:…PERCENTRANK.EXCPERCENTRANK.EXC works like PERCENTRANK.INC, but never returns exactly 0 or 1 — matching the EXC family of percentile…PERCENTRANK.INCPERCENTRANK.INC tells you what share of the data is below a value — “this store beats 82% of…PHIPHI returns the height of the standard normal curve at z, the same as NORM.S.DIST(z, FALSE).POISSONPOISSON returns the Poisson probability. It is a compatibility function: Excel keeps it so workbooks from Excel 2007…POISSON.DISTPOISSON.DIST models counts per interval — calls per minute, breakdowns per month.PROBPROB adds the probabilities of outcomes falling between two limits, from a simple value/probability table.QUARTILEQUARTILE returns the quartile of a data set. It is a compatibility function: Excel keeps it so workbooks…QUARTILE.EXCQUARTILE.EXC matches the method many statistics textbooks and tools use; results differ slightly from QUARTILE.INC on small data.QUARTILE.INCQUARTILE.INC returns the minimum, the 25th, 50th or 75th percentile, or the maximum. It is the fastest way…RANKRANK still works for compatibility and behaves exactly like RANK.EQ. New workbooks should use RANK.EQ or RANK.AVG.RANK.AVGRANK.AVG works like RANK.EQ, but tied values get the average rank (2.5 and 2.5 instead of 2 and…RANK.EQRANK.EQ returns the position of a number in a list. Ties share the same rank.RSQRSQ returns R², between 0 and 1. Near 1 means a straight line explains the data well; near…SKEWSKEW is positive when a long tail stretches to the right — typical for salaries and order values,…SKEW.PSKEW.P measures lopsidedness of a complete data set; SKEW is the sample version.SLOPESLOPE returns the slope of the best-fit straight line — “sales grow by about ₹42,000 a month”.SMALLSMALL returns the k-th smallest number — bottom-N lists and the earliest dates.STANDARDIZESTANDARDIZE converts a value to a z-score: how many standard deviations it sits from the mean. Z-scores let…STDEVSTDEV estimates the standard deviation from a sample. It is a compatibility function: Excel keeps it so workbooks…STDEV.PSTDEV.P measures spread when your data is the entire group, not a sample. The P version divides by…STDEV.SSTDEV.S measures how far values typically sit from the average (sample standard deviation). A small value means consistent;…STDEVASTDEVA is STDEV.S that also counts text as 0 and TRUE as 1; STDEV.S ignores them.STDEVPSTDEVP gives the standard deviation of a whole population. It is a compatibility function: Excel keeps it so…STDEVPASTDEVPA is the population version of STDEVA.STEYXSTEYX returns the standard error of predicted y values — roughly how far actual points sit from the…T.DISTT.DIST gives probabilities for the t-distribution — used instead of the normal curve when samples are small.T.DIST.2TT.DIST.2T turns a t statistic into a two-tailed p-value, the usual one for 'is there any difference?'.T.DIST.RTT.DIST.RT gives the right-tailed t probability, for 'is it greater?' tests.T.INVT.INV returns the t value for a left-tail probability; T.INV.2T is the usual critical value.T.INV.2TT.INV.2T returns the t value for a two-tailed test — the multiplier for a confidence interval on a…T.TESTT.TEST returns a p-value for whether two groups have different means — did the new process really cut…TDISTTDIST gives a one- or two-tailed t probability. It is a compatibility function: Excel keeps it so workbooks…TINVTINV returns the two-tailed t critical value. It is a compatibility function: Excel keeps it so workbooks from…TRENDTREND fits a straight line and returns values on it, for existing points or new ones. In Excel…TRIMMEANTRIMMEAN drops a share of the highest and lowest values and averages the rest, much like judges scoring…TTESTTTEST returns the p-value of a t-test between two samples. It is a compatibility function: Excel keeps it…VARVAR estimates variance from a sample. It is a compatibility function: Excel keeps it so workbooks from Excel…VAR.PVAR.P is the population version of VAR.S: it divides by n, not n−1. Use it when the data…VAR.SVAR.S is the square of STDEV.S. You rarely report it directly, but it turns up in forecasting, portfolio…VARAVARA is VAR.S that counts text as 0 and TRUE as 1.VARPVARP gives the variance of a whole population. It is a compatibility function: Excel keeps it so workbooks…VARPAVARPA is the population version of VARA.WEIBULLWEIBULL returns the Weibull probability. It is a compatibility function: Excel keeps it so workbooks from Excel 2007…WEIBULL.DISTWEIBULL.DIST models time to failure: the chance a machine part fails within x hours.Z.TESTZ.TEST returns the one-tailed p-value for whether a sample mean is higher than a claimed value.ZTESTZTEST returns the one-tailed p-value of a z-test. It is a compatibility function: Excel keeps it so workbooks…