Statistics in Excel: averages, spread, correlation and regression

In this lesson you will learn

Choose between mean, median and mode and interpret the result
Calculate sample and population standard deviation correctly
Calculate the correlation coefficient and a regression line and make a forecast
Read an Analysis ToolPak regression report and build a histogram

A teacher recorded eight students' study hours and exam scores: hours 2, 4, 5, 6, 8, 9, 10, 12; scores 52, 60, 58, 70, 75, 80, 83, 92. What is the average score, and how spread out are the scores? Is there a link between hours and score, and what can a student who studies 10 hours expect? These questions are the core of statistics, and Excel answers them with a handful of functions.

Central tendency: AVERAGE, MEDIAN, MODE

Measure	Excel	For the scores	When to use it
Mean (x̄ = ∑x / n)	`=AVERAGE(B2:B9)`	71.25	symmetric data without outliers
Median (the middle value)	`=MEDIAN(B2:B9)`	72.5	salaries, prices — whenever there are outliers
Mode (most frequent)	`=MODE.SNGL(…)`	#N/A	marks, sizes; these scores have no repeats

Mean or median?

Monthly salaries of 7 people in a small company: 800, 850, 900, 950, 1000, 1100 and the director's ₼6000. Which measure shows the “typical” salary better? Class marks: 4, 5, 5, 3, 5, 4, 2, 5, 4, 5 — what is the mode?

Show solution

Mean: 11,600 / 7 ≈ ₼1657.14 — six of the seven people earn less than that!
Median: the middle of the sorted list — ₼950. The outlier (6000) does not move the median, which is why salary statistics prefer it.
Marks: =MODE.SNGL(A2:A11) → 5 (five times); the mean is 4.2 and the median 4.5.
If there may be several modes, =MODE.MULT(…) returns them all as an array.

Spread: STDEV.S and STDEV.P

s = √( ∑(xᵢ − x̄)² / (n − 1) ); σ = √( ∑(xᵢ − μ)² / N )s = √( ∑(xᵢ − x̄)² / (n − 1) ); σ = √( ∑(xᵢ − μ)² / N )

where:

ssample standard deviation — STDEV.S
σpopulation standard deviation — STDEV.P
n, Nthe sample size and the population size

n − 1 (Bessel's correction): the sample mean is the point “closest” to the data, so the squared deviations come out systematically too small; dividing by n − 1 compensates. For the scores: s = 13.802, σ = 12.911.

Correlation and linear regression

r = Sₓᵧ / √(Sₓₓ · Sᵧᵧ); b = Sₓᵧ / Sₓₓ; a = ȳ − b · x̄; ŷ = a + b · xr = Sₓᵧ / √(Sₓₓ · Sᵧᵧ); b = Sₓᵧ / Sₓₓ; a = ȳ − b · x̄; ŷ = a + b · x

where:

Sₓₓ, Sᵧᵧ∑(x − x̄)² and ∑(y − ȳ)²
Sₓᵧ∑(x − x̄)(y − ȳ) — how x and y vary together
rthe correlation coefficient, −1 ≤ r ≤ 1 — CORREL
b, aslope (points per hour) and intercept (points) — SLOPE, INTERCEPT

b and a come from least squares: set the derivatives of ∑(y − a − bx)² with respect to a and b to zero. As a result the line always passes through the point (x̄, ȳ).

Hours and scores

Hours are in A2:A9 and scores in B2:B9. Calculate r, the regression line and R², and forecast the score of a student who studies 10 hours.

Show solution

x̄ = 7, ȳ = 71.25; Sₓₓ = 78, Sᵧᵧ = 1333.5, Sₓᵧ = 318.
r = 318 / √(78 · 1333.5) ≈ 0.986 — a very strong positive link; =CORREL(A2:A9,B2:B9).
b = 318 / 78 ≈ 4.077 points per hour (=SLOPE(B2:B9,A2:A9)); a = 71.25 − 4.077 · 7 ≈ 42.71 (=INTERCEPT(B2:B9,A2:A9)).
Line: ŷ = 42.71 + 4.077 · x. R² = r² ≈ 0.972 (=RSQ(…)): 97.2% of the variation in scores is explained by hours.
Forecast: =FORECAST.LINEAR(10,B2:B9,A2:A9) → 42.71 + 40.77 ≈ 83.48 points.

Interactive

Loading simulation…

8 students: study hours and scores; correlation, regression and histogram frequencies.

Analysis ToolPak: regression report and histogram

1
Enable the add-in
File › Options › Add-ins › Manage: Excel Add-ins › Go… › tick Analysis ToolPak. The Data › Analyze › Data Analysis button appears.
2
Run Regression
Data Analysis › Regression: Input Y Range B1:B9, Input X Range A1:A9, tick Labels, choose Output Range or New Worksheet Ply › OK.
3
Read the report
Multiple R 0.986; R Square 0.972; Adjusted R Square 0.968; Standard Error 2.48 points; Coefficients: Intercept 42.71, Hours 4.077; the slope's P-value ≈ 0.0000068 < 0.05 — the relationship is statistically significant.
4
Build a histogram
Fastest: B1:B9 › Insert › Charts › Insert Statistic Chart › Histogram; set Format Axis › Bin width = 10. Alternative: Data Analysis › Histogram with a separate Bin Range (59, 69, 79, 89, 99).

Key points

With outliers, the median shows a better “typical” value than the mean.
STDEV.S is for samples (divides by n − 1), STDEV.P for whole populations (divides by N).
r = Sₓᵧ / √(Sₓₓ Sᵧᵧ); b = Sₓᵧ / Sₓₓ, a = ȳ − b x̄; the regression line passes through (x̄, ȳ).
FORECAST.LINEAR and TREND forecast along the line — reliable only within the observed range.
The Analysis ToolPak builds a regression report (R², coefficients, p-values) and histograms.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

Salaries: 800, 850, 900, 950, 1000, 1100, 6000 ₼. What does MEDIAN return?