Descriptive statistics: the summary table
Build the ToolPak's Descriptive Statistics report with formulas: centre, spread, shape and the standard error. Intermediate · 7 min · AVERAGE, STDEV, VAR, SKEW, KURT, Standard error, Descriptive statistics
The ToolPak's Descriptive Statistics tool summarises a column in one table: where the values sit, how spread out they are, and what shape they have. Here you'll build that same table with formulas, in the same order, so it stays up to date.
The data are delivery times, in minutes, for 20 orders. You'll need:
- Centre:
AVERAGE,MEDIANandMODE. - Spread:
STDEV(sample standard deviation),VAR(sample variance) and the range. - Shape:
SKEW, positive when there's a long tail of high values, andKURT, positive when extreme values are more common than in a normal bell curve. - Standard error of the mean: the standard deviation ÷ √count. It shows how precisely the average is known.
Steps
Start at the bottom of the table with the basics. Fill in the Count (
E14), Sum (E13), Minimum (E11) and Maximum (E12) of the delivery times inB2:B21.Now the centre. Fill in the Mean (
E2) with AVERAGE, the Median (E4) with MEDIAN and the Mode (E5) with MODE.Next, the spread. Fill in the Standard Deviation (
E6) with STDEV, the Sample Variance (E7) with VAR, and the Range (E10) as the maximum minus the minimum you already have.The Standard Error says how precisely the 20 orders pin down the true average delivery time. In
E3, divide the standard deviation by the square root of the count:=E6/SQRT(E14)Last, the shape. Fill in the Kurtosis (
E8) with KURT and the Skewness (E9) with SKEW.
Remember
- Centre: AVERAGE, MEDIAN and MODE. When the mean is well above the median, a few large values are pulling it up.
- Spread: STDEV and VAR (samples), and the range, MAX − MIN.
- Standard error = STDEV ÷ √COUNT: how precisely the mean is known.
- Shape: SKEW (lopsidedness) and KURT (heavy tails).
- The ToolPak's Descriptive Statistics tool gives this table as fixed numbers. Built with formulas, it stays live.
In the function reference: AVERAGE, COUNT, KURT, MAX, MEDIAN, MIN, MODE, SKEW, SQRT, STDEV, SUM, VAR
Previous: Histograms with FREQUENCY · Next: Moving averages: smoothing out the noise