Excel Advanced
Intermediate 13 min read Excel Advanced

Statistical Functions in Excel

Statistics without leaving the grid

Excel ships real statistical machinery: AVERAGEIFS, STDEV, CORREL, PERCENTILE, and the T.TEST/F tests that power confidence intervals and hypothesis checks without R.

AVERAGEIFSstandard deviationCORRELT.TESTpercentilesstatistics
Prerequisites: Excel Essential Skills
Before we start

What you will learn

  • Compute conditional aggregations and dispersion with native functions
  • Measure association and rank data with CORREL and PERCENTILE
  • Run t-tests and linear regression in Excel
Introduction

What is this?

Excel exposes a full statistical toolbox: conditional aggregates (AVERAGEIFS), dispersion (STDEV.S/P), association (CORREL), position (PERCENTILE.INC, RANK), distribution (NORM.DIST), and tests (T.TEST, F.TEST, Z.TEST). These functions let an analyst compute confidence intervals, compare cohorts, and test hypotheses without a statistics package.

Motivation

Why do you need it?

  • Most business decisions rest on simple statistics Excel can already compute.
  • Summaries lie without dispersion: 'avg order $40' hides very different distributions.
  • Statistical tests replace 'it feels different' with a defensible p-value.
  • Percentile thinking (P90, deciles) is how analysts talk about tails and outliers.
Think about it

Mental model

Imagine

A GPS showing not just distance but ETA spread

Think of

Statistical functions

Direction alone (average) isn't enough for planning; you need variance (may be 2–20 min) and extremes (P95 → 25 min). Statistics function is the GPS's uncertainty display.

Mechanics

How it works

  • AVERAGEIFS/SUMIFS/COUNTIFS aggregate over a criteria range — the Excel version of GROUP BY … HAVING.
  • STDEV.S estimates population dispersion from a sample; STDEV.P uses the whole population.
  • CORREL returns the Pearson correlation coefficient r in [-1, 1] between two ranges.
  • PERCENTILE.INC/EXC and QUARTILE place values relative to the distribution.
  • T.TEST (two-tailed) returns the probability of observing the difference by chance given two samples.

From raw values to a defensible comparison.

Two groups
Summarize
Test
Verdict
Hands-on

Examples

A/B comparison in Excel

A/B comparison in Excel
// Group stats for control and variant
=AVERAGE(Control_Rev)       =STDEV.S(Control_Rev)
=AVERAGE(Variant_Rev)        =STDEV.S(Variant_Rev)
=COUNT(Control_Rev)
 
// Difference test (two-tailed, unequal variance)
=T.TEST(Control_Rev, Variant_Rev, 2, 3)
 
// Association + ranks
=CORREL(B2:B100, C2:C100)
=PERCENTILE.INC(Rev, 0.9)       // P90 revenue
=RANK.EQ(B2, $B$2:$B$100)

Summarize both groups, run a two-tailed Welch-style t-test (mode 3), and interpret T.TEST < 0.05 as evidence the lift isn't luck.

Real world

In production

  • A pricing analyst uses CORREL to show price elasticity against volume.
  • An ops team sets a P90 response-time target from PERCENTILE.INC over a quarter.
  • A growth team runs a monthly T.TEST comparing cohort means to detect drift before it's visible.
Guidelines

Best practices

  • Report mean AND spread together; a mean without SD is an incomplete summary.
  • State the test's assumptions (independence, roughly normal means) before citing p-values.
  • Compare like for like: same window, same segment, same measuring rules.
  • Frame T.TEST comparisons with a hypothesis written before running it.
Watch out

Common mistakes

  • Using STDEV.P on a sample — understates uncertainty and invents precision.
  • Treating CORREL as proof of causation in a presented chart.
  • Running arrays with blanks/NaN that silently change the test result.
  • Fishing for a significant test after inspecting the data (p-hacking).
When things break

Debugging tips

  • T.TEST #DIV/0! — one group has zero variance or n=1; check sample sizes.
  • CORREL results you distrust — plot the scatter: one outlier can yank r.
  • Mean severely different from median — a skewed distribution; report both.
  • Wrap ranges in IFERROR with blanks handled so tests don't include empty cells.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which function returns the correlation coefficient between two ranges?

2

A p-value below 0.05 proves the effect is practically important.

Go deeper

Official documentation

Microsoft — T.TEST
docs
Microsoft — STDEV functions
docs