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.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Name | Dept | Started | Born | Salary | Level | As of | |
| 2 | Amara Osei | Sales | 15 Apr 2019 | 2 Aug 1990 | $58,000 | 2 | 30 Jun 2026 | |
| 3 | Ben Fischer | Support | 9 Jan 2023 | 21 Nov 1996 | $41,000 | 1 | ||
| 4 | Lina Chen | Engineering | 1 Sep 2016 | 14 Mar 1987 | $88,000 | 4 | Median pay | $63,000 |
| 5 | Diego Alvarez | Sales | 28 Jun 2021 | 30 Jun 1993 | $52,000 | 2 | ||
| 6 | Eva Novak | Finance | 12 Feb 2018 | 5 Dec 1985 | $74,000 | 3 | ||
| 7 | Farid Haddad | Engineering | 4 Mar 2024 | 19 Jan 1998 | $68,000 | 2 | ||
| 8 | Grace Kim | Support | 19 Oct 2020 | 27 Sep 1991 | $45,000 | 2 | ||
| 9 | Hugo Martin | Finance | 11 May 2015 | 8 Apr 1979 | $88,000 | 4 | ||
| 10 | Ines Varga | Engineering | 14 Nov 2022 | 28 Feb 1995 | $79,000 | 3 | ||
| 11 | Kenji Mori | Sales | 25 Aug 2025 | 16 Jul 2000 | $39,000 | 1 |
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.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Student | Class | Hours | Score | Rank | From | Grade | |
| 2 | Noor | A | 2 | 48 | 11 | 0 | F | |
| 3 | Mateo | B | 5 | 66 | 8 | 50 | D | |
| 4 | Hana | A | 8 | 81 | 4 | 60 | C | |
| 5 | Ollie | B | 3 | 55 | 10 | 70 | B | |
| 6 | Zara | A | 10 | 93 | 1 | 85 | A | |
| 7 | Ivan | B | 6 | 72 | 6 | |||
| 8 | Lea | A | 1 | 39 | 12 | |||
| 9 | Omar | B | 7 | 77 | 5 | |||
| 10 | Sara | A | 9 | 88 | 2 | |||
| 11 | Theo | B | 4 | 61 | 9 | |||
| 12 | Yuki | A | 6 | 70 | 7 | |||
| 13 | Bea | B | 8 | 84 | 3 |
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.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Student | Class | Hours | Score | From | Grade | Std dev | 16.5 | ||
| 2 | Noor | A | 2 | 48 | 0 | F | ||||
| 3 | Mateo | B | 5 | 66 | 50 | D | ||||
| 4 | Hana | A | 8 | 81 | 60 | C | ||||
| 5 | Ollie | B | 3 | 55 | 70 | B | ||||
| 6 | Zara | A | 10 | 93 | 85 | A | ||||
| 7 | Ivan | B | 6 | 72 | ||||||
| 8 | Lea | A | 1 | 39 | ||||||
| 9 | Omar | B | 7 | 77 | ||||||
| 10 | Sara | A | 9 | 88 | ||||||
| 11 | Theo | B | 4 | 61 | ||||||
| 12 | Yuki | A | 6 | 70 | ||||||
| 13 | Bea | B | 8 | 84 |
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.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Student | Class | Hours | Score | From | Grade | Correlation | 1.00 | ||
| 2 | Noor | A | 2 | 48 | 0 | F | ||||
| 3 | Mateo | B | 5 | 66 | 50 | D | ||||
| 4 | Hana | A | 8 | 81 | 60 | C | ||||
| 5 | Ollie | B | 3 | 55 | 70 | B | ||||
| 6 | Zara | A | 10 | 93 | 85 | A | ||||
| 7 | Ivan | B | 6 | 72 | ||||||
| 8 | Lea | A | 1 | 39 | ||||||
| 9 | Omar | B | 7 | 77 | ||||||
| 10 | Sara | A | 9 | 88 | ||||||
| 11 | Theo | B | 4 | 61 | ||||||
| 12 | Yuki | A | 6 | 70 | ||||||
| 13 | Bea | B | 8 | 84 |
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
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Name | Dept | Started | Born | Salary | Level | As of | |
| 2 | Amara Osei | Sales | 15 Apr 2019 | 2 Aug 1990 | $58,000 | 2 | 30 Jun 2026 | |
| 3 | Ben Fischer | Support | 9 Jan 2023 | 21 Nov 1996 | $41,000 | 1 | ||
| 4 | Lina Chen | Engineering | 1 Sep 2016 | 14 Mar 1987 | $88,000 | 4 | Median pay | |
| 5 | Diego Alvarez | Sales | 28 Jun 2021 | 30 Jun 1993 | $52,000 | 2 | ||
| 6 | Eva Novak | Finance | 12 Feb 2018 | 5 Dec 1985 | $74,000 | 3 | ||
| 7 | Farid Haddad | Engineering | 4 Mar 2024 | 19 Jan 1998 | $68,000 | 2 | ||
| 8 | Grace Kim | Support | 19 Oct 2020 | 27 Sep 1991 | $45,000 | 2 | ||
| 9 | Hugo Martin | Finance | 11 May 2015 | 8 Apr 1979 | $88,000 | 4 | ||
| 10 | Ines Varga | Engineering | 14 Nov 2022 | 28 Feb 1995 | $79,000 | 3 | ||
| 11 | Kenji Mori | Sales | 25 Aug 2025 | 16 Jul 2000 | $39,000 | 1 |
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.