| Statistic | Excel Function | Comment |
|---|---|---|
| Arithmetic Mean | =AVERAGE(range) | Sum / Count |
| Median | =MEDIAN(range) | Middle value |
| Mode (single) | =MODE.SNGL(range) | Most frequent value |
| Mode (multiple) | =MODE.MULT(range) | Array formula — returns all modes |
| Geometric Mean | =GEOMEAN(range) | For positive values only; growth rates |
| Harmonic Mean | =HARMEAN(range) | Reciprocal of mean of reciprocals; for speeds/rates |
| Trimmed Mean | =TRIMMEAN(range, percent) | Drops extremes; e.g. percent = 0.1 drops 10 % top + bottom |
| Weighted Mean | =SUMPRODUCT(values, weights)/SUM(weights) | No single built-in; use SUMPRODUCT |
Marks in A2:A11 = 45, 52, 38, 71, 64, 49, 58, 33, 67, 72.
=AVERAGE(A2:A11) → 54.9; =MEDIAN(A2:A11) → 55.0; =MODE.SNGL(A2:A11) → #N/A (no repeats).
Annual growth rates in B2:B4 = 1.10, 1.20, 1.30 (i.e. 10 %, 20 %, 30 %). =GEOMEAN(B2:B4) = 1.197 → average growth ≈ 19.7 %.
Average speed for equal distances at 40 and 60 km/h in C2:C3: =HARMEAN(C2:C3) = 48 km/h.
| Statistic | Excel Function | Comment |
|---|---|---|
| Range | =MAX(range) - MIN(range) | — |
| Variance (sample, ÷ n−1) | =VAR.S(range) or =VAR(range) | Use for samples |
| Variance (population, ÷ n) | =VAR.P(range) or =VARP(range) | Use for the whole population |
| SD (sample) | =STDEV.S(range) or =STDEV(range) | √VAR.S |
| SD (population) | =STDEV.P(range) or =STDEVP(range) | √VAR.P |
| Quartile / IQR | =QUARTILE.INC(range, q) | q = 1 or 3; IQR = Q3 − Q1 |
| Average Absolute Deviation | =AVEDEV(range) | Mean absolute deviation from mean |
| Coefficient of Variation (CV) | =STDEV.S(range)/AVERAGE(range)*100 | Unit-free relative dispersion |
For the data of Example 1 above: =VAR.S(A2:A11) ≈ 188.54; =STDEV.S(A2:A11) ≈ 13.73; CV ≈ 13.73/54.9 × 100 = 25.0 %.
=QUARTILE.INC(A2:A11, 1) → Q1 = 46.0; =QUARTILE.INC(A2:A11, 3) → Q3 = 66.25. IQR = 20.25.
| Function | Purpose |
|---|---|
=RANK.EQ(number, ref, [order]) | Standard ranking (ties get equal rank, next rank skipped) |
=RANK.AVG(number, ref, [order]) | Ties get the average rank — used for Spearman correlation |
=PERCENTRANK.INC(array, x) | Percentile rank of x (inclusive, 0–1) |
=PERCENTRANK.EXC(array, x) | Percentile rank (exclusive) |
=PERCENTILE.INC(array, k) | kth percentile (k between 0 and 1) |
=LARGE(array, k) / =SMALL(array, k) | kth largest / smallest value |
Note: The third argument of RANK is order: 0 (or omitted) = descending (largest = rank 1); 1 = ascending.
For marks 45, 52, 38, 71, 64 (A2:A6):
=RANK.EQ(A2, $A$2:$A$6) in B2 returns rank of each cell. Result: 4, 3, 5, 1, 2.
=PERCENTRANK.INC(A2:A11, 60) tells what percentile a score of 60 corresponds to within the data. =PERCENTILE.INC(A2:A11, 0.9) returns the 90th percentile.
| Function | Returns |
|---|---|
=SKEW(range) | Sample skewness (Fisher–Pearson) |
=SKEW.P(range) | Population skewness |
=KURT(range) | Excess kurtosis (so 0 = normal); ≈ \(\gamma_2\) |
Data: 5, 6, 7, 7, 8, 8, 8, 9, 9, 10. =SKEW ≈ −0.36 (slightly left-skewed); =KURT ≈ −0.15 (mildly platykurtic).
Right-skewed data (income): 20, 22, 25, 28, 30, 35, 50, 80, 120. =SKEW ≈ 1.71 (strong positive skew); =KURT ≈ 2.39 (leptokurtic).
30 students' marks in A2:A31. Running Descriptive Statistics with Confidence Level 95 % might give:
| Statistic | Value |
|---|---|
| Mean | 58.30 |
| Standard Error | 2.45 |
| Median | 56.50 |
| Mode | 55.00 |
| SD | 13.42 |
| Sample Variance | 180.10 |
| Kurtosis | −0.34 |
| Skewness | 0.21 |
| Range | 56 |
| Min | 32 |
| Max | 88 |
| Sum | 1 749 |
| Count | 30 |
| Conf. Level (95 %) | 5.02 |
So 95 % CI for mean ≈ (58.30 − 5.02, 58.30 + 5.02) = (53.28, 63.32).
| Functions | ToolPak (Descriptive) | |
|---|---|---|
| Update on data change | Yes (live formulas) | No — must rerun |
| Best for | Custom or single stat | One-shot full report |
| Output type | Single cell each | Multi-row table |
AVERAGE, MEDIAN, MODE.SNGL/MULT, GEOMEAN, HARMEAN for central tendency.VAR.S/P, STDEV.S/P, QUARTILE.INC, AVEDEV for dispersion.RANK.EQ, RANK.AVG, PERCENTRANK, PERCENTILE.INC for position.SKEW, KURT for shape (KURT is excess kurtosis).