Excel Statistical Functions

Statistical functions in Excel analyse numbers and return summaries like averages, counts, rankings, and measures of variation. These functions help you understand data at a glance — whether you are reviewing exam scores, sales figures, employee attendance, or product measurements.

AVERAGE — Find the Mean

AVERAGE adds all values in a range and divides by the count of those values.

=AVERAGE(A1:A6)

A1: 60
A2: 70
A3: 80
A4: 90
A5: 50
A6: 100

Sum = 450 ÷ 6 values = 75

Result: 75

AVERAGE ignores blank cells. It does not treat a blank as zero. If you need blank cells counted as zero, use SUM divided by COUNT instead.

MEDIAN — Find the Middle Value

MEDIAN finds the middle value when numbers are arranged in order. It is useful when extreme values (very high or very low) would skew the average.

=MEDIAN(A1:A5)

Values: 10, 20, 30, 200, 1000

AVERAGE = 252  ← pulled high by 1000
MEDIAN  = 30   ← the true middle value

MEDIAN gives a better picture of "typical" when your data has outliers.

MODE — Find the Most Frequent Value

=MODE(A1:A8)

Values: 5, 3, 7, 3, 8, 3, 9, 2

Result: 3  (appears most often — three times)

In newer Excel versions, use MODE.SNGL for one most-common value or MODE.MULT to return all values that tie for most frequent.

COUNT and COUNTA — Count Cells

COUNT Diagram

A1: 100       ← number ✓
A2: "Apple"   ← text — ignored by COUNT, counted by COUNTA
A3: (blank)   ← empty — ignored by both, counted by COUNTBLANK
A4: 250       ← number ✓
A5: 300       ← number ✓

=COUNT(A1:A5)     → 3
=COUNTA(A1:A5)    → 4
=COUNTBLANK(A1:A5) → 1

MIN and MAX — Smallest and Largest Values

=MIN(A1:A6)  → Returns the lowest number in the range
=MAX(A1:A6)  → Returns the highest number in the range

Values: 45, 92, 17, 68, 33, 81

=MIN → 17
=MAX → 92

MINIFS and MAXIFS — Conditional Min and Max

=MINIFS(min_range, criteria_range, criteria)
=MAXIFS(max_range, criteria_range, criteria)

Find the lowest sales in Region "North":
=MINIFS(B2:B10, A2:A10, "North")

Find the highest score for students who passed:
=MAXIFS(C2:C20, D2:D20, "Pass")

LARGE and SMALL — Nth Largest or Smallest

=LARGE(A1:A10, 1)   → Largest value (same as MAX)
=LARGE(A1:A10, 2)   → 2nd largest value
=LARGE(A1:A10, 3)   → 3rd largest value

=SMALL(A1:A10, 1)   → Smallest value (same as MIN)
=SMALL(A1:A10, 2)   → 2nd smallest value

LARGE Diagram

Scores: 88, 95, 72, 91, 85, 63, 99, 78

=LARGE(range, 1) → 99  (1st)
=LARGE(range, 2) → 95  (2nd)
=LARGE(range, 3) → 91  (3rd)

RANK — Find a Value's Position in a List

=RANK(number, ref, [order])

number → the cell value to rank
ref    → the full range to rank within
order  → 0 for descending (highest = rank 1), 1 for ascending (lowest = rank 1)

=RANK(B2, $B$2:$B$10, 0)

Score 95 in a list where 99 is highest → RANK = 2

In newer Excel versions, use RANK.EQ (ties get the same rank) or RANK.AVG (ties get the average rank).

STDEV — Standard Deviation

Standard deviation measures how spread out numbers are from the average. A small standard deviation means values cluster tightly around the mean. A large one means values are spread wide.

Class A scores: 78, 79, 80, 81, 82   → STDEV ≈ 1.6  (all close together)
Class B scores: 40, 60, 80, 100, 70  → STDEV ≈ 22.4 (widely spread)

=STDEV(A1:A5)     → Sample standard deviation
=STDEVP(A1:A5)    → Population standard deviation (use when data IS the whole population)

VAR — Variance

=VAR(A1:A10)    → Sample variance
=VARP(A1:A10)   → Population variance

Variance is the square of the standard deviation. It measures the same spread, but in squared units. Most people prefer STDEV because it stays in the original unit (for example, if scores are in points, STDEV is also in points).

PERCENTILE and QUARTILE — Distribution Positions

=PERCENTILE(A1:A20, 0.9)   → Value at the 90th percentile
=PERCENTILE(A1:A20, 0.5)   → Value at the 50th percentile (same as MEDIAN)

=QUARTILE(A1:A20, 1)       → 25th percentile (Q1)
=QUARTILE(A1:A20, 2)       → 50th percentile (Q2 = Median)
=QUARTILE(A1:A20, 3)       → 75th percentile (Q3)

CORREL — Correlation Between Two Variables

=CORREL(A1:A10, B1:B10)

Returns a value between -1 and 1:
  1  → Perfect positive relationship (as A rises, B rises equally)
  0  → No relationship
 -1  → Perfect negative relationship (as A rises, B falls equally)

Statistical Functions Summary

Statistical functions work well when combined with SUMIF and COUNTIF for conditional analysis. For example, finding the average score only for students from a specific class, or the maximum sales figure for a specific region. These combinations form the backbone of most Excel data analysis tasks.