Excel Statistical Functions: Practical Reference and Examples

Excel's statistical functions cover everyday summaries, conditional counts, ranking, variation, probability distributions, and hypothesis tests. Start with the question you need to answer, then choose the function whose inputs match your data.

Examples below use commas as argument separators. Some regional Excel settings use semicolons instead.

Count cells and calculate conditional averages

• =COUNT(B2:B100) counts numeric cells.
• =COUNTA(B2:B100) counts non-empty cells.
• =COUNTBLANK(B2:B100) counts blank cells.
• =COUNTIF(C2:C100,"Complete") counts rows meeting one condition.
• =COUNTIFS(C2:C100,"Complete",D2:D100,">=100") counts rows meeting multiple conditions.
• =AVERAGE(B2:B100) returns the arithmetic mean.
• =AVERAGEIF(C2:C100,"East",B2:B100) averages values that meet one condition.
• =AVERAGEIFS(B2:B100,C2:C100,"East",D2:D100,">0") applies multiple conditions.

Find position and center

• MIN and MAX return the smallest and largest numeric values.
• SMALL(array,k) and LARGE(array,k) return the k-th smallest or largest value.
• MEDIAN returns the middle value after sorting.
• MODE.SNGL returns the most frequent value; MODE.MULT can return multiple modes.
• RANK.EQ(number,ref,[order]) gives tied values the same rank. RANK.AVG assigns tied values their average rank.

Example: =RANK.EQ(B2,$B$2:$B$20,0) ranks B2 from largest to smallest. Lock the reference range before filling the formula down.

Measure spread and shape

• STDEV.S estimates standard deviation from a sample.
• STDEV.P calculates standard deviation for the full population.
• VAR.S and VAR.P are the corresponding variance functions.
• AVEDEV returns average absolute deviation from the mean.
• DEVSQ returns the sum of squared deviations.
• SKEW measures sample-distribution asymmetry; SKEW.P is for a population.
• KURT returns excess kurtosis.
• TRIMMEAN(array,percent) calculates a mean after excluding a percentage of values from both tails.

Choose sample or population deliberately. A sample is a subset used to infer a larger group; a population is the complete group being analyzed.

Percentiles, quartiles, and frequency tables

• PERCENTILE.INC(array,k) returns an inclusive percentile for k from 0 through 1.
• PERCENTILE.EXC(array,k) uses the exclusive definition.
• QUARTILE.INC and QUARTILE.EXC return quartiles using the corresponding conventions.
• PERCENTRANK.INC and PERCENTRANK.EXC express a value's relative rank.
• FREQUENCY(data_array,bins_array) returns counts for intervals. In current dynamic-array Excel, results spill into adjacent cells; older Excel may require an array formula.

Probability distributions

Modern function names identify the distribution and operation explicitly:

• BINOM.DIST and BINOM.INV for binomial models.
• NORM.DIST, NORM.INV, NORM.S.DIST, and NORM.S.INV for normal distributions.
• POISSON.DIST for counts of events under a Poisson model.
• EXPON.DIST for an exponential distribution.
• LOGNORM.DIST and LOGNORM.INV for lognormal models.
• GAMMA.DIST, GAMMA.INV, and WEIBULL.DIST for other continuous distributions.
• CHISQ.DIST, F.DIST, and T.DIST for chi-square, F, and t distributions.

Tests and relationships

• T.TEST(array1,array2,tails,type) returns a probability associated with a t-test.
• F.TEST(array1,array2) returns the result of an F-test for variances.
• CHISQ.TEST(actual_range,expected_range) performs a chi-square independence test.
• Z.TEST(array,x,[sigma]) returns a one-tailed probability value for a z-test.
• CORREL(array1,array2) returns a correlation coefficient; it does not by itself prove causation.

Statistical tests require appropriate assumptions and correctly structured data. A formula can calculate a result without establishing that the selected test is valid.

Old names in legacy workbooks

Excel retains many old names for compatibility, but current workbooks should prefer the clearer replacements:

For focused examples, see the guides to COUNTIF conditional counting and MAX and MIN in Excel. Check each formula against the Excel version used by the workbook.