formulastreak

MEDIAN, PERCENTILE, STDEV and CORREL

Updated · Works in: Excel 2010+, Microsoft 365, Google Sheets · Every example checked before release

Short answer

MEDIAN for a typical value of skewed data, MODE.SNGL for the most common, PERCENTILE.INC(range, 0.9) and QUARTILE.INC(range, 3) for cut-offs, RANK.EQ for positions, STDEV.S for a sample's spread and CORREL(x, y) for how two ranges move together.

A few functions answer most “what's typical, how spread out, how related” questions. All of these work the same in Excel and Google Sheets.

Typical values: MEDIAN and MODE

MEDIAN is the middle value, so a few huge values don't drag it. MODE.SNGL(F2:F11) is the most common value.

Team directory · edit H4 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultH4 = $63,000
ABCDEFGH
1NameDeptStartedBornSalaryLevelAs of
2Amara OseiSales15 Apr 20192 Aug 1990$58,000230 Jun 2026
3Ben FischerSupport9 Jan 202321 Nov 1996$41,0001
4Lina ChenEngineering1 Sep 201614 Mar 1987$88,0004Median pay$63,000
5Diego AlvarezSales28 Jun 202130 Jun 1993$52,0002
6Eva NovakFinance12 Feb 20185 Dec 1985$74,0003
7Farid HaddadEngineering4 Mar 202419 Jan 1998$68,0002
8Grace KimSupport19 Oct 202027 Sep 1991$45,0002
9Hugo MartinFinance11 May 20158 Apr 1979$88,0004
10Ines VargaEngineering14 Nov 202228 Feb 1995$79,0003
11Kenji MoriSales25 Aug 202516 Jul 2000$39,0001

Percentiles, quartiles and ranks

PERCENTILE.INC(range, 0.9) is the value 90% of the data is at or below; QUARTILE.INC(range, 3) is the 75th percentile. RANK.EQ(value, range) is the position, 1 for the highest.

Exam results · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = 11
ABCDEFGH
1StudentClassHoursScoreRankFromGrade
2NoorA248110F
3MateoB566850D
4HanaA881460C
5OllieB3551070B
6ZaraA1093185A
7IvanB6726
8LeaA13912
9OmarB7775
10SaraA9882
11TheoB4619
12YukiA6707
13BeaB8843

Spread: STDEV.S

Standard deviation measures how far values sit from the average. Use STDEV.S for a sample (most real data) and STDEV.P for a complete population.

Exam results · edit J1 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultJ1 = 16.5
ABCDEFGHIJ
1StudentClassHoursScoreFromGradeStd dev16.5
2NoorA2480F
3MateoB56650D
4HanaA88160C
5OllieB35570B
6ZaraA109385A
7IvanB672
8LeaA139
9OmarB777
10SaraA988
11TheoB461
12YukiA670
13BeaB884

Relationship: CORREL

CORREL(x, y) runs from -1 to 1. Near 1, the two rise together; near 0, there's no straight-line relationship. It doesn't prove one causes the other.

Exam results · edit J1 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultJ1 = 1.00
ABCDEFGHIJ
1StudentClassHoursScoreFromGradeCorrelation1.00
2NoorA2480F
3MateoB56650D
4HanaA88160C
5OllieB35570B
6ZaraA109385A
7IvanB672
8LeaA139
9OmarB777
10SaraA988
11TheoB461
12YukiA670
13BeaB884

Check yourself

One real exercise from the “Statistics” unit. Checked on our server; no account needed.

What's the median salary? Answer in H4.

  • Use MEDIAN
Select H4 and type = to start its formula (in the cell or the formula bar)
ABCDEFGH
1NameDeptStartedBornSalaryLevelAs of
2Amara OseiSales15 Apr 20192 Aug 1990$58,000230 Jun 2026
3Ben FischerSupport9 Jan 202321 Nov 1996$41,0001
4Lina ChenEngineering1 Sep 201614 Mar 1987$88,0004Median pay
5Diego AlvarezSales28 Jun 202130 Jun 1993$52,0002
6Eva NovakFinance12 Feb 20185 Dec 1985$74,0003
7Farid HaddadEngineering4 Mar 202419 Jan 1998$68,0002
8Grace KimSupport19 Oct 202027 Sep 1991$45,0002
9Hugo MartinFinance11 May 20158 Apr 1979$88,0004
10Ines VargaEngineering14 Nov 202228 Feb 1995$79,0003
11Kenji MoriSales25 Aug 202516 Jul 2000$39,0001

Common mistakes

  • Reporting the average of skewed data (salaries, house prices) as “typical”.

  • Using STDEV.P on a sample, which understates the spread.

  • Reading a high correlation as cause and effect.

Questions

What's the difference between PERCENTILE.INC and PERCENTILE.EXC?+

INC includes the minimum and maximum as the 0th and 100th percentiles; EXC excludes them, so it can't estimate very low or high percentiles of small samples.

How do I count values in bands?+

COUNTIFS with a lower and upper bound per band, or FREQUENCY, which spills a count for every bin.

Reading is step one. Now make it stick.

Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.

Practise it now