Excel Statistical Functions: A Practical Guide

Excel statistical functions turn a column of observations into answers: What is typical? How widely do values vary? Which result ranks second? Does one group behave differently from another? The difficult part is rarely typing the formula. It is choosing a measure that matches the question.

This guide uses one small service-desk dataset to explain the most useful descriptive functions, the decisions behind them, and the data issues that can quietly change a result.

Choose the function by the question

These are only a practical starting set. Microsoft’s statistical functions reference also lists functions for distributions, correlation, regression, forecasting, confidence intervals, and hypothesis tests.

Use one dataset to compare the results

Enter this example in A1:D9. The resolution times in C2:C9 are deliberately uneven so the measures do not all tell the same story.

The example dataset in A1:D9 is used throughout the article to demonstrate Excel statistical functions.

The formulas below assume the headers and values occupy exactly those cells. If you add rows later, convert the source range to an Excel Table so formulas can use expanding structured references.

Measure the center with AVERAGE, MEDIAN, and MODE

=AVERAGE(C2:C9)
=MEDIAN(C2:C9)
=MODE.SNGL(C2:C9)

This worksheet uses AVERAGE, MEDIAN, and MODE.SNGL to summarize the center of the resolution time data.

The results are 30, 26, and 22. Each is correct, but each answers a different question:

• AVERAGE uses every numeric value, so the 66-minute ticket pulls the mean upward.
• MEDIAN sorts the eight values and averages the two in the middle, 25 and 27. It is less sensitive to the unusually long ticket.
• MODE.SNGL returns 22 because that is the only value that appears more than once.

Use the mean when every magnitude should influence the result. Use the median when you want a typical midpoint that is less affected by extreme observations. Use the mode for a genuinely useful most-common value, such as a frequently selected package size; it may add little when nearly every value is unique.

If several values tie for the highest frequency, MODE.MULT can return all modes. If no value repeats, MODE.SNGL returns #N/A. Microsoft documents that behavior in the MODE.SNGL reference.

Measure spread with STDEV.S or STDEV.P

Standard deviation describes how widely values are dispersed around their mean. Use the function that matches what the rows represent:

=STDEV.S(C2:C9)
=STDEV.P(C2:C9)

The worksheet compares STDEV.S and STDEV.P for the same resolution time data to show the difference between sample and population standard deviation.

For the sample data, STDEV.S returns approximately 15.14 minutes and STDEV.P returns approximately 14.16 minutes.

• Use STDEV.S when the observed tickets are a sample used to describe a larger population of tickets. It uses the n-1 method.
• Use STDEV.P when the range contains the complete population you intend to describe. It uses the n method.

The choice depends on the data’s role, not on which answer looks better. Microsoft’s documentation distinguishes STDEV.S for a sample from STDEV.P for an entire population. For large datasets the two results get closer, but the underlying choice remains the same.

Standard deviation has the same unit as the source data, so these results are in minutes. Variance functions VAR.S and VAR.P follow the same sample-versus-population decision, but their results are in squared units.

Find extremes and ranked values

MIN and MAX return the endpoints. SMALL and LARGE accept a second argument, k, that identifies the requested rank.

This example uses MIN, MAX, SMALL, and LARGE to return the lowest, highest, second-smallest, and second-largest resolution times.

The simple range is 66-18, or 48 minutes, but it depends entirely on two observations. Ranked values give more context without requiring you to sort the source rows. Repeated values still occupy separate rank positions: the second-smallest value here is 22, even though another 22 follows it.

Describe position with percentiles and quartiles

Percentiles divide ordered data by relative position. Quartiles are specific percentiles: the first quartile corresponds to 25%, the median to 50%, and the third quartile to 75%.

=QUARTILE.INC(C2:C9,1)
=QUARTILE.INC(C2:C9,3)
=PERCENTILE.INC(C2:C9,0.9)

This example uses QUARTILE.INC and PERCENTILE.INC to calculate Q1, Q3, and the 90th percentile for resolution times.

