Excel for Statistics Students: STDEV, CORREL, and Regression

Descriptive statistics

Mean, median, mode

=AVERAGE(A2:A100)    arithmetic mean
=MEDIAN(A2:A100)     middle value
=MODE(A2:A100)       most frequent value (MODE.SNGL for single result)

Standard deviation

=STDEV(A2:A100)      sample standard deviation (use for samples)
=STDEVP(A2:A100)     population standard deviation (use for entire population)
=STDEV.S(A2:A100)    same as STDEV (newer function name)
=STDEV.P(A2:A100)    same as STDEVP (newer function name)

💡 Sample vs population standard deviation

Use STDEV (sample) when your data is a sample drawn from a larger population — which is almost always the case in research. Use STDEVP (population) only when you have data for the entire population.

Variance

=VAR(A2:A100)        sample variance
=VARP(A2:A100)       population variance

Percentiles and quartiles

=PERCENTILE(A2:A100, 0.9)   90th percentile
=QUARTILE(A2:A100, 1)       Q1 (25th percentile)
=QUARTILE(A2:A100, 2)       Q2 (median)
=QUARTILE(A2:A100, 3)       Q3 (75th percentile)

Correlation

=CORREL(A2:A100, B2:B100)
-- Pearson correlation coefficient between two variables
-- Returns a value between -1 (perfect negative) and +1 (perfect positive)
-- 0 = no linear correlation

Regression

Simple linear regression — SLOPE and INTERCEPT

=SLOPE(y_range, x_range)        slope (coefficient) of the regression line
=INTERCEPT(y_range, x_range)    y-intercept of the regression line
=RSQ(y_range, x_range)          R-squared (coefficient of determination)

-- Regression equation: y = SLOPE*x + INTERCEPT

Predicted values — FORECAST

=FORECAST(x_value, y_range, x_range)
-- Predicts the y value for a given x using linear regression
=FORECAST(25, B2:B50, A2:A50)
-- Predict y when x=25

Frequency distributions

=FREQUENCY(data_range, bins_range)
-- Array formula — press Ctrl+Shift+Enter in older Excel
-- Returns count of values falling in each bin
-- Select the output cells first, enter formula, Ctrl+Shift+Enter

Using the Data Analysis ToolPak

For more advanced statistics (ANOVA, t-tests, regression with output tables), enable the Analysis ToolPak: File → Options → Add-ins → Analysis ToolPak → Go → check the box. Then Data → Data Analysis gives you a menu of statistical tests.

Now practise it for real

Practice these formulas in the Students track — built for university assignments and graduate job assessments. Free to start.

Start the Students track free →

Excel for statistics studentsSTDEV, CORREL, and regression