These formulas return 22 for the first quartile, 29.5 for the third quartile, and 41.5 for the inclusive 90th percentile. The interquartile range is therefore 29.5-22, or 7.5 minutes.

The .INC functions include the 0th and 100th percentile endpoints. Excel also provides PERCENTILE.EXC and QUARTILE.EXC, which use an exclusive definition and can produce different results, especially with small datasets. Choose a convention required by your method or organization and label it; do not switch variants merely to obtain a preferred boundary. See Microsoft’s QUARTILE.INC documentation for the supported quartile arguments.

Calculate conditional averages

AVERAGEIF applies one condition. To average resolution time only for the South team, use:

=AVERAGEIF(B2:B9,"South",C2:C9)

This worksheet uses AVERAGEIF and AVERAGEIFS to calculate conditional averages for South team tickets and for South team tickets with satisfaction scores of at least 3.

The result is approximately 38.33 minutes. The first range is tested against the criterion, and the last range supplies the numbers to average.

AVERAGEIFS handles multiple conditions. This formula averages South-team tickets whose satisfaction score is at least 3:

=AVERAGEIFS(C2:C9,B2:B9,"South",D2:D9,">=3")

The result is 24.5 minutes. In AVERAGEIFS, the average range comes first, followed by pairs of criteria ranges and criteria. Keep every range the same size and align corresponding records on the same rows.

Handle blanks, text, zeros, and errors deliberately

For cell references, AVERAGE, MEDIAN, MODE.SNGL, STDEV.S, and STDEV.P ignore empty cells, text, and logical values, but include numeric zeros. Error values in a referenced range can make the formula return an error.

This distinction matters. A zero can mean a measured result of zero, while a blank may mean “not observed.” Replacing missing observations with zeros changes both the numerator and the count behind an average.

1. Inspect the source cells. Confirm that measurements are numeric values rather than numbers stored as text.
2. Define missing data. Decide whether a blank is genuinely missing, not applicable, or an error in collection.
3. Keep legitimate zeros. Exclude them only when the business rule says zero represents missing or invalid data.
4. Investigate errors. Correct upstream errors instead of wrapping every result in IFERROR and hiding the cause.

Microsoft’s AVERAGE documentation confirms that zeros in references are included while text, logical values, and empty cells are ignored. Functions ending in A, such as AVERAGEA, use different coercion rules and should be chosen intentionally.

Average only visible rows

A regular AVERAGE calculation still reads rows hidden by a filter. To average only visible values in a vertical list, use:

=SUBTOTAL(101,C2:C9)

Function number 101 tells SUBTOTAL to calculate an average while excluding both filtered-out rows and manually hidden rows. Function number 1 also excludes filtered-out rows, but it includes manually hidden rows. The SUBTOTAL reference documents the full set of function numbers and notes that filtered-out rows are always excluded.

Common mistakes and fixes

When the basic functions are not enough

Descriptive functions summarize the values you already have. A different question may require a different family of statistical tools:

• CORREL, SLOPE, and LINEST examine relationships between paired variables.
• NORM.DIST, BINOM.DIST, and related functions calculate probabilities under specified distributions.
• T.TEST, CHISQ.TEST, and F.TEST return probabilities for particular statistical tests.
• CONFIDENCE.T and CONFIDENCE.NORM calculate confidence-interval margins under different assumptions.

Those functions require more than valid syntax. Independence, distribution shape, sample design, pairing, tails, and other assumptions affect whether a method is appropriate. Excel can return a number even when the selected model does not fit the study.

Frequently asked questions

Build a summary that can be trusted

A useful statistical summary does more than display formulas. It states what each row represents, distinguishes missing values from real zeros, chooses sample or population logic explicitly, and pairs the mean with measures that reveal distribution and spread.

Start with AVERAGE, MEDIAN, and STDEV.S or STDEV.P. Add extremes, ranks, quartiles, or conditional averages only when they answer a defined question. That keeps the worksheet compact while making its conclusions easier to explain and audit.